64-bit Unsigned Integers in VBA

VBA is missing a data type. The workaround--while not pretty--is at least straightforward.

64-bit Unsigned Integers in VBA

VBA is missing a data type.

There is no native 64-bit unsigned integer data type in VBA.  In practice, this is rarely an issue.  But "rarely" is not the same as "never."  To take advantage of certain Windows API calls, you'll need a workaround.

Luckily, with a bit of memory manipulation, we can simulate a 64-bit unsigned integer data type in VBA.

New Features in VBA Version 7

Let's start with a bit of background.

The last major feature release for the VBA language–version 7–came as part of Office 2010.  VBA7 provided 64-bit support for the VBA language.  Well, mostly.

Here are the new keywords introduced in VBA7:

  • #If Win64
  • #If VBA7
  • PtrSafe
  • LongPtr
  • LongLong

The #If Win64 compiler constant returns True if the code is executing in 64-bit mode.  It actually doesn't get used that much, because of the other new compiler constant.

The #If VBA7 compiler constant returns True if the code is executing in version 7 of VBA.  That's important, because it means you can use the PtrSafe and LongPtr keywords without raising a compile error.

The PtrSafe keyword is your way as the developer to say to the VBA compiler, "Yes, I reviewed this API call and it works properly in 64-bit mode."

The LongLong data type is a 64-bit signed integer.  It also gets used surprisingly little, because of the other new "data type."

The LongPtr data type is a chameleon.  In 64-bit mode, it's a LongLong.  In 32-bit mode, it's a Long.

Missing Feature in VBA7

The one omission from the above list is an unsigned 64-bit integer type.  

While it's not something you use that often, its absence is annoying when working with certain Windows API functions.  Interestingly, this is not simply a 64-bit VBA issue.  Some of these API functions require a 64-bit unsigned integer even when running in 32-bit VBA.  

For example, the API functions that return hard disk space (GetDiskFreeSpaceEx) need to support numbers larger than 32 bits, otherwise you wouldn't be able to query the size of hard disks that are larger than 4 GB.

Querying Memory Usage in 64-bit VBA

In a recent article, I wrote about how to return the available virtual memory in VBA.  

The focus of the article was on the memory limitations of 32-bit VBA, and the promise of expanding usable memory via the Large Address Aware (LAA) flag.  To help make my point, I provided some code that runs fine in 32-bit VBA.  It blew up if you tried running it in 64-bit VBA, though.  

Thanks to commenter Jörgen Rindstedt for reporting the problem:

I get a strange result: Total memory: -1987477504 B Memory used:  -1987477508 B Memory free:  4 B Pct used: 100,00%

Well, Jörgen, I have good news.  I scoured the internet for information on the topic of unsigned 64-bit integers and re-wrote the code to work for both 32-bit and 64-bit environments.

Here's the sample output when running on my 64-bit VBA development machine:

Sample output in 64-bit VBA.

Here's the sample output from a 32-bit VBA development machine:

Sample output in 32-bit VBA.

Note that in the sample output in 32-bit VBA, the Total Paging File returns a value of -1.  This indicates an overflow, as the total size of the paging file does not fit in a Long integer, which is the return type for the GlobalMemoryStatus API call.

