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 66

                   ("A" & (count rows)) direction toward the top)

              set rNext to get offset of lastRowCellA row offset 1

does exactly the same thing. That's almost simple enough to take the variables out again! But breaking up the code and using self-commenting variable names, if it starts to get too complicated, is usually a good idea: it will help you see things more clearly. (The VBA code was in fact originally written to grab a larger range, not a single cell. In that case the extra steps would be necessary.)

Just remember never to set a variable to a (named) column, row or cell, just to a range. It can be as simple as replacing the word "column" by "range", using the A1 style address.

AutoFilling A Series

You can create a series of numbers or dates using the autofill property:

With Worksheets("Sheet1").Range("A1")

   .Value = 1

   .AutoFill Destination:=.Resize(20, 1), Type:=xlFillSeries

End With

This is straightforward. You can omit the column size parameter since there's no change:

tell application "Microsoft Excel"

     tell range "A1" of worksheet "Sheet1"

          set value to 1

          autofill destination (get resize row size 20) type fill series

     end tell

end tell

Using Validation

Validation restricts user entries into a cell using the Validation object. For instance, an entry could be limited to positive integers:

With ActiveSheet.Range("A2").Validation

   .Delete 'remove current validation object

   .Add _

          Type:=xlValidateWholeNumber, _

          AlertStyle:=xlValidAlertStop, _

          Operator:=xlGreater, _

          Formula1:="0"

   .IgnoreBlank = True

   .InputTitle = ""

   .ErrorTitle = "Invalid Entry!"

   .InputMessage = "Enter an integer > 0"

   .ErrorMessage = "The entry MUST be a positive integer."

   .ShowInput = True

   .ShowError = True

End With

In AppleScript, you cannot delete a property, only an element. The structure of range is such that validation is a read-only property. You can modify it instead, if need be, with the modify command that uses all the same parameters as add data validation uses. Aside from that qualification, the script is just the same as the macro:

tell application "Microsoft Excel"

     tell validation of range "A2" of active sheet

          --can't delete , modify if need be

          add data validation type validate whole number ¬

              alert style valid alert stop ¬

              operator operator greater ¬

              formula1 "0"

          set ignore blank to true