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

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 summatio开发者_StackOverflow社区n of all the array list.In this case, it'll be 15

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

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