Advanced

64-bit Unsigned Integers in VBA

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

Mike Wolfe

Nov 22, 2021 • 7 min read

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:

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:

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)
#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

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.