General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions


XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

Converting Large Numbers to Binary

The DEC2BIN() function in the Analysis Toolpak Add-in (ATP) is limited to 10 binary digits, or 511 decimal. This User Defined Function (UDF) will return a binary integer for decimal values from -2,147,483,647 to 2,147,483,647 (2^31 - 1). If nIn is negative, a two's-complement value is returned (with the high bit set to 1). If numBits is less than the number of bits required to represent the number, #NUM! (Error 2036 in VBA) is returned.

As Harlan Grove has pointed out in a newsgroup message, using a UDF is likely to be slower than using combinations of worksheet functions. See the message for his worksheet function approach. Note that it requires the ATP, since it uses DEC2BIN(). My thanks to Harlan for well-deserved criticism of earlier versions of this macro. See this thread for his alternative method, which returns negative numbers as 32 character strings.

   'Purpose: Create a binary representation of a decimal #
   'Inputs:  nIn: number to be converted. (-2^31 to 2^31-1)
   '         nBits: # bits to return. (If not specified, 
   '                return the minimum required
   'Returns: string of binary digits
   '         negative numbers  returned as 2's complement
   '         with the high bit as a sign bit. If nBits is
   '         insufficient or if nIn is out of range, 
   '         return the #NUM! error
   Function LongDec2Bin(ByVal nIn As Long, _
                   Optional nBits As Long = 0&) As Variant
      'J.E. McGimpsey  
      'Thanks to Harlan Grove for suggested modifications
      Dim nReqBits As Long
      Dim sOut As String
      Dim sBit As String
      Dim bNeg As Boolean
      Dim i As Long
      If nIn < 0& Then
         bNeg = True
         nIn = -(nIn + 1&)
      End If
      If nIn = 0& Then
         nReqBits = 1&
         nReqBits = Int(Log(nIn) / Log(2&)) + 1& - bNeg
      End If
      If nBits <= 0& Then nBits = nReqBits
      If nBits >= nReqBits Then
         If bNeg Then
            sOut = String(nBits, "1")
            sBit = "0"
            sOut = String(nBits, "0")
            sBit = "1"
         End If
         For i = nBits To (nBits - nReqBits + 1&) Step -1
            If (nIn - 2& * (nIn \ 2&)) > 0 _
                Then Mid(sOut, i, 1&) = sBit
            nIn = nIn \ 2&
         Next i
         LongDec2Bin = sOut
         LongDec2Bin = CVErr(xlErrNum)
      End If
   End Function

Valid XHTML 1.1 Valid CSS Made on a Macintosh

Quick Links

Don't know how to use User Defined Functions? See David McRitchie's Getting Started with Macros and User Defined Functions