Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I access a sub in a New Class
From project "VBAProject(Book1.xlsm):"I am trying to access sub
"subInNewClass" in a new class that is in Project "newclass(class test.xlsm)". Both methods below produce errors. Any help would be appreciated. ----------------------------------- In Project newclass(class test.xlsm) In Class Module Class1: Option Explicit Public Sub subInNewClass() MsgBox "I'm in" End Sub In VBAProject(Book1.xlsm): Set reference to Project newclass In Userform1 Option Explicit Dim qq As New Class1 Private Sub UserForm_Click() qq.subInNewClass End Sub ----------------------------------------------------------- I also tried: Option Explicit Dim qq As Class1 Dim kk Private Sub UserForm_Click() Set kk = qq kk.subInNewClass End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I access a sub in a New Class
Difficult to follow your code because nothing like that's going to work.
First ensure Instancing for Class1 is PublicNotCreatable In a normal module in the same project Function NewClass1() As Class1 Set NewClass1 = New Class1 End Function Ensure the project has a unique name, rename VBProject to say ClassTest Save the file (might be an idea to close and reopen it) In another wb set a reference to ClassTest, and in a normal module something like the following Sub test() Dim c As ClassTest.Class1 Set c = ClassTest.NewClass1 MsgBox TypeName(c) ' type c and a dot and should get the intellisense End Sub Regards, Peter T "Mike" wrote in message om... From project "VBAProject(Book1.xlsm):"I am trying to access sub "subInNewClass" in a new class that is in Project "newclass(class test.xlsm)". Both methods below produce errors. Any help would be appreciated. ----------------------------------- In Project newclass(class test.xlsm) In Class Module Class1: Option Explicit Public Sub subInNewClass() MsgBox "I'm in" End Sub In VBAProject(Book1.xlsm): Set reference to Project newclass In Userform1 Option Explicit Dim qq As New Class1 Private Sub UserForm_Click() qq.subInNewClass End Sub ----------------------------------------------------------- I also tried: Option Explicit Dim qq As Class1 Dim kk Private Sub UserForm_Click() Set kk = qq kk.subInNewClass End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I access a sub in a New Class
Thanks Peter. It took me a while to understand what you were getting at. But
after everything fell into place, it gave me exactly what I was looking for. Thanks again!!! "Peter T" <peter_t@discussions wrote in message ... Difficult to follow your code because nothing like that's going to work. First ensure Instancing for Class1 is PublicNotCreatable In a normal module in the same project Function NewClass1() As Class1 Set NewClass1 = New Class1 End Function Ensure the project has a unique name, rename VBProject to say ClassTest Save the file (might be an idea to close and reopen it) In another wb set a reference to ClassTest, and in a normal module something like the following Sub test() Dim c As ClassTest.Class1 Set c = ClassTest.NewClass1 MsgBox TypeName(c) ' type c and a dot and should get the intellisense End Sub Regards, Peter T "Mike" wrote in message om... From project "VBAProject(Book1.xlsm):"I am trying to access sub "subInNewClass" in a new class that is in Project "newclass(class test.xlsm)". Both methods below produce errors. Any help would be appreciated. ----------------------------------- In Project newclass(class test.xlsm) In Class Module Class1: Option Explicit Public Sub subInNewClass() MsgBox "I'm in" End Sub In VBAProject(Book1.xlsm): Set reference to Project newclass In Userform1 Option Explicit Dim qq As New Class1 Private Sub UserForm_Click() qq.subInNewClass End Sub ----------------------------------------------------------- I also tried: Option Explicit Dim qq As Class1 Dim kk Private Sub UserForm_Click() Set kk = qq kk.subInNewClass End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I access a sub in a New Class
On May 3, 6:09*am, "Mike" wrote:
Thanks Peter. It took me a while to understand what you were getting at. But after everything fell into place, it gave me exactly what I was looking for. *Thanks again!!! "Peter T" <peter_t@discussions wrote in message ... Difficult to follow your code because nothing like that's going to work.. First ensure Instancing for Class1 is PublicNotCreatable In a normal module in the same project Function NewClass1() As Class1 * *Set NewClass1 = New Class1 End Function Ensure the project has a unique name, rename VBProject to say ClassTest Save the file (might be an idea to close and reopen it) In another wb set a reference to ClassTest, and in a normal module something like the following Sub test() Dim c As ClassTest.Class1 Set c = ClassTest.NewClass1 MsgBox TypeName(c) ' type c and a dot and should get the intellisense End Sub Regards, Peter T "Mike" wrote in message news:FfKdnc8MVZCHBkDWnZ2dnUVZ_j6dnZ2d@pghconnect. com... From project "VBAProject(Book1.xlsm):"I am trying to access *sub "subInNewClass" in a new class that is in Project "newclass(class test.xlsm)". Both methods below produce errors. Any help would be appreciated. ----------------------------------- In Project newclass(class test.xlsm) In Class Module Class1: * * * * Option Explicit * * * * Public Sub subInNewClass() * * * * * * *MsgBox "I'm in" * * * * End Sub In VBAProject(Book1.xlsm): Set reference to Project newclass In Userform1 * * * * Option Explicit * * * * Dim qq As New Class1 * * * * Private Sub UserForm_Click() * * * * * * * qq.subInNewClass * * * * End Sub ----------------------------------------------------------- I also tried: * * * * Option Explicit * * * * Dim qq As Class1 * * * * Dim kk * * * * Private Sub UserForm_Click() * * * * * * * * *Set kk = qq * * * * * * * * *kk.subInNewClass * * * * End Sub- Hide quoted text - - Show quoted text - Sorry Peter n Mike I couldn't get the thing correctly What i expected was that to call the sub from the class module and use in the module (by calling from the module) If i am not worng, there is no reference in the peters post about class module. How sub from a class be called from the normal module. Let me make it more clear. in a class module msgbox "This is in a class module" '''''' '''''''' in a normal module sub callfromclass() '''''' code goes here end sub And the output should be that there should be a message box. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I access a sub in a New Class
"Subodh" wrote in message ... On May 3, 6:09 am, "Mike" wrote: Thanks Peter. It took me a while to understand what you were getting at. But after everything fell into place, it gave me exactly what I was looking for. Thanks again!!! "Peter T" <peter_t@discussions wrote in message ... Difficult to follow your code because nothing like that's going to work. First ensure Instancing for Class1 is PublicNotCreatable In a normal module in the same project Function NewClass1() As Class1 Set NewClass1 = New Class1 End Function Ensure the project has a unique name, rename VBProject to say ClassTest Save the file (might be an idea to close and reopen it) In another wb set a reference to ClassTest, and in a normal module something like the following Sub test() Dim c As ClassTest.Class1 Set c = ClassTest.NewClass1 MsgBox TypeName(c) ' type c and a dot and should get the intellisense End Sub Regards, Peter T "Mike" wrote in message news:FfKdnc8MVZCHBkDWnZ2dnUVZ_j6dnZ2d@pghconnect. com... From project "VBAProject(Book1.xlsm):"I am trying to access sub "subInNewClass" in a new class that is in Project "newclass(class test.xlsm)". Both methods below produce errors. Any help would be appreciated. ----------------------------------- In Project newclass(class test.xlsm) In Class Module Class1: Option Explicit Public Sub subInNewClass() MsgBox "I'm in" End Sub In VBAProject(Book1.xlsm): Set reference to Project newclass In Userform1 Option Explicit Dim qq As New Class1 Private Sub UserForm_Click() qq.subInNewClass End Sub ----------------------------------------------------------- I also tried: Option Explicit Dim qq As Class1 Dim kk Private Sub UserForm_Click() Set kk = qq kk.subInNewClass End Sub- Hide quoted text - - Show quoted text - Sorry Peter n Mike I couldn't get the thing correctly What i expected was that to call the sub from the class module and use in the module (by calling from the module) If i am not worng, there is no reference in the peters post about class module. How sub from a class be called from the normal module. Let me make it more clear. in a class module msgbox "This is in a class module" '''''' '''''''' in a normal module sub callfromclass() '''''' code goes here end sub And the output should be that there should be a message box. ============================== The point to keep in mind is in VBA Class modules can only be created from within the their own project. However, if Instancing is changed from default Private to PublicNonCreatable, the class can be used in any other project, subject to the appropriate Reference being set. (FYI in say a VB6 dll Instancing can be changed to MultiUse (or GlobalMultiUse) which allows the Class to be created from outside its own project, but N/A in VBA) So the way to do it is for a public function in a normal module in the Class' project to create the Class, then pass it to the calling function in the other workbook (which must of course have a reference to the Class project) Make sure you have changed Instancing as described, - changed the project name to something unique (eg ClassTest), - included a public procedure in a normal module to create class and pass it - saved the file, - set the reference to ClassTest in the other workbook FWIW, instead of the function NewClass1 (see my previous post) could change it to a Property, eg ' in a normal module same project as Class1 Property Get propClass1() As Class1 Set propClass1 = New Class1 End Property ' in the calling workbook Dim c As ClassTest.Class1 Set c = ClassTest.propClass1 Now you should be able to use c as if Class1 existed in the same project Regards, Peter T |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I access a sub in a New Class
I probably should have mentioned before, if intending to use the same class
instance in multiple workbooks, I'd strongly suggest maintaining only one reference to it in the Class's own project. A reference to the class can be obtained as and when required by other workbooks ideally with a Property Get and passing whatever details are required to identify the class. The actual references of course could be stored in say an Array or Collection declared at module level in the Class workbook If multiple references are maintained to a Class instance there could be problems in "tearing down" leading to memory leaks. Regards, Peter T "Mike" wrote in message om... Thanks Peter. It took me a while to understand what you were getting at. But after everything fell into place, it gave me exactly what I was looking for. Thanks again!!! "Peter T" <peter_t@discussions wrote in message ... Difficult to follow your code because nothing like that's going to work. First ensure Instancing for Class1 is PublicNotCreatable In a normal module in the same project Function NewClass1() As Class1 Set NewClass1 = New Class1 End Function Ensure the project has a unique name, rename VBProject to say ClassTest Save the file (might be an idea to close and reopen it) In another wb set a reference to ClassTest, and in a normal module something like the following Sub test() Dim c As ClassTest.Class1 Set c = ClassTest.NewClass1 MsgBox TypeName(c) ' type c and a dot and should get the intellisense End Sub Regards, Peter T "Mike" wrote in message om... From project "VBAProject(Book1.xlsm):"I am trying to access sub "subInNewClass" in a new class that is in Project "newclass(class test.xlsm)". Both methods below produce errors. Any help would be appreciated. ----------------------------------- In Project newclass(class test.xlsm) In Class Module Class1: Option Explicit Public Sub subInNewClass() MsgBox "I'm in" End Sub In VBAProject(Book1.xlsm): Set reference to Project newclass In Userform1 Option Explicit Dim qq As New Class1 Private Sub UserForm_Click() qq.subInNewClass End Sub ----------------------------------------------------------- I also tried: Option Explicit Dim qq As Class1 Dim kk Private Sub UserForm_Click() Set kk = qq kk.subInNewClass End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Class module to access range events | Excel Programming | |||
To access a previous object via class module | Excel Programming | |||
How to access group buttons in Class Module? | Excel Programming | |||
Reference Class Module in Access from Excel | Excel Programming | |||
Access to class property in other workbook | Excel Programming |