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 56

End With

The first two statements translate perfectly to AppleScript, as we will see in a moment. There is nothing difficult about setting row height and column width.

To use autofit, which can be a great convenience, you need to loop it for each column separately, as we have just seen in the discussion about columns and rows. Nothing happens if you try it on columns as a plural, or every column.

That is manageable. Much worse is that autofit does not work at all for rows, not even on a single row. Something is badly wrong there: it actually messes up the columns instead, reducing the width of many of them and destroying any autofit you did for them, and doesn't do anything for the rows. It looks to me as if it is taking the instructions for rows and using them on columns instead by mistake. Fortunately, 99% of the time your rows are likely to be all the same height. If any need to be sized differently, you'll have to set their row height individually.

So, the way you have to convert the macro above is like this:

tell application "Microsoft Excel"

     set row height of range "1:4" to 20 -- points (rows)

     set column width of range "A:C" to 20 -- characters (columns)

     tell range "J5:Z43" of active sheet

          repeat with i from 1 to (count columns)

              autofit column i

          end repeat

          -- now see if here are any rows whose row height

          -- has to be adjusted, and do them separately

     end tell

end tell

Working with Ranges

Alert: If you skipped the previous section on Columns and Rows, please go back and read about a series of serious bugs in the AppleScript implementation, which can affect ranges too if you refer to them as column, row or cell in your scripts rather than as range.

Ranges are the basis for most manipulation of Excel using VBA and AppleScript. Note that ranges are child objects of worksheet, and so cannot contain cells in multiple worksheets.

Note that AppleScript refers to ranges by address in A1 format as it does "by name" in other applications: that is, the keyword range is followed directly, after a space, by the address in quotes, without the parentheses that VBA uses:

VBA: Range("A1:J10")                                             AppleScript: range "A1:J10"

There's one minor "gotcha" that should be mentioned at this point. Most people have discovered, in both VBA and AppleScript, that if they write simply Range("A1:J10") in VBA, or range "A1:J10" or cell "A1" in AppleScript, without specifying any sheet, Excel defaults to the active sheet. However, for some reason, if you refer to used range:

set ur to used range

or

cell 3 of row 4 of (get used range)

without specifying of active sheet, no result is returned and the next line of the script referencing the result will error. It is always best practice, in any case, to target the sheet, perhaps via tell active sheet around as much of the script as needs it (just as in VBA you would write With ActiveSheet). This minor bug will keep you honest! If you are in the habit of omitting active sheet, you will hit perplexing errors when you need to access the used range, as happens in a great many scripts. So it's best not to get into that particular habit.

Selecting or setting a variable to a Range

In VBA, ranges are specified using the Range Property applied to a Worksheet (or to another Range), or Range‘s Cells property applied to a Range. (The Cells property is the default property for the Range object, so it generally is not explicitly called by name. You might see just parentheses with two numbers representing the row and column of the cell respectively.) There is no "cells" property of a range or worksheet in AppleScript: just use the appropriate number to get the cell (column number) of row (row number), as in the second line of the next AppleScript snippet. I.e., where in VBA you write .Cells(10, 6) or just .(10, 6), in AppleScript you write cell 6 of row 10.

Ranges can be selected and set directly.