Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on excel 2007
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on excel 2007
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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on excel 2007
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. -- Regards, Sébastien <http://www.ondemandanalysis.com "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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro/VB Question DDE Question | Excel Worksheet Functions | |||
2007 Excel Tab Question | Excel Discussion (Misc queries) | |||
Excel 2007 question | New Users to Excel | |||
Two question about Excel 2007 | Excel Worksheet Functions | |||
Excel 2007 vba question | Excel Programming |