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 50

          set name of active sheet to sTryName

     end try

end tell

I've given this script a script property declaration at the top, more nearly equivalent to the CONST declaration that many of these VBA macros have, although it's not strictly necessary. It does draw attention to the definition of sBASENAME, however, more than a simple

set sBASENAME to "MySheetName"

at the top would, so if you want to change the name to something else later it's very clear where to do it, as with CONST declarations. I say it's "not strictly necessary" since you're not taking advantage of the main virtue of script properties – persistence between runs (which you would do in VBA by a very different method) – and thus is no more effective for the purpose of having a single place to make the change than a regular set assignment, but certainly makes the definition clear. Note that the property declaration is placed above and outside the Excel tell block.

Error trapping, especially in connection with repeat loops, are a little different in AppleScript since the try/on error/end try structure has to be carefully positioned, unlike the freer On Error Resume Next manner of VBA. You have to work out whether your try/end try block needs to be around the repeat block, or within it, or whether you might need two of them. You might also need an exit repeat within an if/end if block inside the repeat block; we don't need that this time.

Another way to do it in AppleScript without needing a try/error block uses the exists command found in the Standard Suite and implemented by most applications, including Excel:

property sBASENAME : "MySheetName"

tell application "Microsoft Excel"

     tell active workbook

          set i to 1

          repeat until (worksheet (sBASENAME & " (" & i & ")") ¬

              exists) = false

              set i to i + 1

          end repeat

          make new worksheet at end with properties ¬

              {name:sBASENAME & " (" & i & ")"}

     end tell

end tell

This is actually simpler and clearer to read, with the added advantages of not needing extra variables nor those extra lines required to force errors. It does involve "thinking in AppleScript" rather than merely "translating" VBA literally, and is the sort of thing you should start doing as you become more familiar with the language.

One unusual thing about some newer scriptable applications like Excel, Word, PPT and the Apple iApps – less so with "traditional" AppleScriptable applications like Entourage – is that setting a variable to something non-existent often does not in itself necessarily cause an error. The error occurs when you next use the variable. That is why you have to immediately follow both statements setting ws to a worksheet that may not exist with an immediate call to the variable – it's an implicit get – to force the error.

You must check when writing your scripts if such a call is necessary, or else just do it as a matter of course. Do not assume an error will occur otherwise. If not, an error may not appear until later in the script (the first time you need to access ws) at a point where you have not trapped specifically for this one: it could be something else.

Next – in AppleScript there is no default value 0 for an increment variable, as in VBA: the variable is undefined. If you write

   set i to i + 1

without first setting i to 0, the script will error. We need to initialize i to 0 before the repeat loop begins, of course.

There is no equivalent to VBA's Format function in basic AppleScript. Many third-party scripting additions provide equivalent functions, and AppleScript has full access, via the built-in do shell script command in Standard Additions, to all the very powerful Unix tools that can probably out-perform Format if needed. On this occasion, it's a very simple matter just to concatenate the incrementing variable i into a string expression between two parentheses. A string to the left of the concatenation operator & coerces a number on its right to a string, so there is no need for an explicit coercion as string.

Because an error will be forced when you arrive at a sheet name that does not yet exist, taking you right out of the repeat loop into the on error block outside it, there is no need for any sort of while or until condition in the repeat statement nor for an exit repeat in an if block anywhere. You are guaranteed to exit straight into the on error block, and thence to the end of the script every time. It makes life a lot simpler sometimes having structured try/on error blocks!