# Binary from 32-bit integer

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

## Question Info

Last updated October 21, 2018 Views 3,759 Applies to:

* Please try a lower page number.

* Please try a lower page number.

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.

[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..]

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

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

2 people were helped by this reply

·

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

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

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

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

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

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

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

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

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

Also, what's with the If i = 0 line?  Is that in the wrong place - should it be inside the For i loop?
a) That is not a problem with my code, try this:

Sub Test()
Dim L As Long
L = 2 ^ 31
End Sub

b) The function is STATIC, means it stays in memory and all variables hold there values between the calls. So "If i = 0 then" is true only the first time, means the Pow2 array is calculated only once.

Andreas.

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

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

a) That is not a problem with my code, try this:

Sub Test()
Dim L As Long
L = 2 ^ 31
End Sub

Hi Andreas,

I don't understand...

Your Sub Test here returns an Overflow error; 2^31 is greater than the max possible Long Int isn't it?

2^31 = 2,147,483,648

Max Long = 2,147,483,647

Are you saying we shouldn't be using Doubles?  The Function Declaration must contain a Double if it is to be able to accept 2^31+ as an input...

A 32-bit Decimal number can be up to (2^32)-1

(2^32)-1 = 4,294,967,295 = 11111111.11111111.11111111.11111111

I can't get your code to work at or above 2^31....

Also, what's with the If i = 0 line?  Is that in the wrong place - should it be inside the For i loop?
b) The function is STATIC, means it stays in memory and all variables hold there values between the calls. So "If i = 0 then" is true only the first time, means the Pow2 array is calculated only once.

That's neat!  I was thinking Static Function was to do with the volatility of UDFs, wrong again....

- Why is Pow2(0) = -2147483648 (negative??)

- Why when I feed 1 to your function does the Temp array contain alternate zeros and 48, except for Temp(62) which = 49.  Your code does 48 minus a Boolean.  True in VBA is -1 not +1 (I remember this now, but your comment says '0 or 1.... which confused me...).  And I've just realised that 48 is ascii for zero and 49 is ascii for 1, and it forms a string (I'm getting there!).  But how does the

(  ( lnDec And Pow2(i) )   <> 0)

work?

- and how do we get it to work up to (2^32)-1 ?

Cheers
Rich

PS. The If I change to Doubles throughout your code, it errors when the input is 2^31 at

(  ( lnDec And Pow2(i) )   <> 0)

when i = 0      i.e. when Pow2(i) = -2147483648

RTE6 Overflow.....

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Are you saying we shouldn't be using Doubles?
No, I say that we can't use anything other as LONG, because VB can only use the AND NOT OR XOR EQV IMP operators with integer data types (BYTE INTEGER LONG).

When you want to handle greater values, you have to upgrade to 64bit applications, they support the data type LONGLONG.

BTW, for the output "11111111.11111111.11111111.11111111" use DecTo32Bin(-1)

Andreas.

Sub Test()
Dim LL As LongLong
LL = 2 ^ 63 - 1 'Max. possible value
Debug.Print DecTo32Bin(LL)
End Sub

Static Function DecTo32Bin(lnDec As LongLong) As String
'EDIT: This is wrong, we need at min 0...63*2 places:
'  Dim Temp(0 To 62 * 2 + 1) As Byte, Pow2(0 To 62 * 2) As LongLong, i As Integer
'See my follow ups for a working version.
If i = 0 Then
Pow2(62 * 2) = 1
For i = 61 * 2 To 2 Step -2
Pow2(i) = 2 * Pow2(i + 2)
Next
Pow2(0) = &H8000000000000000^
End If
For i = 0 To 62 * 2 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) & "." & _
Mid(DecTo32Bin, 33, 8) & "." & _
Mid(DecTo32Bin, 41, 8) & "." & _
Mid(DecTo32Bin, 49, 8) & "." & _
Mid(DecTo32Bin, 57, 8)
End Function

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Are you saying we shouldn't be using Doubles?
No, I say that we can't use anything other as LONG, because VB can only use the AND NOT OR XOR EQV IMP operators with integer data types (BYTE INTEGER LONG).

When you want to handle greater values, you have to upgrade to 64bit applications, they support the data type LONGLONG.

Thanks for this Andreas.

So for 32-bit Office, we can't use your code?  Shame, as always with your code, I liked its style.

I guess the lesson for all here then is when using VBA to replicate the Worksheet Function MOD, use this:

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

Thanks again.

Cheers
Rich

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

So for 32-bit Office, we can't use your code?
Rich,

be sure you can use my code, it works. The confusing thing might be that the result values changes from positiv to neagtiv when you set the next higher bit.

Try to make the same with only 2 bytes, resp. an INTEGER, you can only store values from -32768 to 32767 into that variable.

So when you use only the positiv values up to 32767, you did not use all bits inside the INTEGER. When you try to store 32768, you get the RTE 6 => overflow. But when you set the next higher bit within the INTEGER the result value changes from positiv to negativ!

Other programming languages support UNSIGNED INTEGER for that, e.g. the data type WORD has also 2 bytes and you can store numbers from 0 to 65535.

But when we look at the bits inside the WORD resp. INTEGER, they are indentical.

For the LONG data type means that if you want to store 2^31 to set the last bit, you have to store -1, which is the same in this case.

Hmm, that sounds a little strange, I post an example tomorrow.

Andreas.

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?