But Will It Work on a Mac?

One of the most prevalent problems that I hear about is Macintosh compatibility. Excel for Macintosh represents a very small proportion of the total Excel market, and many developers choose simply to ignore it. The good news is that the old Excel XLS file format is compatible across both platforms. The bad news is that the features supported are not identical, and VBA macro compatibility is far from perfect.

Note  

As I write this, Microsoft has not released a compatibility pack for the Macintosh version of Excel - although, apparently, one is in the works. In addition, Microsoft has announced that future versions of Excel for Macintosh will no longer support VBA.

You can write VBA code to determine which platform your application is running. The following function accesses the OperatingSystem property of the Application object and returns True if the operating system is any version of Windows (that is, if the returned string contains the text "Win" ):

 Function WindowsOS() As Boolean     If Application.OperatingSystem like "*Win*" Then         WindowsOS = True     Else         WindowsOS = False     End If End Function 

Many subtle (and not so subtle) differences exist between the Windows versions and the Mac versions of Excel. Many of those differences are cosmetic (for example, different default fonts), but others are much more serious. For example, Excel for Macintosh doesn't include ActiveX controls. Also, it uses the 1904 date system as the default, so workbooks that use dates could be off by four years . Excel for Windows, by default, uses the 1900 date system. On the Macintosh, a date serial number of 1 refers to January 1, 1904; in Excel for Windows, that same serial number represents January 1, 1900.

Another limitation concerns Windows API functions: They won't work with Excel for Macintosh. If your application depends on such functions, you need to develop a workaround.

If your code deals with paths and filenames, you need to construct your path with the appropriate path separator (a colon for the Macintosh, a backslash for Windows). A better approach is to avoid hard-coding the path separator character and use VBA to determine it. The following statement assigns the path separator character to a variable named PathSep :

 PathSep = Application.PathSeparator 

After this statement is executed, your code can use the PathSep variable in place of a hard-coded colon or backslash.

Rather than try to make a single file compatible with both platforms, most developers choose to develop on one platform (typically Excel for Windows) and then modify the application so that it works on the Mac platform. In other words, you'll probably need to maintain two separate versions of your application.

There is only one way to make sure that your application is compatible with the Macintosh version of Excel: You must test it thoroughly on a Macintosh - and be prepared to develop some workarounds for routines that don't work correctly.