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 54

You get a sort of bogus single object – a single row or column of no dimensions, rather than a list of items as you should. You cannot then get any individual items from what ought to be a list – your script errors if you try. It also means that you cannot do repeat loops of this sort:

     set theRows to rows of (get used range of active sheet)

     repeat with theRow in theRows

          set v to value of theRow

     end repeat

The script just "jumps" over this as if it's not there (try it in a line-by-line debugger editor and you'll see it happen), and then errors as soon as anything tries to use a result or variable set to anything in the loop.

However, you can get there another way since, even more strangely, count (every row) or count rows (the same thing) works fine! You can't get rows, but you can get (count rows)! So you can iterate through a repeat loop getting row i, where i is looping from 1 to (count rows).

Even stranger – and this one is very hard to fathom – you cannot set a variable to a row or column along the way because once again you get no result! The reference to a row or column seems to lose its connection to the real thing whenever it is evaluated to a variable. The only thing that works is a pure application reference without variables, drilling through each element along the way.

So you must do it this way:

     tell (get used range of active sheet)

          repeat with i from 1 to count rows

              set v to value of row i

          end repeat

     end tell

Wait until you've got the final result you need before setting a variable to that (e.g., the value property here), never to the row or column or cell along the way. It's perfectly all right to set a variable to something referred to as a range, however, whether used range or a range by address (range "A1:A46"), including ranges that actually are rows or columns, but not to anything that was defined as, for example, row 5 or column 8. So this is also OK, where the variable ur is set to a range:

      set ur to (get used range of active sheet)

     tell ur

          repeat with i from 1 to count rows

              set v to value of row i

          end repeat

     end tell

but not this, where the variable theRow is set to a row:

      set ur to (get used range of active sheet)