An integer to a logical / bitmap without a loop

Question

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

Is there a way to do this without a loop?

I can convert to Binary without a loop in my code with a worksheet formula as follows

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

But I was told that the worksheet features are very inefficient, and this is especially limited.

I'm not sure how to split into an array without scrolling digits using MID . Is there something like strConv for numbers?

Answer 1

First, you can convert the value to the string "01" using WorksheetFunction.Dec2Bin . Then replace each "0", "1" with the code 0 or 1 and pass the result to the 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 limited to 511, and working with strings is quite expensive. Therefore, if your goal is to get maximum 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 

Answer 2

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

What you do is 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 this string into an array, where buff is your array

Answer 3

I think you probably got everything you need above from other answers, but if you need a simple function that takes a decimal and returns an 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 

Answer 4

Calling Application.Dec2Bin(n) not very expensive, you just have to take care of the late call. Use the function below to convert any integer into 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 

ps: s will contain only 0 and 1 , which are ASCII characters, so the separation method is absolutely right.

Answer 5

Please check this code if this is what you need: You can replace the number 5 with any reference to the cell value, this is just an 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 

Answer 6

Just change the lngNumber value to the 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