How to Convert Binary String into Array Of Integer Bit using Excel VBA

Question

I'm having hardstop with this problem to solve in my Excel macro coding. My input data is a binary string '1110'. Need to extract the binary string and return each bit of binary in sequence into array (as integer bit). Pls help me...really appreciate help for coaching.

For each bit = 1, the array value for that bit = Power of 2. At the end, add up all the value. Example:

Input = 1110 (binary string) store into Array (i). i = 3 (total bit from input =4)

Array (0) = 1^2 to the pwr of 0 = 1
Array (1) = 1^2 to the pwr of 1 = 2
Array (2) = 1^2 to the pwr of 2 = 4
Array (3) = 1^2 to the pwr of 3 = 8

The final output to be returned is summation of all the array list.In this case, it'll be 15

rene · Accepted Answer · 2011-03-13 09:39:51Z

1

From the top of my head without testing this will get you close.

Dim bin as String

bin="1101"

Dim bits(Len(bin)) as Integer

Dim bitIndex as Integer

Dim sum as Integer
sum = 0
For bitIndex = 0 to Len(bin)-1
  bits(bitIndex) = CInt(Mid(bin,bitIndex+1, 1)) * (2 ^ bitIndex)
  Debug.Print bits(bitIndex)
  sum = sum + bits(bitIndex) 
Next

Debug.Print sum

answered Mar 13, 2011 at 9:39

rene

42.6k78 gold badges122 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LeenNew Over a year ago

thanks Rene. I've been thinking too much. That's a simple workable soultion. Thank u very much...ur help is so appreciated

Charles Williams · Accepted Answer · 2011-03-13 17:51:03Z

0

You can get the final answer using the built-in Excel function BIN2DEC (from the analysis Toolpak)

answered Mar 13, 2011 at 17:51

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

1 Comment

LeenNew Over a year ago

i'm afraid BIN2DEC will not work here as i'm not looking for a direct binary to decimal translation. Once binary obtained from the binary string, it has to be manipulated with power of 2 & sum all the values up. Thanks Charles for the advise, though :)

Collectives™ on Stack Overflow

How to Convert Binary String into Array Of Integer Bit using Excel VBA

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related