In Visual Basic for Applications (VBA), worksheets can be renamed by setting their name property:

ActiveSheet.Name = "New Sheet Name"

In AppleScript:

set name of active sheet to "New Sheet Name"

You can cause an error if the sheet name is invalid, or if a sheet already has that name. One way to avoid this problem in VBA is shown in the following example:

Const sNAME = "New Sheet Name"
On Error Resume Next
ActiveSheet.Name = sNAME
If ActiveSheet.Name <> sNAME
Then MsgBox "Re-name failed"
On Error GoTo 0

In AppleScript, you can accomplish the same thing, as shown in the following example:

tell application "Microsoft Excel"
	try
		set name of active sheet to "New Sheet Name"
	on error
		display dialog "Re-name failed." buttons {"Cancel"} with icon 0
	end try
end tell

In this example, the code does not fail silently, but creates a true error that you can trap.

On Error Resume Next
With ActiveSheet
	.Name = .Range("A1").Text
End With
On Error GoTo 0

try
	set name of active sheet to string value of range "A1" 
end try