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 17

Perhaps if a later version of Office introduces script menus run by each application (as Entourage already has), it might be a next step to be able to drag those menu items to a toolbar as in Word's Customize feature. Some day, at any rate. I would not expect it to be a first priority.

In addition, there are several third-party utilities for automating the Mac that provide palettes or toolbars with buttons that can run scripts: QuicKeys, iKey, DragStuff all come to mind. (Look for them at <http://www.versiontracker.com>.) There are others, too. And there are ways in which you can make your own toolbars and buttons if you really want to, which I will mention in a moment.

A greater loss is that there is no way to run Auto Macros automatically when opening or closing a document, or the application. This may be an intentional decision by Microsoft in this day of security consciousness. In order for auto macros to work, scripts would first have to be stored in the documents themselves, as macros are now, as well as be provided with event procedures that watch the application and can intercept buttons and menu items.

While AppleScript is capable of implementing events ("Folder Actions" in the Finder that know when a file is added to folder are one notable example), most applications do not provide them. They have the potential to provide too much power to a scripter to interfere with the application. Storing macros, or scripts, in the documents themselves, as is essential for auto macros to function, is also what has allowed macro viruses to infest (mostly Windows) computers. The Macintosh unit of Microsoft would seem to be anxious not to allow this to happen on the Mac via their applications, and so "auto-scripts" do not appear to have a future.

You will not be able to convert your auto macros to auto scripts, but you can certainly make them into regular scripts that you run manually after launching an application or opening a document. There might be circumstances where it would be convenient to have an Entourage schedule run scripts for Word, Excel or PowerPoint on a regular schedule. [Ed. Note: This can also be achieved with cron and osascript, or scheduled via iCal.]

A third missing feature is UserForms. They are not available via AppleScript. To be honest, VBA user forms aren't all that great on the Mac anyway, stuck as they are in VBA 5.0 dating from 1997. They did not manage the transition to OS X all that well (they are looking rather dowdy), and so often forms made on Windows have not been adapted too well to the differing screen resolution on the Mac: the text and controls seem askew. It must be just too much effort, if even possible, for Microsoft to bring them up to date and also to somehow adapt them for AppleScript, which in its normal form is not in any way a UI feature.

But there is a solution, albeit a complex one. AppleScript Studio provides a glorious UI, where you can make fully-fledged Cocoa applications using AppleScript. And it's free. There is quite a learning curve – it is not for the faint of heart – but it provides a beautiful UI for your scripts with everything you could possibly want – and then some. Its visual objects are mostly made in Interface Builder, a development environment that lets you drop objects and controls of all shapes and sizes onto windows (reminiscent of User Forms, but far more sophisticated) and then control them from a specialized AppleScript dictionary that translates a good many Cocoa classes and methods into AppleScript.

The bonus is that you can also include regular AppleScript targeted at other applications such as the Office applications. (If you want to see an example of an AppleScript Studio add-on for Entourage, download my Entourage Today from <http://macscripter.net>.) You can read about AppleScript Studio at Apple's Developer website : <http://developer.apple.com/applescript/> and <http://developer.apple.com/referencelibrary/API_Fundamentals/AppleScript-fund-date.html>.

If AppleScript Studio is too overwhelming, you might want to look into a third-party scripting addition* called 24U Appearance OSAX, which provides a generous number of controls such as checkboxes, radio buttons, multiple text boxes, and so on. This is probably the most convenient solution and is easy to use: <http://www.24usoftware.com/AppearanceOSAX>. It's not free, and if you distribute your scripts to others, you would have to become involved in paying a rather hefty fee for bundling it.

(*Scripting additions extend the AppleScript language, rather like plug-ins, and are available for use when installed in a 'ScriptingAdditions' [no space] folder made in ~/Library or /Library. Apple has already installed its own collection called Standard Additions in /System/Library/ScriptingAdditions: check out its Dictionary. In the past, the liability always was that to distribute scripts that make use of third-party scripting additions, you had to depend on your users correctly installing their own copy of the same addition, something beyond the comfort zone of many users. Now, since OS 10.3, you can instead save your script as a Script Bundle or Application Bundle, place the scripting addition inside a 'Scripting Additions' [with space!] folder within the Contents/Resources folder of the bundle, and distribute the bundle. You must always check for licensing requirements, of course.)

A similar utility, but free, is Pashua <http://www.bluem.net/downloads/pashua_en/>, which requires some simple Unix-type scripting and is accessible from AppleScript. It needs to be included and installed with your scripts.

Another alternative is the free script editor Smile, already mentioned, which also has a very fully developed UI component. I honestly don't know which presents more of a learning process between AppleScript Studio and Smile, but if you are already using Smile its UI sector would be a natural extension. You'd have to bundle it for users too, but it's free (and your users would never have to look at it or learn anything about it).

Yet another solution, and an excellent one – perhaps the best – is the commercial FaceSpan <http://www.latenightsw.com/fs4/index.html>, which offers a full user-interface like AppleScript Studio (windows, panels, sheets, menus, and all varieties of widgets), but is much easier and more intuitive to use, and designed explicitly for AppleScripters. It is not cheap, however.

That about covers it, aside from one alternative without AppleScript at all: that's REALbasic <http://www.realsoftware.com/>.

Many of these "UserForms" solutions – AppleScript Studio, FaceSpan, 24U Appearance OSAX, and Smile – can also make toolbars with buttons that can run your scripts.

Note for developers: with AppleScript Studio, although the learning curve is very steep, the advantage is that the frameworks required to run the distributed applications are built into every Mac or are included in the builds. Users need to install nothing else. With FaceSpan, no separate installation is needed by users, but the builds include the frameworks and so can be large. For Smile and 24U OSAX solutions, you can now save your scripts in Script Editor and other editors as a script bundle or application bundle (a "package" that looks like a file to users), with Smile or 24U included in the bundle: in the case of 24U you can even include a "Scripting Additions" folder inside the bundle that makes it fully functioning, so again users need install nothing else. 24U allows you to include bundled versions free for free scripts and charge a developer's fee for commercial and shareware scripts. Smile is free.

The fourth missing feature is an AppleScript recorder. Microsoft used to provide a pretty good one up to Office v. X (Excel's was really quite impressive, Word's did everything by 'do Visual Basic') so it's not impossible that they'll bring one back in a later version of Office. But if you made it this far in this article, you're probably not someone who needs or enjoys a script recorder much. The limitations for a script recorder are the same as for a macro recorder – simplistic mirroring of UI actions, all done with 'selection' and no conditionals, etc. Let's pass on that.

That's the bad news. There is also some good news, which is that AppleScript can do some things VBA cannot, as well as connect you to almost everything else on your Mac. Read on.

Some Special AppleScript Features: whose filters and every element