Integer to boolean/bit array without looping

Question

I have a number (say 5) which I would first like to convert to binary (101) and then split into an array of bits {1,0,1} or Booleans {True,False,True} in VBA

Is there a way to do this without looping?

I can convert to Binary without looping in my code with the worksheet formula as follows

myBinaryNum = [DEC2BIN(myDecInteger,[places])]

But I've been told that worksheet functions are very inefficient, and this one is particularly limited.

I'm not sure how to split into an array without looping through the digits with MID. Is there anything like strConv for numbers?

score 5 · Accepted Answer · answered Jul 25 '17 at 01:31

You could first convert the value to a "01" string with WorksheetFunction.Dec2Bin. Then replace each "0","1" with the code 0 or 1 and cast the result to a Byte array :

Public Function ToBitArray(ByVal value As Long) As Byte()
  Dim str As String
  str = WorksheetFunction.Dec2Bin(value)                   ' "101"
  str = Replace(Replace(str, "0", ChrW(0)), "1", ChrW(1))  ' "\u0001\u0000\u0001"
  ToBitArray = StrConv(str, vbFromUnicode)                 ' [1, 0, 1]
End Function

But Dec2Bin is limited to 511 and working with strings is rather expensive. So if your goal is to get the best performance, then you should use a loop to read each bit:

Public Function ToBitArray(ByVal value As Long) As Byte()
  Dim arr(0 To 31) As Byte, i As Long
  i = 32&

  Do While value
    i = i - 1
    arr(i) = value And 1
    value = value \ 2
  Loop

  ToBitArray = MidB(arr, i + 1)   ' trim leading zeros
End Function

Novice question, in your second solution (with a loop), what is the meaning of `i = 32**&**`, doesn't the `&` declare the preceding value as `Long` type, but since `i` is already declared and 32 is just a number, I'm not really sure what that means? — Greedo, Aug 01 '17 at 16:10
The `32&` means that 32 is used as a `Long`. `32` alone is used as an `Integer`. It is useful when the value is assigned to a variant and when you need it to hold a `Long` or to avoid an overflow on an operation. It doesn't matter here since it is automatically converted to the type of `i` which is `Long`. — Florent B., Aug 01 '17 at 16:23

score 1 · Answer 2 · answered Jul 24 '17 at 15:28

I found this neat code on another question here at SO. Basically, you can be sure your string is ASCII due to the fact it's 1's and 0's.

What you do is you use

Dim my_string As String

my_string =  CStr("your binary number")

To turn your binary number into a string

And then

Dim buff() As String
buff = Split(StrConv(my_string, vbUnicode), Chr$(0))
ReDim Preserve buff(UBound(buff) - 1

To split that string into an array where buff is your array

score 1 · Answer 3 · answered Jul 24 '17 at 15:42

I think you've probably got everything you need above from other answers, but if you want a simple function that takes the decimal and returns the array..

Function dec_to_binary_array(decNum As Integer)
    Dim arr() As String, NumAsString As String
    NumAsString = Application.Dec2Bin(decNum)
    arr = Split(StrConv(NumAsString, vbUnicode), vbNullChar)
    ReDim Preserve arr(UBound(arr) - 1)
    dec_to_binary_array = arr
End Function

A.S.H · Answer 4 · 2017-07-24T17:16:51.867

Invoking Application.Dec2Bin(n) isn't realy expensive, it only costs a late bound call. Use the function below to transform any integer into an arrays of bits:

Function Bits(n as long)
  Dim s As String: s = Application.Dec2Bin(n)
  Dim ar: ar = Split(StrConv(s, vbUnicode), vbNullChar)
  Bits = ar
End Function

p.s.: s will only contain 0 and 1 which are ASCII characters, so the split technique is perfectly valid.

Jacek Krasicki · Answer 5 · 2018-04-22T16:30:53.587

Function d2bin(dec As Integer, bits As Integer) As Integer()
    Dim maxVal As Integer
    maxVal = 2 ^ (bits)-1

    If dec > maxVal Then Exit Function

    Dim i As Integer
    Dim result() As Integer
    ReDim result(0 To bits - 1)

    For i = bits - 1 To 0 Step -1
        result(bits - i - 1) = -(dec > (2 ^ (i) - 1))
        If result(bits - i - 1) Then dec = dec - (2 ^ i)
    Next i

    d2bin = result

End Function

score -1 · Answer 6 · answered Jul 24 '17 at 15:37

Please check this code if this is what you need: You can replace the the digit 5 by any cell value reference, this is just and example:

Sub dectobinary()
Dim BinaryString As String

BinaryString = "5"

tempval = Dec2Bin(BinaryString)

MsgBox tempval

End Sub


Function Dec2Bin(ByVal DecimalIn As Variant) As String
Dec2Bin = ""
DecimalIn = Int(CDec(DecimalIn))
Do While DecimalIn <> 0
    Dec2BinTemp = Format$(DecimalIn - 2 * Int(DecimalIn / 2))
        If Dec2BinTemp = "1" Then
            Dec2Bin = "True" & "," & Dec2Bin
        Else
            Dec2Bin = "False" & "," & Dec2Bin
        End If
    DecimalIn = Int(DecimalIn / 2)
Loop
End Function

score -1 · Answer 7 · answered Jul 24 '17 at 15:39

Just change lngNumber value to your desired number

Public Sub sChangeNumberToBinaryArray()

Dim strBinaryNumber As String
Dim strBinaryArray()  As String
Dim lngNumber As Long

    lngNumber = 5

    strBinaryNumber = DecToBin(lngNumber)
    strBinaryArray() = Split(strBinaryNumber, "|")

End Sub

Function DecToBin(ByVal varDecimalIn As Variant) As String

Dim lngCounter As Long

    DecToBin = ""
    varDecimalIn = Int(CDec(varDecimalIn))
    lngCounter = 1
    Do While varDecimalIn <> 0
        If lngCounter = 1 Then
            DecToBin = Format$(varDecimalIn - 2 * Int(varDecimalIn / 2)) & DecToBin
            lngCounter = lngCounter + 1
        Else
            DecToBin = Format$(varDecimalIn - 2 * Int(varDecimalIn / 2)) & "|" & DecToBin
            lngCounter = lngCounter + 1
        End If
        varDecimalIn = Int(varDecimalIn / 2)
    Loop
End Function

Integer to boolean/bit array without looping

7 Answers7