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,417 Applies to:

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

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

Did this solve your problem?

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 feedback.

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

If this post is helpful or answers the question, please mark it so.

2 people were helped by this reply

·

Did this solve your problem?

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 feedback.

 (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

Did this solve your problem?

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 feedback.

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

Did this solve your problem?

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 feedback.

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

Did this solve your problem?

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 feedback.

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.

Did this solve your problem?

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 feedback.

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

Please can you explain how your code works?

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

Did this solve your problem?

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 feedback.

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

Did this solve your problem?

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 feedback.

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

Did this solve your problem?

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 feedback.

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.

Did this solve your problem?

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 feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.