The documentation for GlobalMemoryStatus says to use `GlobalMemoryStatusEx instead:

On computers with more than 4 GB of memory, the GlobalMemoryStatus function can return incorrect information, reporting a value of –1 to indicate an overflow. For this reason, applications should use the GlobalMemoryStatusEx function instead.

However, when I tried running GlobalMemoryStatusEx on my 32-bit VBA development machine, I got no information at all:

Sample output running GlobalMemoryStatusEx in 32-bit VBA.

Sample Code

I wrote earlier that #If Win64 is rarely necessary, since the combination of #If VBA7 and LongPtr are a simpler alternative.  However, #If Win64 exists for a reason.  And, as I also wrote earlier, "rarely" is not the same as "never."  The #If Win64 compiler constant makes several appearances in the sample code below.

Without further ado, here's the sample code:

Option Compare Database
Option Explicit

'Inspired by: https://stackoverflow.com/a/48626253/154439  (h/t Charles Williams)
#If VBA7 Then
    Private Declare PtrSafe Sub CopyMemory Lib "Kernel32.dll" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
    Private Declare Sub CopyMemory Lib "Kernel32.dll" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
#End If

#If Win64 Then
    Declare PtrSafe Sub GlobalMemoryStatusEx Lib "kernel32" (lpBuffer As MEMORYSTATUS)
    Private Type LARGE_INTEGER
        LowPart As Long
        HighPart As Long
    End Type
    'GlobalMemoryStatusEx outputs memory sizes in 64-bit *un*-signed integers;
    '   LongLong won't give us correct values because it is a signed type;
    '   the workaround is to use a custom data type and convert the result to Currency,
    '   as Currency is a fixed-point numeric data type supporting large values
    Public Type MEMORYSTATUS
       dwLength As Long
       dwMemoryLoad As Long
       dwTotalPhys As LARGE_INTEGER
       dwAvailPhys As LARGE_INTEGER
       dwTotalPageFile As LARGE_INTEGER
       dwAvailPageFile As LARGE_INTEGER
       dwTotalVirtual As LARGE_INTEGER
       dwAvailVirtual As LARGE_INTEGER
       dwAvailExtendedVirtual As LARGE_INTEGER
    End Type
    Declare Sub GlobalMemoryStatus Lib "kernel32" (lpBuffer As MEMORYSTATUS)
    Public Type MEMORYSTATUS
       dwLength As Long
       dwMemoryLoad As Long
       dwTotalPhys As Long
       dwAvailPhys As Long
       dwTotalPageFile As Long
       dwAvailPageFile As Long
       dwTotalVirtual As Long
       dwAvailVirtual As Long
    End Type
#End If

#If Win64 Then
'Convert raw 64-bit unsigned integers to Currency data type
Private Function LargeIntToCurrency(liInput As LARGE_INTEGER) As Currency
    'copy 8 bytes from the large integer to an empty currency
    CopyMemory LargeIntToCurrency, liInput, LenB(liInput)
    'adjust it
    LargeIntToCurrency = LargeIntToCurrency * 10000
End Function
#End If

Sub ShowMemStats()
    Mem.dwLength = LenB(Mem)
#If Win64 Then
    GlobalMemoryStatusEx Mem
    GlobalMemoryStatus Mem
#End If

    Debug.Print "Memory load:", , Mem.dwMemoryLoad; "%"
    Debug.Print "Total physical memory:", BytesToXB(Mem.dwTotalPhys)
    Debug.Print "Physical memory free: ", BytesToXB(Mem.dwAvailPhys)
    Debug.Print "Total paging file:", BytesToXB(Mem.dwTotalPageFile)
    Debug.Print "Paging file  free: ", BytesToXB(Mem.dwAvailPageFile)
    Debug.Print "Total virtual memory:", BytesToXB(Mem.dwTotalVirtual)
    Debug.Print "Virtual memory free: ", BytesToXB(Mem.dwAvailVirtual)
End Sub

'Convert raw byte count to a more human readable format
#If Win64 Then
Private Function BytesToXB(RawValue As LARGE_INTEGER) As String
    Dim Value As Currency
    Value = LargeIntToCurrency(RawValue)
Private Function BytesToXB(Value As Long) As String

#End If
    Select Case Value
    Case Is > (2 ^ 30)
        BytesToXB = Round(Value / (2 ^ 30), 2) & " GB"
    Case Is > (2 ^ 20)
        BytesToXB = Round(Value / (2 ^ 20), 2) & " MB"
    Case Is > (2 ^ 10)
        BytesToXB = Round(Value / (2 ^ 10), 2) & " KB"
    Case Else
        BytesToXB = Value & " B"
    End Select
End Function

Special thanks to Charles Williams, whose answer on StackOverflow heavily influenced the changes I made in the above code.  

If you would like to learn more about 64-bit unsigned integers in VBA, I posted some good articles in the Further Reading section below.

Referenced articles

Large Address Aware Flag
Getting a lot of “Out of memory” errors in newer versions of Access? This trick can more than triple your available memory in 32-bit versions of Access.
Office 2019 Runs in 64-bit Mode By Default. Here’s What That Means for VBA Developers
Through Office 2016, default installs used the 32-bit version of the software. Now that 64-bit is the default, it’s time to bite the bullet and convert your VBA code.

External references

Detecting the environment: ‘Large Address Aware’ and the 3GB Boot Switch
Can VBA detect the /3GB boot switch? Microsoft have documented it here on MSDN and it’s quite good, as MSDN pages go: they have explained what it is as well as how to use it. This question is aim...

Further reading

Windows API Guide: Faking 64-bit Integers in Visual Basic
How to get around Visual Basic’s lack of a true 64-bit integer data type.
High-precision timing with the QueryPerformanceCounter API function
While VB Timer functions is sufficiently precise for most tasks, it doesn’t provide the highest possible resolution most modern computer can provide. If your hardware supports high-resolution counters, you can do a much better job with a pair of API functions: Private Type LARGE_INTEGER lowpart As L…
Accurate Performance Timers in VBA
There are many times when you want to know how fast your code really is. Especially if you find your VBA application responding slowly you need to know where the bottleneck is occurring. You can bu…

Image by Rudy and Peter Skitterians from Pixabay

UPDATE [2022-09-09]: Fix broken link to QueryPerformanceCounter API article.

UPDATE [2023-02-19]: Bug fix for 32-bit VBA (added #If Win64 conditional compilation around LargeIntToCurrency function).  Also added screenshots for calling code in 32-bit VBA.  Thanks for reporting, Tom Wickerath!

All original code samples by Mike Wolfe are licensed under CC BY 4.0