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