Binary from 32-bit integer

Question

Hi,

I have the following worksheet formula:

=DEC2BIN(MOD(A1/16777216,256),8) &"."& DEC2BIN(MOD(A1/65536,256),8) &"."& DEC2BIN(MOD(A1/256,256),8) &"."& DEC2BIN(MOD(A1,256),8)

(which I found here: http://ottobelden.blogspot.co.uk/2010/06/ms-excel-dec2bin-32-bit-rotate-no-carry.html).

I have created a UDF equivalent, but it doesn't behave in the same way.  Here's the UDF:

Function DecTo32Bin(lnDec As Long)

' Function to return the binary representation of a 32-bit integer as a string' in the format "00000000.00000000.00000000.00000000"' Input must be a positive integer between 0 and 4,294,967,295 inclusive.'

    DecTo32Bin = Application.WorksheetFunction.Dec2Bin(((lnDec / 16777216) Mod 256), 8) _

         & "." & Application.WorksheetFunction.Dec2Bin(((lnDec / 65536) Mod 256), 8) _

         & "." & Application.WorksheetFunction.Dec2Bin(((lnDec / 256) Mod 256), 8) _

         & "." & Application.WorksheetFunction.Dec2Bin((lnDec Mod 256), 8)

End Function

Examples:

253	<--INPUT
00000000.00000000.00000000.11111101	<--Worksheet Formula
00000000.00000000.00000001.11111101	<-- DecTo32Bin UDF
^
254	<--INPUT
00000000.00000000.00000000.11111110	<--Worksheet Formula
00000000.00000000.00000001.11111110	<-- DecTo32Bin UDF
^
255	<--INPUT
00000000.00000000.00000000.11111111	<--Worksheet Formula
00000000.00000000.00000001.11111111	<-- DecTo32Bin UDF
^
256	<--INPUT
00000000.00000000.00000001.00000000	<--Worksheet Formula
00000000.00000000.00000001.00000000	<-- DecTo32Bin UDF
257	<--INPUT
00000000.00000000.00000001.00000001	<--Worksheet Formula
00000000.00000000.00000001.00000001	<-- DecTo32Bin UDF

How come the UDF is giving incorrect results? (I suspect rounding issues... if so, how do we correct this?).

Cheers

Rich

Answer 1

Rich,

The difference lies in the use of the Excel worksheet version of Mod versus the VBA version of Mod.  See, for example:

http://stackoverflow.com/questions/4378047/vba-equivalent-to-excels-mod-function

Test Value	Mod Function (Excel, Then VBA)
253	0.98828125
	1
254	0.9921875
	1
255	0.99609375
	1

Unfortunately, there is no support for Application.WorksheetFunction.Mod in VBA.  However, the link above suggests a way to get the VBA equivalent of the worksheet Mod function.

HTH,

Eric

Answer 2

Hi Andreas,

Thanks for your suggestion, but it doesn't work... it falls over at 2^31. 

(2^31)-1 is ok.

I tried replacing both

    lnDec As Long

and

    Pow2(0 To 62) As Long

with 

    lnDec As Double

and

    Pow2(0 To 62) As Double

but that didn't fix it.  Any thoughts?

(my code above works to (2^32)-1   ;o)

Also, what's with the If i = 0 line?  Is that in the wrong place - should it be inside the For i loop?

Cheers

Rich

Answer 3

Hi Eric,

Thanks for that.  Confirmed my suspicions.  I've seen this issue crop up several times on this forum but it's never hit me before now.

Cheers

Rich

Answer 4

 (I suspect rounding issues... if so, how do we correct this?).

Use binary operators instead.

Andreas.

Static Function DecTo32Bin(lnDec As Long) As String

  Dim Temp(0 To 63) As Byte, Pow2(0 To 62) As Long, i As Integer

  If i = 0 Then

    Pow2(62) = 1

    For i = 60 To 2 Step -2

      Pow2(i) = 2 * Pow2(i + 2)

    Next

    Pow2(0) = &H80000000

  End If

  For i = 0 To 62 Step 2

    Temp(i) = 48 - ((lnDec And Pow2(i)) <> 0) '0 or 1

  Next

  'Without dots

  DecTo32Bin = Temp

  'With dots

  DecTo32Bin = _

    Mid(DecTo32Bin, 1, 8) & "." & _

    Mid(DecTo32Bin, 9, 8) & "." & _

    Mid(DecTo32Bin, 17, 8) & "." & _

    Mid(DecTo32Bin, 25, 8)

End Function

Answer 5

OK, so using the fact that the worksheet MOD function can be expressed in terms of the INT function:

MOD(n, d) = n - d*INT(n/d)

(from the Excel help file)

I re-wrote the UDF like this:

Function DecTo32Bin(Dec As Double)

' Function to return the binary representation of a 32-bit integer as a string' in the format "00000000.00000000.00000000.00000000"' Input must be a positive integer between 0 and 4,294,967,295 inclusive.'

    Dim str1 As String, str2 As String, str3 As String, str4 As String

    Dim d As Double, n As Double

    d = 256

    n = Dec / (2 ^ 24) '16777216

    str1 = Application.WorksheetFunction.Dec2Bin((n - d * Int(n / d)), 8)

    n = Dec / (2 ^ 16) '65536

    str2 = Application.WorksheetFunction.Dec2Bin((n - d * Int(n / d)), 8)

    n = Dec / (2 ^ 8) '256

    str3 = Application.WorksheetFunction.Dec2Bin((n - d * Int(n / d)), 8)

    n = Dec

    str4 = Application.WorksheetFunction.Dec2Bin((n - d * Int(n / d)), 8)

    DecTo32Bin = str1 & "." & str2 & "." & str3 & "." & str4

End Function

This now appears to work ok.  I'm left with 2 questions:

1) Why didn't my original UDF work?

2) In my re-written UDF, should "d" and "n" be declared as Doubles, or something else?

Cheers

Rich

[EDIT: Perhaps I spoke too soon.  Works ok except with 2147483648 or above as the input.  This should return 10000000.00000000.00000000.00000000 but instead returns the #NUM! error.

Please help!]

[EDIT2: OK, fixed that too by changing the first line of the function from:

Function DecTo32Bin(lnDec As Long)

to

Function DecTo32Bin(Dec As Double)

BUT doing that to my original UDF didn't fix it, so my questions still stand..]