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 42

4. Excel

Excel VBA Macros to AppleScript

Make sure you have read Chapter 2: AppleScript in Microsoft Office (General) first. It deals with many important features of VBA, including Excel VBA, that need to be treated very differently in AppleScript, as well as some equivalent control and operator statements in AppleScript you need to know.

This chapter consists of typical, and popular, subroutines (macros) in VBA, and their equivalent in AppleScript, with comments as necessary to explain differences in the AppleScript approach. All the many examples have been written, and provided, by John McGimpsey, Macintosh MVP specialist in Excel. Some of his explanatory comments have been incorporated as well.

Note: when testing out scripts in Script Editor, you may wish to begin every one with an activate command on its own line just inside the tell application "Microsoft Excel" block, as its first line. That will bring Excel to the front so you can see what is happening. It is not necessary to include activate when running saved scripts from the script menu while Excel is in the front (and there may be other occasions when you are running a script from elsewhere and don't want Excel to come to the front). I have not included activate in the sample scripts here, but you may wish to do so.

Before launching into scripting Excel, it is a good idea to read the introductory section Using the Excel Dictionary and especially the section How to reference cells and ranges on p. 15, of the Excel AppleScript Reference available in PDF format free from Microsoft at <http://www.microsoft.com/mac/resources/resources.aspx?pid=asforoffice>. Although it is largely aimed at AppleScripters unfamiliar with Excel's Object Model rather than vice versa, it is full of useful knowledge for all scripters of Excel.

Remember that this article was written while Office 2004 is current: some problems with Excel 2004's AppleScript discussed here may be fixed in Excel 2008 and workarounds provided here may no longer be needed there.

Excel is one of the most complex and detailed applications there is, with a huge variety of capabilities. Rather than demonstrating a few sample scripts of a somewhat personal choice, this chapter will offer AppleScript translations of short commands of various types to illustrate a wide variety of techniques, such as you might find in various combinations in many macros. Credit for assembling and organizing these samples is fully John McGimpsey's. They should be of help in many different situations and lead you on to other areas of the AppleScript dictionary.

Most of the enormous repertoire of Excel features as programmed through VBA has a straightforward equivalent in AppleScript. But here and there are Methods or Properties that have not made it over, or not in the same format. When we come upon those, solutions and workarounds will be offered.

In particular, although the AppleScript implementation of Excel has been very thoroughly carried out, such that almost all of the VBA Model can be found mirrored in the AppleScript Model, there are some serious bugs connected with columns and rows. Make sure to read the relevant section of this chapter (Working with Columns and Rows) or you will run into trouble.

The one significant gap in the AppleScript model is that Worksheet Functions did not make it over from VBA. Apparently there are conflicts between how Excel performs these and how they would have to be done piped through AppleScript, which does not have the same precision with floating point numbers. But you can enter any Function (via AppleScript) into a cell on a worksheet, let Excel do its calculations, pass the result (cell value) back to the script, and clear the cell.

We will start, as with the other application chapters, with making and opening new documents (workbooks and worksheets), then pass on to working with existing worksheets, working with ranges, and then to a few of the many other features of Excel.

New Workbook, Open Workbook, Add a Worksheet

Make a new workbook

Just a reminder, already mentioned in Chapter 2, that the way to make a new blank document equivalent to

   Application.Workbooks.Add

is

make new workbook