vba print binary code example

Example 1: excel vba convert number to binary string

'VBA function to convert number to binary string:

Function DecToBin$(ByVal n&)
    Do
        DecToBin = n Mod 2 & DecToBin
        n = n \ 2
    Loop While n
End Function

'-------------------------------------------------------------------  
'NB: Excel has the built-in worksheet function, DEC2BIN(), but it
'can only handle 10-bit numbers or less. The above function can
'convert all positive 32-bit numbers: 
  
MsgBox DecToBin(9512489)    '<--displays: 100100010010011000101001

Example 2: excel vba binary string to long

'Fast VBA function to converst binary string to a Long Integer:

Function BitsToLong&(bits$)
    Dim i&
    Static b() As Byte
    If LenB(bits) > 64 Then Exit Function
    If LenB(bits) = 64 Then
        b = bits
    Else
        b = String$(32 - Len(bits), "0") & bits
    End If
    For i = 2 To 62 Step 2
        BitsToLong = 2 * BitsToLong Or (b(i) Xor 48)
    Next
    If (b(0) Xor 48) Then BitsToLong = BitsToLong Or &H80000000
End Function

'-----------------------------------------------------------------------------

MsgBox BitsToLong("1")									'<--displays:  1    
MsgBox BitsToLong("10")									'<--displays:  2
MsgBox BitsToLong("0110")								'<--displays:  6
MsgBox BitsToLong("0100101")							'<--displays:  37
MsgBox BitsToLong("100000000000000000000")				'<--displays:  1048576
MsgBox BitsToLong("11111111111111111111111111111111")	'<--displays:  -1