Yes you can write macro code for Excel 2008 on the Mac using XLM macros – also known as Excel 4.0 macros
A little known feature of Excel on both Windows and Mac is XLM macros, also called Excel 4.0 macros, an old technology that dates back to 1992, before most of you even owned a computer! Another macro technology, VBA (Visual Basic for Applications) followed a year later, but XLM macros still work in all recent versions of Excel.
Why should you care? Because you can build a cross-platform compatible workbook using XLM macros. That’s right – you can open the same workbook under Excel 2004 for Mac, Excel 2008 for Mac, Excel 2003 for Windows or Excel 2007 for Windows, and the XLM macros will run the same in all these environments. Go ahead – try that with VBA! You’ll find that VBA doesn’t work at all under Excel 2008 for Mac, because Microsoft decided to remove it. They are pushing users toward AppleScript as an alternative scripting technology, but AppleScript cannot respond to events such as clicking a command button. VBA support will return in Excel 2011 for Mac, but for now, XLM macros are the only way to achieve cross-platform compatibility.
What you can do
XLM contains hundreds of functions and closely maps the capabilities of VBA. There are a few things that XLM can do which VBA cannot. Since this technology has not been updated in a long time, you’ll reach occasional stumbling points when trying to implement options that are found only in more recent releases of Excel. It is possible to have XLM macros in the same workbook with VBA code.
Documentation
Microsoft provides documentation for XLM functions in a series of Word docs that can be downloaded in a Mac .sit archive from the Microsoft Download Center. If you are a Windows user, you’ll probably find our .zip file of the same docs to be more convenient.
How XLM works
First, you will need to add a macro sheet. To do this, right click on a worksheet tab and select Insert, then choose Excel 4.0 Macro Sheet. The keyboard shortcut is Ctrl-F11 on Windows. You will see a new worksheet added called “Macro1″ and it will look like a typical worksheet except the column widths are wider by default. Macro commands are entered into cells and all cell entries begin with an equals sign. A typical macro begins with a label like “Test1″ and ends with the statement “=RETURN()”.
A Simple Example
Here is a simple example. Enter the following lines in cells A1 through A3 on your Macro1 worksheet.
| Test1 |
| =ALERT(”Hello World”) |
| =RETURN() |
Set a range name for cell A1 (Insert >Name>Define). The label “Test1″ in cell A1 is actually irrelevant; what matters is the range name you assign to it. We recommend using the same name (”Test1″ in this case). Select the radio button for the Command option in the Macro selector in the bottom right corner of the dialog. Click Add then OK. Now you can test your macro by running it. Just click on Tools> Macros>Macro and run Test1. You should see the alert box containing the text “Hello World.”
More About Dialogs
Right click on a worksheet tab again, but this time select Insert then MS Excel 5.0 Dialog. Up pops a worksheet tab called Dialog1. You can use your Forms toolbar to add text, buttons, spinners and more. The process is very similar to VBA forms.
Another Example
You can download an example of a workbook with XLM macros to maintain a simple list of employees. Each employee has a name and a position. When you click the “Add Employee” button, a dialog will appear where you can enter the name and position of a new employee.
Below you can see the macro code, which demonstrate some conditionals (IF) and looping (WHILE):
| ShowAddEmployeeForm |
| =SHOW.DIALOG(”Dialog1″) |
| =RETURN() |
| AddEmployeeOK |
| =WORKBOOK.SELECT(”Dialog1″) |
| =SET.NAME(”EmployeeName”,GET.OBJECT(12,”Edit Box 5″)) |
| =SET.NAME(”EmployeePosition”,GET.OBJECT(12,”Edit Box 7″)) |
| =WORKBOOK.SELECT(”Employees”) |
| =IF(EmployeeName=”") |
| = ALERT(”The employee name was not entered.”) |
| = RETURN() |
| =END.IF() |
| =SET.NAME(”CurrentReference”,Employees!A2) |
| =WHILE(AND(DEREF(CurrentReference)<>”",DEREF(CurrentReference)<>EmployeeName)) |
| = SET.NAME(”CurrentReference”,ABSREF(”R[1]C”,CurrentReference)) |
| =NEXT() |
| =IF(DEREF(CurrentReference)=EmployeeName) |
| = ALERT(”A record with this employee name already exists.”) |
| = RETURN() |
| =END.IF() |
| =FORMULA(EmployeeName,CurrentReference) |
| =FORMULA(EmployeePosition,ABSREF(”RC[1]“,CurrentReference)) |
| =RETURN() |