Developing Excel Workbooks with Windows/Mac compatible Macros

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:

Function getOSType()
#If Win32 Or Win64 Then
' Windows specific code goes here
getOSType = "Windows"
#ElseIf Mac Then
' Mac specific code goes here
getOSType = "Mac"
#Else
' Not Windows or Mac
getOSType = "Unknown"
#End If
End Function

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.

Advertisements

Author: dave

Consider myself kid in soul and naive by choice. I am interested in people, technology and business and thrive when they all work together. My favorite quote and motto is that “You can fool some people some times but you cant fool all the people all the time” ― Bob Marley

1 thought on “Developing Excel Workbooks with Windows/Mac compatible Macros”

  1. On the Mac, a DLL (Dynamic Link Library) is called a DYLIB (Dynamic Link Library) and, while the code within would be clearly different, a DYLIB should provide comparable functionality when accessed under Excel. The differences should be largely confined to the interface with the Unix-based software (vs. Windows-based) with the functional aspects of the code pretty much the same as both PC’s and Mac’s are running on Intel processors.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s