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.
As recently as Office 2016, Microsoft recommended installing the 32-bit version of its flagship office suite.
That default setting changed to 64-bit as of Office 2019/Office 365/Microsoft 365:
API Declares Require PtrSafe in 64-bit Mode
While there are several compatibility issues to be aware of when updating your code for 64-bit VBA, one of the most common is the requirement to update your API declarations with the PtrSafe keyword.
Many API calls include arguments that are pointers to memory locations. In 32-bit VBA, those memory addresses are 32 bits long. In 64-bit VBA, those memory addresses are 64 bits long.
VBA 7 introduced a couple of new data types to handle this situation:
- LongLong: a 64-bit integer
- LongPtr: a 32-bit integer when running in 32-bit mode and a 64-bit integer when running in 64-bit mode
In VBA 6 and earlier, there was no need to distinguish between a long integer that referred to a memory address and a long integer that referred to a plain old number. That's because VBA 6 had no 64-bit mode. All memory addresses were 32 bits long.
Beginning in VBA 7, you can begin to update your API declarations. If the Long argument of an API function represents a plain old number, then it should remain declared as a Long integer in VBA 7. However, if the VBA 6 Long argument actually refers to a memory address, then the argument should be declared as a LongPtr in VBA 7.
There is no across-the-board rule for how to update every API declaration from VBA 6 to VBA 7. You can't simply do a find and replace of Long with LongPtr in all of your Declare statements.
Philipp Stiefel has the most comprehensive tutorial for upgrading API declarations at: Windows API declarations in VBA for 64-bit.
Can't I Just Ostrich This Thing a Little Longer?
If your approach to the arrival of 64-bit VBA has been to keep your head in the sand and pretend it does not exist, I have some bad news for you. That approach won't work much longer.
Up until Office 2016, you could tell your clients that your Access application only works with 32-bit Office and, oh by the way, that's the version of Office that Microsoft officially recommends you install.
Beginning with Office 2019, that argument no longer holds water. What's more, if you insist that your users install the 32-bit version of Office, you will need to explain the explicit extra steps they will need to take to run the non-default Office install.
What If I Install a 32-bit Access Runtime Alongside 64-bit Office?
Yes, that would be very nice if it worked, wouldn't it?
There was a time in the not-too-distant past when you could safely run multiple Office versions side-by-side. As long as you started by installing the oldest version first, everything worked pretty well.
Things started to fall apart with that approach around the time Office/Access 2007 was released. The 2007+ versions of Office/Access would spend a minute or more rewriting registry entries at startup if you switched between versions. That was annoying, but at least it was an option.
Unfortunately, installing a 32-bit Access runtime alongside a 64-bit version of Office simply DOES NOT WORK.*
* Actually, if you still have a copy of Office/Access 2003 or earlier, you CAN install that side-by-side with a 64-bit version of Office. Of course, those versions of Access are waaaaaay out of support now.
Bite the Bullet: Install 64-bit Access on Your Dev Machine
I knew for years that 64-bit VBA would supplant 32-bit VBA one day. But none of my clients were running 64-bit VBA. I was not running 64-bit VBA.
I set up a virtual machine and installed a 64-bit runtime to test my applications. I started converting one to run in 64-bit mode, but there was no urgency. I proved the concept and then did nothing about it for years.
Finally, I realized that if I waited much longer I was going to run out of runway. I had to do something to force myself to convert my applications for 64-bit compatibility.
I installed 64-bit Office on my main development machine. I still had a laptop with 32-bit Office that I could use for emergencies and applications that I could not upgrade due to 32-bit library dependencies.
By forcing myself to open every application in 64-bit mode, I was confronted with the errors. I finally worked to resolve them.
Low - Medium - High
When Microsoft recommended the 32-bit version of Office (through Office 2016), converting your VBA code for 64-bit compatibility was a low priority. That time has passed.
Now that Microsoft recommends the 64-bit version of Office (beginning with Office 2019), converting VBA for 64-bit compatibility should now be a medium priority for you.
If you serve corporate clients, I recommend you bite the bullet and convert your Access applications for 64-bit compatibility now. If you don't, you'll end up doing it as a high priority when their policy changes and they no longer allow 32-bit Office installations on their network.
External references
Image by Gerhard G. from Pixabay
UPDATE [2021-07-29]: Reworded the final paragraph to clarify why conversion to 64-bit VBA could become a high priority in the not-too-distant future.
