Rename a worksheet with a number index

When Microsoft Excel copies a worksheet, by default it's named "Sheet(xxx)," where "xxx" is a number that increments each time a worksheet is added. In VBA, to reproduce that with another name, you can use the code in the following example:

Const sBASENAME  As String = "MySheetName"
Dim ws As Worksheet
Dim sTryName As String
Dim i As Long
Worksheets.Add After:=Sheets(Sheets.Count)
On Error Resume Next
sTryName = sBASENAME
Set ws =
ActiveWorkbook.Worksheets(sTryName)
Do Until ws Is Nothing
	Set ws = Nothing
	i = i + 1
	sTryName = sBASENAME & Format(i, " (0)")
	Set ws = ActiveWorkbook.Worksheets(sTryName)
Loop
ActiveSheet.Name = sTryName
On Error GoTo 0

In AppleScript, you do a few things differently:

property sBASENAME : "MySheetName"

tell application "Microsoft Excel"
	make new worksheet at end of active workbook
	try
		set sTryName to sBASENAME
		set ws to worksheet sTryName of active workbook
		ws -- forces an error if no such worksheet
		set i to 0 -- needs to be explicit
		repeat -- no need to set ws to null at end
			set i to i + 1
			set sTryName to sBASENAME & " (" & i & ")"
			set ws to worksheet sTryName of active workbook
			ws -- forces an error if no such worksheet
		end repeat
	on error
		set name of active sheet to sTryName
	end try
end tell

This script has 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. It's not strictly necessary because you're not taking advantage of the main virtue of script properties — persistence between runs (which you do in VBA by a very different method). Thus, it is no more effective for the purpose of having a single place to make the change than a regular set assignment, but it certainly makes the definition clear. Note that the property declaration is placed above and outside of the Excel tell block.

Error trapping, especially in connection with repeat loops, is a little different in AppleScript since the try/on error/end try structure has to be carefully positioned, unlike the 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 need two of them.

Sometimes, you also need an exit repeat within an if/end if block inside the repeat block, but you don't need that in this case.

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 or 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 Microsoft Excel, Word, PowerPoint, and the Apple "i" applications (less so with "traditional" AppleScriptable applications like Entourage) is that setting a variable to something non-existent often does not in itself 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.

Also, 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. You need to initialize i to 0 before the repeat loop begins.

There is no equivalent to VBA's Format function in basic AppleScript. Many third-party scripting additions provide equivalent functions. AppleScript has full access, via the built-in do shell script command in Standard Additions, to all of the very powerful UNIX tools that can probably out-perform Format if needed.

In this case, it's a simple matter of concatenating 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 you force an error that takes you right out of the repeat loop into the on error block outside it when you arrive at a sheet name that does not yet exist, 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 therefore to the end of the script every time. Sometimes, coding can be simpler with structured try/on error blocks.

Finally, in AppleScript, because there is no need to set variables that are application references to null (the VBA equivalent of Nothing), none of that needs to be prepared either. You're done. In this example, given the short script, there are quite a lot of syntactical differences between VBA and AppleScript.