Moving from Microsoft Office VBA to AppleScript:
MacTech's Guide to Making the Transition

Page Prev and Page Next buttons at bottom of the page.

April, 2007
Page 1

Converting VBA Macros to AppleScript in Microsoft Office

VBA is going away in Office 2008 for Mac. Learn about the challenges and the benefits.

The Coming Event

Within months, the next version of Microsoft Office for Mac, Office 2008, will be out. (Or perhaps by the time you are reading this, if you are coming to it belatedly, it is already out.) From the information released at the time of writing (March 2007), it‘s clear that there will be good reasons for many to upgrade to the new version, even if you don‘t feel you always have to have the latest of everything.

For one thing, it will be a Universal Binary, running natively (and fast) on Intel, as well as PPC, Macs. It will be able to create files in the new XML file format used by Office 2007 for Windows, so you won‘t be left out of the new features and robustness provided by the new format. (Office 2004 will be able to open and read these files with new converters to be released imminently, but will not be able to save in the new format. You‘ll need Office 2008 for that.)

Microsoft has begun to tell us more about what the new User Interface will look like in Office 2008, previewing the new Elements Gallery at Macworld in January, along with other brand new features in the new Office for Mac. And, there will undoubtedly be much more – perhaps the very feature requests or bug fixes you‘ve been waiting for.

But there is a loss as well: Visual Basic for Applications, the programming language used for macros in Word, Excel and PowerPoint, that functions (with proper precautions taken) cross-platform on both the Mac and in Windows versions of the same Office applications, will be no more. It is being removed from Mac Office 2008.

Why? Why remove something so useful? The reason is that Microsoft needed to re-develop Office 2008 to be a Universal application as Apple has made its move to Intel processors. The VBA compiler was designed for a much earlier PPC Mac, and will not work on Intel Macs. All VBA macro code is compiled at runtime, and needs the compiler. The compiler code (and VB Editor code, and code for VB forms too) could not have been converted without some two years' work, we are told, interrupting development of and most likely seriously delaying the Universal version of Office for Mac.

The Windows version of VBA could not be brought over because it depends on execution of Windows machine assembly. Furthermore, VBA on Windows will also be going away in a few more years (Ed. Note: since the writing of this article, Microsoft has indicated that VBA support will be here for the foreseeable future.) , so doing the conversion would represent a huge investment for a vanishing language. (Many would say there is some hope that some years down the line, whatever replaces VBA – probably some form of .NET technology – can be made thoroughly cross-platform again.) If you are interested, check out the blog post of Erik Schwiebert ("Schwieb"), a Microsoft Macintosh developer, here: <http://www.schwieb.com/blog/2006/08/08/saying-goodbye-to-visual-basic/>. Schwieb gives a number of technical details and other information regarding the decision.

So what is the solution? Well, you could stick with Office 2004, running in Rosetta on Intel Macs, forever and ever. But you‘d thereby miss out on all the new features of Office 2008 (and versions beyond that). Furthermore, macros found in the new "m" file formats of Office 2007 on Windows (.docm, .xlsm, .pptm) will not run nor be converted in Office 2004 (nor 2008, nor any version of Office Mac). And, eventually, Rosetta may disappear in some future Mac OS, as Classic already has done for those on Intel Macs. If that happens, you would have to stick with older, out of date Macs and OS's in order to keep on using Office 2004 and Rosetta. Or, you could investigate alternative automation technologies such as AppleScript – which we will be discussing in this document.

The people who will be affected most by the loss of VBA are those who need cross-platform programmability for Microsoft Office. There may be some options out there for cross platform developers to control Office externally. For example, if cross platform is a necessity, one may want to wait and see whether REALbasic <http://www.realsoftware.com/users/productivity/officeautomation/> will be releasing a version of their Office Automation software that will work with a version of Office that has removed its VBA. But, this and other possible solutions are outside the scope of this document.

Most Mac users will want to convert their macros to AppleScript as there‘s not only rich AppleScript in Office now, with more to come, but also it gives the huge benefit of being able to weave Mac OS X and other non-Microsoft applications into one‘s solutions.

Office 2004 completely revised and introduced enormous new AppleScript implementations for Word, Excel and PowerPoint. (Entourage, which exists only on the Mac, has always had an excellent AppleScript implementation, and has never had VBA support.) AppleScript in Word, Excel and PowerPoint virtually mirrors the VBA Object Model for these applications. That‘s because both languages access ("hook into") a deeper-level framework called OLE Automation.

So virtually everything you could do in VBA, you can do in AppleScript (the few exceptions will be discussed). In addition, AppleScript opens up the whole world of the Mac to you. A lot of other applications on the Mac are also AppleScriptable, so you can set up automated inter-application workflows, sending data from FileMaker Pro to Excel and vice-versa, even PowerPoint to Photoshop, Entourage to Word to InDesign, or whatever. And, you get to take advantage of Apple‘s new Automator technology, which is especially promising as Microsoft has announced it will be supporting Office 2008 with Automator features. The entire Mac world is now your automated oyster.

These articles will introduce you to AppleScript, to its rather individual syntax, with pointers to resources where you can learn more, will review VBA conversion to AppleScript basics applicable Office-wide, then delve a little more deeply into conversions of sample macros in Word, Excel and PowerPoint demonstrating some important elements in action, and end with some examples of integrating the various applications with Entourage.

Why now? If you have a lot of macros, this will take some time, so it‘s best to start converting while your macros are still working in Office 2004. Moreover, don‘t forget that when you upgrade to Office 2008, you‘ll no longer even be able to open or view your macros there! So I recommend keeping Office 2004 around on your computer even after upgrading, for just this purpose. (The two versions should not cause any conflicts. Right now it‘s perfectly possible to keep Office v. X and 2004 – even 2001 on PPC computers with Classic – simultaneously, although it‘s probably not a great idea to have them running at the same time. There are a few tricks needed for using a Script Editor with more than one version of an application, which I will explain below.)

Better yet, get started now in Office 2004, which can display both VBA macros and scripts in Script Editor simultaneously, and be ready when Office 2008 hits the streets to explore whatever new features (and surely some scripting features too) it introduces, with your scripts ready, waiting – and functioning.

For the most part, the following pages will assume you know a bit of VBA – that you‘re the one who either wrote the VBA macros or tinkered with macros designed for Office Windows to adapt them to your Macs. You may or may not yet know some AppleScript as well, but will need to learn now. There are pointers to resources, and tips to how to go about it, below.

About the author   Paul Berkowitz is an expert both in AppleScript and on Microsoft Office for Macintosh. He wrote the chapter on AppleScript in Office 2004 for Macintosh: The Missing Manual and was Technical Editor for several books on AppleScript including AppleScript: The Definitive Guide, 1st Edition by Matt Neuburg, AppleScript: The Missing Manual, and others. He is well known for more than 100 AppleScripts he has written and released for Microsoft Entourage, especially those that export and import almost all Entourage data and that sync Entourage to Apple's Address Book and iCal. He is a Microsoft MVP for Macintosh Office, helping out users in the Microsoft newsgroups in his spare time, and wrote the article on 'do Visual Basic' for the Mac Word MVP website. He also has a completely separate life as a classical concert pianist and professor of piano.