excel vba binary string to short integer code example

Example 1: excel vba string to binary

'VBA function to convert text to a binary string representation:

Public Function TextToBinaryString$(s$)
    Dim c&, i&, lo&, bin$(0 To 255), d() As Byte
    Const ZEROS$ = "00000000"
    For c = 0 To 255
        bin(c) = ZEROS
        If c And 1& Then MidB$(bin(c), 15) = "1"
        If c And 2& Then MidB$(bin(c), 13) = "1"
        If c And 4& Then MidB$(bin(c), 11) = "1"
        If c And 8& Then MidB$(bin(c), 9) = "1"
        If c And 16& Then MidB$(bin(c), 7) = "1"
        If c And 32& Then MidB$(bin(c), 5) = "1"
        If c And 64& Then MidB$(bin(c), 3) = "1"
        If c And 128& Then MidB$(bin(c), 1) = "1"
    Next
    d = s
    lo = 1
    TextToBinaryString = Space$(LenB(s) * 8)
    For i = 0 To LenB(s) - 1 Step 2
        Mid$(TextToBinaryString, lo) = bin(d(i + 1))
        Mid$(TextToBinaryString, lo + 8) = bin(d(i))
        lo = lo + 16
    Next
End Function
            
'------------------------------------------------------------------------------
            
MsgBox TextToBinaryString("Hi")  '<--displays: 00000000010010000000000001101001

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

Example 3: excel vba binary string to short integer

'Extremely fast VBA function to convert a binary string to a 16-bit Integer:

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

'Example:

MsgBox BitsToInteger("1111111111111111")    '<--displays: -1
MsgBox BitsToInteger("0111111111111111")    '<--displays:  32767