Convert number to/from binary (VBA)

Topic

New Reply

Hi,
I encountered some weird bug in a function conversing a number to a binary value… can anyone tell me what went wrong? I use two functions
– CBin() is the main procedure converting a double value to a binary one by splitting it into powers of two and recombining these as powers of ten into the right binary number (e.g. 9 = 8 + 1 = 2^3 + 2^0 => 10

Reply | Quote

Replies

Double precision floating point numbers have a precision of 15 significant digits. The difference between 1000000000000000 and 1000000000000001 is in the 16th digit, so it gets lost. You can’t represent numbers over 32,767 as binary in a variable of type Double. You’ll have to use strings.

Reply | Quote

Thanks!

Reply | Quote

If interested here is an example of a Decimal-to-Binary function that converts a Long Integer value (max value range -2^31 to 2^31 or -2147483648 to 2147483647) to binary number represented by a string:

Public Function DecToBin(ByVal lngDec As Long) As String

Const MAXLEN = 30
Dim strBin As String
Dim n As Long

If lngDec < 0 Then
strBin = "1"
Else
strBin = "0"
End If

For n = MAXLEN To 0 Step -1
If lngDec And (2 ^ n) Then
strBin = strBin & "1"
Else
strBin = strBin & "0"
End If
Next

DecToBin = strBin

End Function

Sample results:

? DecToBin(32768)
00000000000000001000000000000000
? DecToBin(32769)
00000000000000001000000000000001
? DecToBin(2^31-1)
01111111111111111111111111111111
? DecToBin(2147483647)
01111111111111111111111111111111
? DecToBin(-2^31)
10000000000000000000000000000000
? DecToBin(-2147483648)
10000000000000000000000000000000
? DecToBin(2^31)
' Overflow error

Function based on example in MSKB Art 109260, How to Convert a Decimal Number to a Binary Number in a String modified somewhat. As noted in article, "This program accepts a nine-digit positive decimal number and returns a 32- character string that represents the number in binary notation. Negative numbers are converted into the 32-digit, twos-complement binary format used by long integers in Basic…. In that format, the left-most binary digit (the thirty-second digit in a long integer) will always be 1 for a negative number and 0 for a positive number."

If you don't want the leading zeroes then you'd need to modify function.

HTH

Reply | Quote

Thanks Marc! Nice use of the AND operator… which makes it a lot simpler. Need to work on my programming skills…

Reply | Quote

Thanks Marc! Nice use of the AND operator… which makes it a lot simpler. Need to work on my programming skills…

Reply | Quote

If interested here is an example of a Decimal-to-Binary function that converts a Long Integer value (max value range -2^31 to 2^31 or -2147483648 to 2147483647) to binary number represented by a string:

Public Function DecToBin(ByVal lngDec As Long) As String

Const MAXLEN = 30
Dim strBin As String
Dim n As Long

If lngDec < 0 Then
strBin = "1"
Else
strBin = "0"
End If

For n = MAXLEN To 0 Step -1
If lngDec And (2 ^ n) Then
strBin = strBin & "1"
Else
strBin = strBin & "0"
End If
Next

DecToBin = strBin

End Function

Sample results:

? DecToBin(32768)
00000000000000001000000000000000
? DecToBin(32769)
00000000000000001000000000000001
? DecToBin(2^31-1)
01111111111111111111111111111111
? DecToBin(2147483647)
01111111111111111111111111111111
? DecToBin(-2^31)
10000000000000000000000000000000
? DecToBin(-2147483648)
10000000000000000000000000000000
? DecToBin(2^31)
' Overflow error

Function based on example in MSKB Art 109260, How to Convert a Decimal Number to a Binary Number in a String modified somewhat. As noted in article, "This program accepts a nine-digit positive decimal number and returns a 32- character string that represents the number in binary notation. Negative numbers are converted into the 32-digit, twos-complement binary format used by long integers in Basic…. In that format, the left-most binary digit (the thirty-second digit in a long integer) will always be 1 for a negative number and 0 for a positive number."

If you don't want the leading zeroes then you'd need to modify function.

HTH

Reply | Quote

Thanks!

Reply | Quote

Double precision floating point numbers have a precision of 15 significant digits. The difference between 1000000000000000 and 1000000000000001 is in the 16th digit, so it gets lost. You can’t represent numbers over 32,767 as binary in a variable of type Double. You’ll have to use strings.

Reply | Quote