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 55

     tell ur

          repeat with i from 1 to count rows

              set theRow to row i

              set v to value of theRow

          end repeat

     end tell

These "rules", or behaviors, are baffling, and very hard to discover. So do keep this description on hand until you're used to it.

Another problem is that commands will not work on 'every column of someRange', or 'rows of someRange', because those do not resolve to AppleScript lists as they usually do, as mentioned at the beginning of this section. They are those bogus objects of no consequence mentioned earlier. So you really do need to use the only dependable technique for accessing single items one at a time in a repeat loop with no row or column variables, as illustrated above.

I suspect that the phantoms of the VBA Collection Objects are lurking behind this behavior. The fact that you can count rows and columns and refer to them by index – two Methods available to Rows and Columns Collection Objects in VBA – but not by address sounds rather familiar. As does the fact that you can't resolve the plurals to lists of individual items.

To make things work more smoothly, Microsoft will have to improve the structure behind columns and rows and make them more AppleScript-friendly. And cells too – they are almost as bad, if you try to use them via every cell of someRange. You can set a variable to a cell, though, because it is returned as a range. Stick to range as much as you can.

Value of Columns, Rows and Ranges as Lists

Now for some good news: in AppleScript, you can get and set the value not only of an individual cell, but also of an entire row, column, or any range, via AppleScript lists. This is how it works: the value of a range is a list of lists, with the inner lists being the rows, and the items of the inner lists being the cells. Like so:

{{1, 2, 3}, {10, 20, 30}, {100, 200, 300}, {1000, 2000, 3000}}

represents a range with 3 columns and 4 rows. The top row contains {1, 2, 3}, and so on.

A range consisting of a single row may be thought of as a single list {1, 2, 3}: you can set the value of a 3-columned row to that, if you wish, and it will work. If you ask Excel for the value, however, it will always return a list of list {{1, 2, 3}}, since a row is itself a range: a one-row-range. That is its proper representation. You can set it that way too.

A column's value is always represented like this: {{1}, {10}, {100}, {1000}} – just like any range, but where the inner lists have just one item each.

A single cell's value is returned as just the value: 1, or 10, not as a list of list. That is for convenience. And you can, and usually will, set it that way too. But you could set the value of a cell to {{10}} for example, instead of 10, and you would still see 10 in the cell.

(See the next section – Working with Ranges – for an example setting the content of every cell of a non-contiguous range all to the same value.)

Only the value property can be retrieved and set for a row, column or range as a list of lists in this way. Not even the string value property can operate with multi-cell lists.

With all this in mind, let's try out a couple of simple procedures brought over from VBA.

Selecting the Active Cell‘s Row or column

In VBA, you select the row or column of the active cell using the .EntireRow or .EntireColumn properties:

   ActiveCell.EntireRow.Select

In AppleScript, that's:

select entire column of active cell

(select is in the Standard Suite.) And it works perfectly.

Row Height, Column width, and Autofit

While row height is set by the row height property in points, columns use the column width property to set the width in number of standard numeric characters (e.g., the width of the zero, "0"), which depends on the standard font and size set in Preferences. In VBA, you have the alternative of using the Autofit command on columns and rows to reduce the sizes to the minimum height and width required for the contents. But there is a problem with autofit in AppleScript. The VBA macro writer of this next macro has used all three:

Range("1:4").RowHeight = 20   'points

Range("A:C").ColumnWidth = 20 'characters

With Range("J5:Z43")

   .Columns.AutoFit

   .Rows.AutoFit