Excel Workbooks with Macros are powerful. Most of my professional life I’ve worked with fairly complex Excel files with many macros. However, till now it was done on Windows based machines only. Recently I’ve moved to Mac and discovered that some of the Macros i’ve used for years are not designed for the Mac environments.
In a broad generalization, every time there is a reference to external resources, such as access to the file system (paths are different) or reference to DLLs (no DLLs) the macros will fail and sometimes the VBA code will not even compile.
Microsoft provides development-time and run-time methods to figure out the environment and change the code or the behavior accordingly.
As a first step let’s review the development-time ability.
The VBA compiler includes constants that are set to true or false depending on the Operating System. See the complete list here.
In this case, we want to know is the machine is running a MacOS, Windows 64 bit or Windows 32 bit. And the relevant compiler constants are Mac, Win64, Win32 respectively.
These constants can be use with the commands #IF, #ElseIf, #Else and #End IF.
For example the following code for getOSType macro will return “Windows” or “Mac” (or “Unknown”) depending on the machine type:
#If Win32 Or Win64 Then
' Windows specific code goes here
getOSType = "Windows"
#ElseIf Mac Then
' Mac specific code goes here
getOSType = "Mac"
' Not Windows or Mac
getOSType = "Unknown"
The key concept to highlight is that these conditions are tested at compile-time as appose to run-time. That means that not only that the code inside each section is executed only if the OS type is matched, but also that from the Excel perspective, the rest of the sections simply do not exist (not just not executed). For example, in cases of attempting to load DLLs this distinction is important.
Read here more about this important semantic difference between If and #If.