How To Do 64-bit Arithmetic in VBA

Article translations Article translations
Article ID: 189862 - View products that this article applies to.
This article was previously published under Q189862
Expand all | Collapse all

On This Page

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

Properties

Article ID: 189862 - Last Review: June 30, 2004 - Revision: 3.2
APPLIES TO
  • 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
Keywords: 
kbhowto KB189862

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com