Binary from 32-bit integer

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

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

 (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

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

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

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.

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

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

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

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

Rich,