You are currently offline, waiting for your internet to reconnect

How To Do 64-bit Arithmetic in VBA

This article was previously published under Q189862
SUMMARY
A number of Windows APIs require a 64-bit value as either a single 8-byteinteger, or as two 4-byte integers. This article contains sample code theyou can use to convert data between an 8-byte integer and two 4-byteintegers. It also contains sample code that you can use to perform simplemathematical operations on the integers, such as would be required when youincrement the file pointer in the SetFilePointer API.
MORE INFORMATION
Visual Basic for Applications provides an 8-byte integer data type, namelyCurrency. However, it is scaled by a factor of 0.0001 for input, output,and mathematical operations. This does not prevent it from being used as anargument for API calls that require a 64-bit value. This article presents anumber of conversion routines and demonstrates basic mathematicaloperations that take the scale factor into account.

There are two ways to convert between two 32-bit values and a 64-bit value:
  • You can use the LSet statement and user-defined types.
  • You can use the CopyMemory function.
This article uses the LSet method.

The sample code segments below use the following user-defined types:
Type MungeCurr     Value As Currency   End Type   Type Munge2Long     LoValue As Long     HiValue As Long   End Type				

Converting from Two 32-bit Values to a 64-bit Value

    Dim C As MungeCurr, L As Munge2Long   L.HiValue = Value1   L.LoValue = Value2   LSet C = L   Value3 = C.Value				

Converting from a 64-bit Value to Two 32-bit Values

   Dim C As MungeCurr, L As Munge2Long   C.Value = Value1   LSet L = C   Value2 = L.HiValue   Value3 = L.LoValue				

Formatting a Currency Value to Output without the Decimal Place

This routine takes the following conditions into account:
  • If the number is less than 4 digits long, it has to remove extra zeros from the left-hand side.
  • If the first condition is met, it must also remove the extra zeros but leave the sign in tact.
NOTE: This routine assumes the decimal separator is "." and the sign is "-"(and to the left of the number).
      Private Function CurrToText(ByVal Value As Currency) As String   Dim Temp As String, L As Long     Temp = Format$(Value, "#.0000")     L = Len(Temp)     Temp = Left$(Temp, L - 5) & Right$(Temp, 4)     Do While Len(Temp) > 1 And Left$(Temp, 1) = "0"       Temp = Mid$(Temp, 2)     Loop     Do While Len(Temp) > 2 And Left$(Temp, 2) = "-0"       Temp = "-" & Mid$(Temp, 3)     Loop     CurrToText = Temp   End Function

Converting a Text Field without Decimal Place to a Currency Value

This routine takes the following conditions into account:
  • The number is less than 4 digits long and requires padding.
  • If #1 is met, then it must also do additional work in the event that the number is negative.
NOTE: This routine assumes the decimal separator is "." and the sign is "-"(and to the left of the number).

   Private Function TextToCurr(ByVal Value As String) As Currency   Dim L As Long, Negative As Boolean     Value = Trim$(Value)     If Left$(Value, 1) = "-" Then       Negative = True       Value = Mid$(Value, 2)     End If     L = Len(Value)     If L < 4 Then       TextToCurr = CCur(IIf(Negative, "-0.", "0.") & _                         Right$("0000" & Value, 4))     Else       TextToCurr = CCur(IIf(Negative, "-", "") & _                         Left$(Value, L - 4) & "." & Right$(Value, 4))     End If   End Function				

Adding or Subtracting Two sets of 32-bit Values

This procedure doesn't require any special consideration. The steps are asfollows:
  1. Convert the four 32-bit values into two 64-bit values (see above).
  2. Add the 64-bit values.
  3. Convert the 64-bit result into two 32-bit values (see above).

Multiplying and Dividing 64-bit Values

When multiplying 64-bit values, you need to include an additional factor of10000 to cancel the squaring of the .0001 scale.
Multiplying: C3 = (C1 * 10000) * C2
Dividing: C3 = (C1 / C2) / 10000
NOTES:
  1. The parentheses are placed in order to preserve as much precision as possible, though it may increase the possibility of overflow.
  2. If you are using 32-bit values, convert to and from 64-bit values as outlined above.

Multiplying and Dividing by a Scalar Value

