Knowledge Base

How To Do 64-bit Arithmetic in VBA

Article ID: 189862

Article Last Modified on 6/30/2004

APPLIES TO

This article was previously published under Q189862

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

172338 How To Use QueryPerformanceCounter to Time Code

For additional information on CopyMemory, please see the following article in 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 API calls, please see the following article in the Microsoft Knowledge Base:

189981 How To Seek Past VBA's 2Gb File Limit

Keywords: kbhowto KB189862


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.