32-bit vs. 64-bit VBA: Deep Dive

When Microsoft introduced 64-bit support with Office 2010, it also released a new version of VBA: VBA 7.

VBA 7 had one mission and one mission only: provide language support for 64-bit development.  No other features would be added to the language.  It was nearly identical to VBA 6 except for the introduction of seven things:

1. The PtrSafe keyword (for marking API declares as safe to execute in 64-bit mode)
2. The LongLong data type (which holds 64-bit signed integers)
3. The CLngLng() function (to convert values to LongLong)
4. The LongPtr data type (which is an alias for Long in 32-bit VBA and LongLong in 64-bit VBA)
5. The CLngPtr() function (to convert values to LongPtr)
6. The VBA7 compiler constant (e.g., #If VBA7 Then)
7. The Win64 compiler constant (e.g., #If Win64 Then)

Additionally, the ObjPtr(), VarPtr(), and StrPtr() functions–which return the memory addresses of objects, variables, and strings, respectively–now return LongPtr values rather than Long values.

For more details about these changes, check out the official documentation here: Compatibility Between the 32-bit and 64-bit Versions of Office 2010.

The VBA Declare Statement

While the vast majority of VBA code written for the 32-bit only versions of VBA continue to work just fine in 64-bit VBA, there is one major exception: API Declare statements.

In VBA, the Declare keyword can be used if you want to call a DLL function (often written in another language, such as C++) from your VBA code.  The Declare statement tells VBA what sort of data to send to (and receive from) the DLL.  As far as VBA is concerned, the DLL function itself is a black box: VBA passes it some binary data and (in most cases) gets some other binary data back in return.  

It's the miracle of COM programming:

Memory Pointers in C++ and VBA

As a low-level programming language, C++ concerns itself with pointers (i.e., memory addresses) A LOT.

Direct manipulation of memory is what makes C++ so fast, so powerful, and so dangerous.  You have to be extraordinarily careful to clean up after yourself when writing code in C++.  Memory-related errors lead to hard crashes, insanely difficult to debug errors, and security vulnerabilities.

One of the reasons the original Visual Basic programming language (hereafter referred to as "VB6" for simplicity) was so popular in the 1990's is that it managed to hide most of the memory manipulation tasks. As Moore's law led to exponentially faster hardware, maximizing performance took a back seat to maximizing readability and maintainability of the codebase. Instead of worrying about managing memory, developers could concentrate on providing useful features.  

The VB6 language itself provided read-only access to pointers via the aforementioned ObjPtr(), VarPtr(), and StrPtr() functions.  Direct manipulation of pointers was limited to API calls.  

As a result of this design decision, VB6 had no dedicated type to hold pointer data. Memory addresses were 32 bits long–same as a 32-bit Long integer–and so the existing Long data type performed double duty.  It was used to store both 32-bit signed integers AND pointers (i.e., memory addresses).

The LongPtr Data Type

With the introduction of 64-bit programming in VBA 7 (VB6 got left out of the 64-bit party entirely, likely as a cynical business decision to force as many VB6 developers onto the .NET platform as possible), there was suddenly a problem: 32-bit integers and 64-bit pointers could no longer share the same data type.

As a solution, Microsoft introduced the LongPtr "data type."  I put data type in scare quotes because it's more properly understood as an alias for two different data types:

• The Long data type in 32-bit VBA environments
• The LongLong data type in 64-bit VBA environments

When code is running in a 32-bit VBA environment, the compiler translates LongPtr to Long.

When code is running in a 64-bit VBA environment, the compiler translates LongPtr to LongLong.

The LongPtr data type is a convenience to simplify writing VBA code that needs to work properly with Windows API calls in both 32-bit and 64-bit VBA environments.  

It is important to note that the bitness of the VBA environment is distinct from the bitness of the operating system.  It's not uncommon to run 32-bit Office on a 64-bit machine.

Converting from Long to LongPtr

For many (most?) VBA developers, Windows API calls are seen as dark magic.

(Until very recently, I was solidly in this camp myself.  Reading the discussions around the twinBASIC project and writing this blog have provided me with a much better understanding of what's actually going on behind the scenes.  I am still by no means an expert. From time to time, Ben Clothier and others will drop by in the comments to gently correct my misunderstandings and set the record straight, which I greatly appreciate.)

If Access developers have API calls in their code at all, it's likely because they were copied and pasted there from one of a handful of the early Access sorcerers, such as Ken Getz, Dev Ashish, or Stephen Lebans.  Those code samples almost exclusively predate the release of Office 2010, meaning none of them have 64-bit compatibility.

This reality caused a bit of an existential crisis for developers like me when 64-bit support first appeared on the horizon.  Here I was with a bunch of code that I didn't really understand, but that I apparently was going to have to update anyway or all my applications would break.

As a programmer, I was looking for an algorithm that I could apply to my existing code to ease this transition.  My first thought when I read about 64-bit VBA and the LongPtr type is that the fix was to go through each Declare statement in my code and change every occurrence of Long to LongPtr.  Unfortunately, it wasn't that easy.

Eventually, I came to understand that some–but not all–instances of Long data types needed to be changed to LongPtr.  Additionally, the vast majority of those changes did occur within Declare statements (though not all).  

All that said, most instances of Long to LongPtr conversions appear in two places:

• Windows API Declare statements
• Calling code that references the subroutines and functions defined in those Declare statements

Of course, that still left the question of which specific Long arguments needed to be changed to LongPtr.  And, unless you're one of the handful of VBA developers who is comfortable reading C++ header files, your best bet is to refer to a document or tool that shows the 64-bit-safe Declare statements for each Windows API call.

The two best references for this purpose that I know of are:

• Peter Cole's MS Access Windows API Viewer
• The Win32API_PtrSafe.txt file

For general 32-bit to 64-bit conversion advice, check out these pages from fellow Access MVPs Philipp Stiefel and Colin Riddington:

• How to convert Windows API declarations in VBA for 64-bit, by Philipp Stiefel
• 32 to 64-bit Conversion, by Colin Riddington

The PtrSafe Keyword

The first time you try to compile code with API calls (in the form of Declare statements) in 64-bit VBA, you are greeted with the following error message:

Compile error:

The code in this project must be updated for use on 64-bit systems.  Please review and update Declare statements and then mark them with the PtrSafe attribute.

Once again, for cargo cult API programmers like me at the time, there was a very simple way to get the above error message to go away: simply find and replace "Declare" with "Declare PtrSafe" throughout your entire codebase.  This overly simplistic (and, to be crystal clear, WRONG) approach brings to mind one of my favorite quotes (from journalist H.L. Mencken):

For every complex problem there is an answer that is clear, simple, and wrong.

To better understand how to use the PtrSafe attribute, it helps to understand why it was added to VBA7 in the first place.

The PtrSafe keyword exists purely for the sake of humans, not the compiler.

Unlike nearly every other VBA keyword, the PtrSafe attribute has no effect on the output of the compiler.  It functions merely as the syntactical equivalent of a checkbox on a to-do list.

Let's go back to our discussion on the Long to LongPtr conversion from above.  Many instances of Long in API Declare statements needed to be changed to LongPtr to achieve 64-bit compatibility.  But not all of them!  In many cases, the 32-bit-only and 32-bit/64-bit compatible versions of an API Declare statement are identical.  Unless one of the arguments or the return type involve memory addresses (i.e., pointers), then chances are you don't need to make any special changes to the Declare statement at all.

Unfortunately, that poses a different problem.

The compiler can't simply look to see whether each Declare statement has the LongPtr data type.  As we just wrote, some Declare statements won't have any arguments with a type of LongPtr or a return value of type LongPtr.  However, not applying the Long to LongPtr change where it is needed, can be severe–often resulting in hard crashes or other unpredictable behavior.  

Given these facts, it was clear to those on the VBA 7 team that programmers would need a way to explicitly indicate that they had reviewed the Declare statement and applied any necessary Long to LongPtr conversions, as needed.

Thus was born the PtrSafe attribute.

Once the developer reviews a Declare statement and makes any necessary changes in the name of 64-bit compatibility, they then add the PtrSafe attribute immediately after the Declare keyword.

The PtrSafe keyword is the VBA developer's stamp of approval telling the compiler that the current Declare statement may be considered "pointer-safe."

The "Compile error" message box from above will continue to appear until all Declare statements have been marked with the PtrSafe attribute.

The VBA7 Compiler Constant

It's not uncommon for end users of an Access application to be running several different versions of Access.  

This was especially true before the introduction of Microsoft 365 (née Office 365). More to the point, when 64-bit VBA first appeared in Office 2010, many Access users were running fully supported versions of Office (2007, 2003, etc.) that did not have support for the new PtrSafe keyword.

So, the following code–which works with both the 32-bit and 64-bit versions of VBA7 (Office 2010 and later)–would fail to compile in Office 2007 and earlier because the PtrSafe keyword was not yet a part of the language:

Private Declare PtrSafe Function apiGetParent _
    Lib "user32" Alias "GetParent" (ByVal hWnd As LongPtr) As LongPtr

Thus, the VBA7 compiler constant was added.  Incorporating this new compiler constant made it so that a single code base could provide support for both Office 2007 and earlier as well as 64-bit versions of Office 2010 and later.

The above code could be rewritten as follows, making it safe to run in all versions and bitnesses of Access, including 32-bit and 64-bit versions of Access 97 and later:

#If VBA7 Then
    Private Declare PtrSafe Function apiGetParent _
        Lib "user32" Alias "GetParent" (ByVal hWnd As LongPtr) As LongPtr
#Else
    Private Declare Function apiGetParent _
        Lib "user32" Alias "GetParent" (ByVal hWnd As Long) As Long
#End If

The Win64 Compiler Constant

As I mentioned way back in the beginning of this article, Microsoft introduced a second new compiler constant with the release of VBA 7: Win64.

The Win64 compiler constant returns true when VBA is running in 64-bit mode and false when VBA is running in 32-bit mode.  It's important to note here that this compiler constant relates to the Office bitness, not the operating system's bitness.  Almost all Windows operating systems are 64-bit systems and have been for many years.  However, there is still a very sizable chunk of 32-bit Office installations, since 32-bit was the default installation option through Office 2016.

The above code that uses the VBA7 conditional compile constant would work equally well using the Win64 conditional compile constant:

#If Win64 Then
    Private Declare PtrSafe Function apiGetParent _
        Lib "user32" Alias "GetParent" (ByVal hWnd As LongPtr) As LongPtr
#Else
    Private Declare Function apiGetParent _
        Lib "user32" Alias "GetParent" (ByVal hWnd As Long) As Long
#End If

In fact, it could also be written slightly differently (by switching LongPtr to LongLong) and still compile to the same byte code:

#If Win64 Then
    Private Declare PtrSafe Function apiGetParent _
        Lib "user32" Alias "GetParent" (ByVal hWnd As LongLong) As LongLong
#Else
    Private Declare Function apiGetParent _
        Lib "user32" Alias "GetParent" (ByVal hWnd As Long) As Long
#End If

VBA7/LongPtr vs. Win64/LongLong

Let's look at the two approaches and see how they stack up:

VBA7/LongPtr

#If VBA7 Then
    Private Declare PtrSafe Function apiGetParent _
        Lib "user32" Alias "GetParent" (ByVal hWnd As LongPtr) As LongPtr
#Else
    Private Declare Function apiGetParent _
        Lib "user32" Alias "GetParent" (ByVal hWnd As Long) As Long
#End If

Win64/LongLong

#If Win64 Then
    Private Declare PtrSafe Function apiGetParent _
        Lib "user32" Alias "GetParent" (ByVal hWnd As LongLong) As LongLong
#Else
    Private Declare Function apiGetParent _
        Lib "user32" Alias "GetParent" (ByVal hWnd As Long) As Long
#End If

Now, let's look at how the compiler will handle these two approaches in the following situations:

• 32-bit VBA6 (e.g., Access 2007 and earlier)
• 32-bit VBA7 (e.g., Access 2010 and later in 32-bit mode)
• 64-bit VBA7 (e.g., Access 2010 and later in 64-bit mode)

Note that the fourth combination–64-bit VBA6–is not possible, since VBA6 does not include 64-bit support.

32-bit VBA6

VBA7/LongPtr

In this case, the VBA7 compiler constant evaluates to False, and the following line of code gets executed:

Private Declare Function apiGetParent _
    Lib "user32" Alias "GetParent" (ByVal hWnd As Long) As Long

Win64/LongLong

In this case, the Win64 compiler constant evaluates to False, and the following line of code gets executed:

Private Declare Function apiGetParent _
    Lib "user32" Alias "GetParent" (ByVal hWnd As Long) As Long

In other words, for Access 2007 and earlier, both approaches result in identical compiled code.

32-bit VBA7

VBA7/LongPtr

In this case, the VBA7 compiler constant evaluates to True, and the following line of code gets executed:

Private Declare PtrSafe Function apiGetParent _
    Lib "user32" Alias "GetParent" (ByVal hWnd As LongPtr) As LongPtr

However, as we discussed earlier, LongPtr is nothing more than an alias for Long and LongLong, depending on the Office bitness.  Since we are dealing with 32-bit VBA in this example, our LongPtr values get translated to Long.  So, the above line of code gets further compiled to:

Private Declare PtrSafe Function apiGetParent _
    Lib "user32" Alias "GetParent" (ByVal hWnd As Long) As Long

Win64/LongLong

In this case, the Win64 compiler constant evaluates to False, and the following line of code gets executed:

Private Declare Function apiGetParent _
    Lib "user32" Alias "GetParent" (ByVal hWnd As Long) As Long

In other words, for 32-bit versions of Access 2010 and later, both approaches result in identical compiled code.

64-bit VBA7

VBA7/LongPtr

In this case, the VBA7 compiler constant evaluates to True, and the following line of code gets executed:

Private Declare PtrSafe Function apiGetParent _
    Lib "user32" Alias "GetParent" (ByVal hWnd As LongPtr) As LongPtr

Since we are dealing with 64-bit VBA in this example, our LongPtr values get translated to LongLong.  So, the above line of code gets further compiled to:

Private Declare PtrSafe Function apiGetParent _
    Lib "user32" Alias "GetParent" (ByVal hWnd As LongLong) As LongLong

Win64/LongLong

In this case, the Win64 compiler constant evaluates to True, and the following line of code gets executed:

Private Declare Function apiGetParent _
    Lib "user32" Alias "GetParent" (ByVal hWnd As LongLong) As LongLong

In other words, for 64-bit versions of Access 2010 and later, both approaches result in identical compiled code.

To recap, both approaches yield identical compiled code in all three situations, though how they get there varies slightly, especially for the 32-bit VBA7 scenario. Given these facts, it's fair to ask whether one approach is really better than the other.

In my well-informed opinion, I believe the VBA7/LongPtr approach is superior.  Let me explain.

VBA7: The Transitional Compiler Constant

The main reason I favor the VBA7/LongPtr approach is that it requires fewer lines of code in the long run.

In our earlier analysis, the two approaches each required five lines of code (I'm counting the line continuations as a single line of code).  Baked into those approaches, though, were two key assumptions:

• Users could be running 32-bit or 64-bit versions of Office
• Users could be running 2007 and earlier or 2010 and later versions of Office

That first statement is likely to be true for many more years, at a minimum.  However, the second statement is decreasingly likely to be true.

Office 2007 has now been out of support for more than six years (end of life was October 10, 2017).  Furthermore, Office 2010 and Office 2013 are also out of support now (as of October 13, 2020 and April 11, 2023, respectively).

Now that we've had two perpetual VBA7-supported versions of Office reach end of life, I feel comfortable dropping support for Office 2007 and earlier in both my applications and my writing.

If we take the approach that we will no longer support software that Microsoft stopped supporting over six years ago, then we can adjust our assumptions:

• Users could be running 32-bit or 64-bit versions of Office
• Users will not be running any versions of Office older than Office 2010

Let's re-write our code samples from earlier given these updated assumptions:

VBA7/LongPtr

Private Declare PtrSafe Function apiGetParent _
    Lib "user32" Alias "GetParent" (ByVal hWnd As LongPtr) As LongPtr

Win64/LongLong

#If Win64 Then
    Private Declare PtrSafe Function apiGetParent _
        Lib "user32" Alias "GetParent" (ByVal hWnd As LongLong) As LongLong
#Else
    Private Declare Function apiGetParent _
        Lib "user32" Alias "GetParent" (ByVal hWnd As Long) As Long
#End If

If we were to stick with the Win64 approach, we would be committing to writing a lot of unnecessary boilerplate code for the foreseeable future.

Will Microsoft drop support for 32-bit VBA someday?  I imagine so.  But even if they ended 32-bit support with the next perpetual release (which I highly doubt they will), Office 2019 will still be supported for another six years Office 2021 will still be supported for another three years, meaning we'll need to support 32-bit and 64-bit runtimes for at least that long.

Ultimately, the VBA7 conditional compile constant was only ever meant to serve as a transitional tool.  With the ubiquity of VBA7 in the marketplace, the need for the VBA7 conditional compile constant is greatly diminished.

UPDATE [2023-12-20]: Microsoft has changed its lifecycle policy for Office.  Extended support for Office 2021 (the newest perpetual Office product as of writing) ends on October 13, 2026.  (h/t Karl Donaubauer)

Additional Reading