Back in December, I wrote about Microsoft’s decision to drop support for Visual Basic for Applications (VBA) from the next version of Office. I had, and still have, many concerns about the switch. The biggest concern is that this change means the end of true cross-platform integration between the Windows and OS X versions of Office. Windows users still have Visual Basic, but if you receive a macro-enabled Word or Excel document from one of them, the macros won’t work in the Mac version of Office. I still think this decision was fundamentally flawed, and that it doesn’t bode well for the future of the Mac version of Office. Only time will tell, of course, as the product isn’t even out yet.
Another concern I voiced was the difficulty involved in writing AppleScripts for Office 2008 versus the ease of creating simple macros in Word and Excel using today’s Record Macro menu item. Back in December, I wrote:
I don’t know about you, but selecting Tools -> Macro -> Record New Macro certainly strikes me as much simpler than learning a new programming language—I don’t want to be a programmer, I just want to write and use some simple macros in my projects!
While nothing has changed relative to the issue of cross-platform support, things are looking up a bit on the AppleScripting front. Neil Ticktin, publisher of MacTECH, was kind enough to send me a draft of a 150+ page VBA to AppleScript guide that MacTECH will be running in their April 2007 issue. This guide is divided into six main sections—AppleScript in general, AppleScript in Office in general, and then specific sections for each of the four Office applications. Of special note is the chapter on Entourage, which is really about using AppleScript in Entourage, as it’s never had VBA support.
So while I may still have to learn a new programming language, MacTECH is providing a resource to make that transition as simple as possible. You can read more about the guide in this writeup over on the MacTECH website. (If you want to save some money, pay attention to the bit about Microsoft’s Macintosh Business Unit’s sponsorship of this special guide; you can pick it up for a trivial amount of money if you qualify and subscribe before March 31st.)
I haven’t had time to read the entire guide yet, but it looks to be an excellent resource for anyone contemplating the transition from VBA to AppleScript. There are literally hundreds of examples of VBA code, followed by their AppleScript translations. For instance, to create a new Excel worksheet at the front of the current workbook using VBA, the code would look like this:
ActiveWorkbook.Worksheets.Add Before:=1, Count:=1
The MacTECH VBA to AppleScript guide presents the AppleScript alternative:
make new worksheet at beginning of active workbook
The guide continues in this style, explaining each translation, as well as pointing out possible “gotchas” in the move from VBA to AppleScript, like this one:
…in AppleScript there is no default value 0 for an increment variable, as in VBA: the variable is undefined. If you writeset i to i + 1without first settingito0, the script will error.
The examples get more complex, of course, demonstrating things such as creating new charts in Excel, exporting Entourage contacts to Excel, page setup and printing, delete all empty rows in a Word table, and many more. If you’re a power user of VBA on the Mac version of Office, MacTECH’s guide seems to be a great way to get up to speed quickly with the change to AppleScript-based solutions in Office 2008.
For me, though, there’s still a bigger question: will there be any benefit to the work required to convert my VBA macro library to AppleScript? As Office 2008 won’t ship until later this year, I can’t answer that question quite yet. However, even if I don’t move forward with a conversion project, I’ll still put this guide to good use when I want to build a macro solution in the new version of Office.