HOWTO: Do 64-bit Arithmetic in VBA

ID: Q189862

The information in this article applies to:

Microsoft Visual Basic for Applications version 5.0
Microsoft Visual Basic Standard and Professional Editions, 32-bit only, for Windows, version 4.0
Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, versions 5.0, 6.0

SUMMARY

A number of Windows APIs require a 64-bit value as either a single 8-byte integer, or as two 4-byte integers. This article contains sample code the you can use to convert data between an 8-byte integer and two 4-byte integers. It also contains sample code that you can use to perform simple mathematical operations on the integers, such as would be required when you increment the file pointer in the SetFilePointer API.

MORE INFORMATION

Visual Basic for Applications provides an 8-byte integer data type, namely Currency. 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 an argument for API calls that require a 64-bit value. This article presents a number of conversion routines and demonstrates basic mathematical operations 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 as follows:

Convert the four 32-bit values into two 64-bit values (see above).

Add the 64-bit values.

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 of 10000 to cancel the squaring of the .0001 scale.

Multiplying: C3 = (C1 * 10000) * C2
Dividing: C3 = (C1 / C2) / 10000

NOTES:

The parentheses are placed in order to preserve as much precision as possible, though it may increase the possibility of overflow.

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 * 24 Dividing: C2 = C1 / 3

NOTE: If you are using 32-bit values, convert to and from 64-bit values as outlined 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.

Create a new VBA project and add a form (Form1) and a CommandButton (Command1).

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

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 to Windows API calls, please see the following article in the Microsoft Knowledge Base:

Q172338 HOWTO: Use QueryPerformanceCounter to Time Code

For additional information on CopyMemory, please see the following article in the Microsoft Knowledge Base:

Q129947 INFO: Win32 Replacement for the hmemcpy Function

For additional information on using a pair of 32-bit values in Windows API calls, please see the following article in the Microsoft Knowledge Base:

Q189981 HOWTO: Seek Past VBA's 2Gb File Limit

Additional query words:

Keywords : KbVBA kbVBp400 kbVBp500 kbVBp600
Version : WINDOWS:4.0,5.0,6.0
Platform : WINDOWS
Issue type : kbhowto