I'm not sure whether this is what you want to do. You must use New against a
class name, either built-in or in your VBA project as a Class Module.

Sub TestSheetClass()
Dim obj1 As New Excel.Worksheet, obj2 As New Excel.Worksheet
obj1.Name = "New name"
Debug.Print obj2.Name
' Reset name back to original.
obj2.Name = "Start here"
End Sub

"Janis" wrote:

I am going through some example vba procedures in a book. The book is
copyrighted 2006. I'm assuming it is because I'm using excel 2007 but this
procedure doesn't work. It says "invalid use of New keyword". Can you tell
me how to fix it?

Sub TestSheetClass()
Dim obj1 As New Sheet1, obj2 As New Sheet1
obj1.Name = "New name"
Debug.Print obj2.Name
' Reset name back to original.
obj2.Name = "Start here"
End Sub

The point of this example is there can only be one name for an object but
now I wonder why it doesn't create the new object.

tia,