Microsoft has made some significant changes in VBA 7.0 that aren’t discussed in VBA for Dummies because the book came out before Office 2010 was released. All of them are good and necessary changes for the environment that Office 2010 supports. Of course, one of the biggest changes for Office 2010 users is that Office now supports both a 32-bit and a 64-bit environment, so you need a way to check for this condition. It turns out that all sorts of programming functionality changes in a 64-bit environment, including the use of Windows API pointers. Calls to the Windows API that worked just fine in previous versions of VBA may not work in VBA 7.0 when you’re working with a 64-bit version of Office.
Because users often install software that support doesn’t know about, you can’t assume that users will follow the rules and use only the 32-bit version of Office 2010. As a consequence, you must make some changes to your code to ensure it handles any environment thrown at it. Fortunately, Microsoft also makes it possible to check the environment using the following code pattern.
Notice the use of the specialized If…Then construction. The #If statement differs from the standard If statement. Because VBA7 isn’t defined in older versions of VBA, the #Else clause is executed. Likewise, only the 64-bit version of Office defines the Win64 constant, so the 32-bit version of Office 2010 will execute the #Else clause.
It’s essential to understand that the Win64 constant refers to the version of Office, not the version of Windows. You can run the 32-bit version of Office 2010 on a 64-bit Windows system without any problem. When you execute this macro on a 64-bit Windows system with 32-bit Office 2010 installed, you’ll still see the “VBA 7 running in 32-bit Office” message. When you think about it, this response makes sense because the version of Office is important, the version of Windows is less so.
Of course, there are times when you also need to know which version of Windows you’re using. The check is easy. Make sure you use an environment variable that only appears on the version of Windows you’re using. Checking for 64-bit Windows is easiest using this code.
The “ProgramFiles(x86)” only comes with 64-bit Windows. Now that you know how to check the various VBA 7.0 permutations, I’ll look at some VBA 7.0-specific programming in my next post. Let me know if you have any questions or suggestions at John@JohnMuellerBooks.com.