Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Convert number to/from binary (VBA)

    Thanks!

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    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
  •