Checking the VBA Environment

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.

Sub CheckVBA()
    ' Check for VBA 7
    #If VBA7 Then
     
        ' Determine whether this is a 64-bit environment.
        #If Win64 Then
            MsgBox "VBA 7 running in 64-bit Office"
         
        ' This is a 32-bit environment.
        #Else
            MsgBox "VBA 7 running in 32-bit Office"
        #End If
         
    ' This isn't VBA 7.
    #Else
        MsgBox "Older version of VBA"
    #End If
End Sub

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.

Sub CheckWin()
 
    ' Check for a 64-bit-specific environment variable.
    If Not Environ("ProgramFiles(x86)") = "" Then
        MsgBox "Running on 64-bit Windows"
    Else
        MsgBox "Running on 32-bit Windows"
    End If
End Sub

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.

 

Author: John

John Mueller is a freelance author and technical editor. He has writing in his blood, having produced 99 books and over 600 articles to date. The topics range from networking to artificial intelligence and from database management to heads-down programming. Some of his current books include a Web security book, discussions of how to manage big data using data science, a Windows command -line reference, and a book that shows how to build your own custom PC. His technical editing skills have helped over more than 67 authors refine the content of their manuscripts. John has provided technical editing services to both Data Based Advisor and Coast Compute magazines. He has also contributed articles to magazines such as Software Quality Connection, DevSource, InformIT, SQL Server Professional, Visual C++ Developer, Hard Core Visual Basic, asp.netPRO, Software Test and Performance, and Visual Basic Developer. Be sure to read John’s blog at http://blog.johnmuellerbooks.com/. When John isn’t working at the computer, you can find him outside in the garden, cutting wood, or generally enjoying nature. John also likes making wine and knitting. When not occupied with anything else, he makes glycerin soap and candles, which comes in handy for gift baskets. You can reach John on the Internet at John@JohnMuellerBooks.com. John is also setting up a website at http://www.johnmuellerbooks.com/. Feel free to take a look and make suggestions on how he can improve it.