Add a worksheet

To add a new worksheet to the active workbook, placing it just to the left of the active worksheet:

ActiveWorkbook.Worksheets.Add

becomes:

make new worksheet at active workbook

You can always use in instead of at if it sounds better to you.

make new worksheet in active workbook

It works the same. (You may notice that AppleScript has many synonyms and alternative ways of saying the same thing.)

Again, you can set read/write properties at inception.

set newSheet to make new worksheet at active workbook with properties ¬
	{name:"Test 1", display page breaks:false}

Using Before or After in Visual Basic for Applications (VBA) allows you to place the new worksheet precisely. In VBA, to add one worksheet to the beginning of the workbook, you do the following:

ActiveWorkbook.Worksheets.Add Before:=1, Count:=1

In AppleScript, you can do it this way:

make new worksheet at beginning of active workbook

To add two worksheets to the right of the last worksheet:

With ActiveWorkbook.Worksheets
	.Add After:=.Item(.Count), Count:=2
End With

That's a little more complicated for AppleScript, since there is no way to make two or more elements at a time: make new makes just one element. However, we do have a lot of options for inserting elements.

The following are the proper ways in AppleScript of indicating an insertion location:

But they do not always work. What works here is at beginning of, at end of, but not reliably with before or after.

The property before appears to work if you don't refer to any element (that is, an existing sheet) as you're supposed to, and just write make new worksheet at before active workbook. However, all that's happening is that before is being ignored and the worksheet is made at the default location at the beginning. If you try it at before sheet 3, for example, it works once but then crashes if the new sheet 3 is not the active sheet.

For the particular VBA example here, we do not have to count the sheets. Just make a new sheet at the end, twice:

repeat 2 times
	make new worksheet at end of active workbook
end repeat

For example, if you already have four sheets, and you want to insert two more after the first one (at the left) and have no idea which sheet is currently the active one, you can try using after. However, don't omit the at as well, which sounds peculiar, but is correct. However, if you try it:

tell application "Microsoft Excel"
	tell active workbook
		repeat 2 times
			make new worksheet at after sheet 1
		end repeat
	end tell
end tell

Excel crashes on the second repeat (as noted above: sheet 1 is not active after the first time through the repeat). You may think that you cannot create new sheets in the middle by AppleScript directly — you'll have to add sheets at the beginning or end only. But, you can do it using the Standard Suite's move command.

The Excel copy worksheet command was needed to translate VBA's Copy method to AppleScript in order to get before and after parameters, but the Standard Suite's move command already has a to parameter that allows before and after insertions. The developers made use of move.

Here's how to insert two new worksheets after sheet 1:

tell application "Microsoft Excel"
	tell active workbook
		repeat 2 times
			set ws to make new worksheet at end
			move ws to after sheet 1
		end repeat
	end tell
end tell

Add a chart

To add a Chart in VBA, you use the Type argument with the Add method, on the Sheets collection. (Charts are not Worksheets, but another subvariety of Sheet.)

ActiveWorkbook.Sheets.Add Type:=xlChart

But the worksheet type property of sheet class, which includes sheet type chart as one of its types, is read-only, and cannot be set even at inception (as sometimes is the case with some commands). If you try to set the type to sheet type chart when making a new sheet, this property just gets ignored. You end up with a new worksheet as usual. (This is not unexpected, and it's not a bug. It's just an indication that AppleScript's make new with properties is not the same as VBA's Add method.)

In AppleScript, chart sheet is a class, corresponding to Chart in VBA, and is a subclass of sheet. Unlike in VBA, you can make one directly:

tell application "Microsoft Excel"
	make new chart sheet at active workbook
end tell

This is just a completely blank sheet, ready for its chart (chart is a read-write property of chart sheet, and can be set along with most of its own properties.) Charts are complex objects. For more information about charts, see Create charts.