# Thread: Convert number to/from binary (VBA)

1. ## Convert number to/from binary (VBA)

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

2. ## Re: Convert number to/from binary (VBA)

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.

Thanks!

4. ## Re: Convert number to/from binary (VBA)

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

5. ## Re: Convert number to/from binary (VBA)

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•