question on excel 2007

[Janis]

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,

 

[Ilia]

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

 

[sebastienm]

Hi,
It's not an xl2007 error.
You cannot create new instance of Sheet1 (Dim obj1 As New Sheet1).
You can however create two variables pointing to the one and single instance
Sheet1

Dim obj1 as worksheet , obj2 as worksheet
Set obj1=Sheet1
Set obj2 = Sheet1
obj1.name = "New name"
debug.print obj2.name ''' same as obj1.name
''' reset
obj2.name="Start here"

You could also do
Dim obj1 as Sheet1, obj2 as Sheet1
but the only thing you say is that obj1 and obj2 are variables of type Class
Sheet1 (like the template of sheet1, not the object itself)
So you still need to do
Set obj1 = Sheet1 ''' assign to the object/instance Sheet1 (not the
class)
Set obj2=sheet1
Now, obj1 and obj2 points to the same object.