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?

Answer 1

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