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 53

     end repeat

end tell

AppleScript's text operators – all except AppleScript's text item delimiters (more on them later) – are case insensitive unless you've placed them inside a considering case block. So we do not need to go through any contortions as in the VBA code to compare any word with "(Hide)" or "(hide)". And although standard AppleScript without scripting additions does not have wildcards or regular expressions, it does have contains, which does the job perfectly.

Now here's a case where every worksheet whose… does not work, because Excel can't deal with a property (e.g., visible) of such a construction, as mentioned at the end of Chapter 3.

set visible of (every worksheet in active workbook ¬

          whose its name contains "(Hide)") to false

--> ERROR: Can't set visible of every worksheet of active workbook whose name contains "(Hide)" to false.

So here you need to use a repeat loop.

But there's a subtle twist you need to be aware of: the VBA code above used Not – i.e., ws.Visible = False – as a synonym for the enumeration xlHidden. To prevent the worksheet from appearing even in the Format/Sheet/Unhide dialog, it would instead set the Visible property to xlVeryHidden:

   ActiveSheet.Visible = xlVeryHidden

In AppleScript, that's:

set visible of active sheet to sheet very hidden

The correct enumeration for the visible property is, in AppleScript: sheet hidden, sheet very hidden and sheet visible, equivalent to VBA's xlHidden, xlVeryHidden and xlVisible. You can use true and false as synonyms for sheet visible and sheet hidden respectively if you wish, just as in VBA.

This is a bit clearer in the Object Browser of VB Editor if you check the actual Longs (numbers) of the enumerated constants. But it works in AppleScript too.

This is very convenient in making these VBA-to-AppleScript conversions, as I mentioned back in the open text file section: if the VBA macro writer used numbers instead of the enumerated xl or vb constants, you can generally just transcribe the same numbers into your AppleScript code even though it's not explicitly defined in the AppleScript dictionary which enumerated constants they refer to, nor even that these "number equivalents" exist for the constants.

Working with Columns and Rows

Alert: There are some very serious AppleScript bugs in Excel 2004 to do with columns and rows. Something was implemented incorrectly when the Columns and Rows Collection Objects were adapted for AppleScript. Thus:

     row 5 of (get used range of active sheet)

     column 7 of range "A1:P26" of active sheet

     range "G1:G26" of range "A1:P26" of active sheet

all produce something that is useful and functional, namely a range. The result is never referred to as a column or row, always as a range. But      

     column "G1:G26" of range "A1:P26" of active sheet

(i.e., referring to it as a column, rather than as a range) produces nothing – no result. So you can refer to columns and rows by index (number), never by address. Perhaps this will not be unexpected coming from VBA, where you would typically refer to Columns(7) and Rows(5). It doesn't jibe with what you would expect from the dictionary, where column, row and cell are all subclasses of range and inherit all its properties. That means you ought to be able to do the same with column, row and cell as you do for range, but you can't. For most purposes, if you stick to referring to columns and rows by index, and use range when using an address, things will be smoother.

Worse, and stranger, is that you cannot get anything useful for:

     rows of (get used range of active sheet)

     columns of range "A1:P26" of active sheet