When multiplying or dividing a 64-bit value by a scalar amount (Byte,Integer, Long), you don't have to adjust for the scale.
Multiplying: C2 = C1 * 24Dividing: C2 = C1 / 3
NOTE: If you are using 32-bit values, convert to and from 64-bit values asoutlined above.

Sample Application

The following sample application demonstrates:
  • How to convert 32-bit values to 64-bit values.
  • How to convert 64-bit values to 32-bit values.
  • How to add two pairs of 32-bit values, resulting in a pair of 32-bit values.
  1. Create a new VBA project and add a form (Form1) and a CommandButton (Command1).
  2. Add the following code to the module of Form1:
     Option Explicit      Private Type MungeCurr        Value As Currency      End Type      Private Type Munge2Long        LoValue As Long        HiValue As Long      End Type      Private Function TextToCurr(ByVal Value As String) As Currency      Dim L As Long, Negative As Boolean        Value = Trim$(Value)        If Left$(Value, 1) = "-" Then          Negative = True          Value = Mid$(Value, 2)        End If        L = Len(Value)        If L < 4 Then          TextToCurr = CCur(IIf(Negative, "-0.", "0.") & _                            Right$("0000" & Value, 4))        Else          TextToCurr = CCur(IIf(Negative, "-", "") & _                            Left$(Value, L - 4) & "." & Right$(Value, 4))        End If      End Function      Private Function CurrToText(ByVal Value As Currency) As String      Dim Temp As String, L As Long        Temp = Format$(Value, "#.0000")        L = Len(Temp)        Temp = Left$(Temp, L - 5) & Right$(Temp, 4)        Do While Len(Temp) > 1 And Left$(Temp, 1) = "0"          Temp = Mid$(Temp, 2)        Loop        Do While Len(Temp) > 2 And Left$(Temp, 2) = "-0"          Temp = "-" & Mid$(Temp, 3)        Loop        CurrToText = Temp      End Function      Private Sub Command1_Click()      Dim C1 As MungeCurr, C2 As MungeCurr, C3 As MungeCurr      Dim L As Munge2Long      ' Convert a 64-bit value to two 32-bit values.        C1.Value = TextToCurr("123456789012345678")        LSet L = C1        Debug.Print CurrToText(C1.Value) & " => (" & L.HiValue & "," & _                    L.LoValue & ")"      ' Convert two 32-bit values to a 64-bit value.        L.HiValue = -1        L.LoValue = -1        LSet C1 = L        Debug.Print "(" & L.HiValue & "," & L.LoValue & ") => " & _                    CurrToText(C1.Value)      ' Add two pairs of 32-bit values and output the result as a pair      ' of 32-bit values.        L.HiValue = 33333333        L.LoValue = 44444444        LSet C1 = L        L.HiValue = -22222222        L.LoValue = 11111111        LSet C2 = L        C3.Value = C1.Value + C2.Value        LSet L = C3        Debug.Print "(33333333,44444444) + (-22222222,11111111) => (" & _                    L.HiValue & "," & L.LoValue & ")"      End Sub						
  3. Run the project and click the CommandButton.
RESULT: The following should appear in the Debug/Immediate Window:
123456789012345678 => (28744523,-1506741426)
(-1,-1) => -1
(33333333,44444444) + (-22222222,11111111) => (11111111,55555555)
REFERENCES
For additional information on using Currency to pass 64-bit values toWindows API calls, please see the following article in the MicrosoftKnowledge Base:
172338 How To Use QueryPerformanceCounter to Time Code
For additional information on CopyMemory, please see the following articlein the Microsoft Knowledge Base:
129947 INFO: Win32 Replacement for the hmemcpy Function
For additional information on using a pair of 32-bit values in Windows APIcalls, please see the following article in the Microsoft Knowledge Base:
189981 How To Seek Past VBA's 2Gb File Limit
Properties

Article ID: 189862 - Last Review: 06/30/2004 20:46:44 - Revision: 3.2

  • Microsoft Visual Basic for Applications 5.0
  • Microsoft Visual Basic 4.0 Standard Edition
  • Microsoft Visual Basic 4.0 Professional Edition
  • Microsoft Visual Basic 5.0 Learning Edition
  • Microsoft Visual Basic 6.0 Learning Edition
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • kbhowto KB189862
Feedback