This site will look better if you upgrade to a browser that supports web standards.
This site will look better if you upgrade to a browser that supports web standards.
McGimpsey & Associates This site will look better if you upgrade to a browser that supports web standards.
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
'http://www.mcgimpsey.com/excel/longdec2binary.html
'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&
Else
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"
Else
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
Else
LongDec2Bin = CVErr(xlErrNum)
End If
End Function
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
Don't know how to use User Defined Functions? See David McRitchie's Getting Started with Macros and User Defined Functions