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 57

Range("A1").Select

ActiveWorksheet.Cells(10, 6).Select

Set rMyRange = Range("B10:C14")

becomes:

tell application "Microsoft Excel"

     select range "A1"

     select (cell 6 of row 10 of active sheet)

     set myRange to range "B10:C14"

end tell

You can use the get resize command, in the Table Suite (where most commands acting on ranges are to be found), where VBA uses the .Resize Property (or Method) to expand or choose a range:

Range("A1").Resize(10, 10).Select   ’Selects A1:J10

becomes:

select (get resize range "A1" row size 10 column size 10) --selects A1:J10

Setting a non-contiguous range to a single value

In the last section Working with Columns and Rows we learned how to set the values of a range to a list of lists (rows). VBA is also full of handy shortcuts such as the following:

      Range("A1:B5,G9,A16:D19") = 5

The Range address describes a non-contiguous range consisting of the rectangle containing 10 cells (2 columns of 5 rows) within A1:B5, the single cell G9 off on its own, and the discrete rectangle A16:D19 containing 16 cells (4 columns of 4 rows). The statement is a sort of shortcut (you can't actually set such a range to the integer 5) that sets the value of every cell in the range to 5. It works.

Again, it works because VBA has Default properties which don't need to be spelled out explicitly if you're in a hurry. Cells is the Default property of range, and Value is the Default property of Cell.

At first glance, it doesn't look as if you can do this in AppleScript. But you can – as long as you state the properties explicitly, since there are no Default properties in Excel AppleScript:

set value of every cell of range "A1:B5,G9,A16:D19" to 5

This is even "pure" AppleScript: no "shortcut" required. It sets the value of every cell of that peculiar, disjointed, non-contiguous range to 5. all in one go, just as the VBA version does.

Copying and pasting a Range

The simplest way to copy a range from one location to another is to use the copy range command equivalent to VBA's Copy method:

With ActiveSheet

   .Range("A1:J10").Copy Destination:=.Range("M1")

End With

becomes:

tell application "Microsoft Excel"

     copy range range "A1:J10" destination range "M1"

end tell

You'll notice a quite common repetition of keywords (e.g., range) from commands to direct objects to parameters, all running on. Don't leave any out. You'll see that the shorthand of specifying just the top-left initial cell for the destination range is sufficient in AppleScript as well as in VBA. And without saying so until now, we've sometimes been neglecting to include 'of active sheet' and that is taken for granted as the default, just as in VBA.

Note that copy range copies values, formatting, conditional formatting, validation, etc.

You can paste the contents of the clipboard into the active cell using the paste worksheet command (in the Excel Suite this time), equivalent to the VBA Paste method, or, using the optional destination argument, into a particular cell.

ActiveWorkbook.Sheets("Sheet1").Range("A1:J10").Copy

With Workbooks("Book2.xls").Sheets("Sheet1")

   .Paste Destination:=.Range("J3")

End With

becomes:

tell application "Microsoft Excel"

     copy range (range "A1:J10" of sheet "Sheet1" of active workbook)