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 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.
#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
LongPtr keywords without raising a compile error.
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."
LongLong data type is a 64-bit signed integer. It also gets used surprisingly little, because of the other new "data type."
LongPtr data type is a chameleon. In 64-bit mode, it's a
LongLong. In 32-bit mode, it's a
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:
Here's the sample output from a 32-bit VBA development machine:
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:
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) #Else 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 #Else 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() Dim Mem As MEMORYSTATUS Mem.dwLength = LenB(Mem) #If Win64 Then GlobalMemoryStatusEx Mem #Else GlobalMemoryStatus Mem #End If Debug.Print "Memory load:", , Mem.dwMemoryLoad; "%" Debug.Print Debug.Print "Total physical memory:", BytesToXB(Mem.dwTotalPhys) Debug.Print "Physical memory free: ", BytesToXB(Mem.dwAvailPhys) Debug.Print Debug.Print "Total paging file:", BytesToXB(Mem.dwTotalPageFile) Debug.Print "Paging file free: ", BytesToXB(Mem.dwAvailPageFile) Debug.Print 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) #Else 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
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.
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!