Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing classes defined in another workbook
Hi,
This may be more of a deployment issue than a programmatic one. My question is: I have defined a number of useful classes that i would like to use in various excel workbooks (ie. VBA projects). How can i programmaticaly use these classes without having to define individual and identical class modules for each workbook (VBA project). Doing it this way makes maintenance a nightmare if i have a change in the class definition; that means i'll have to change all the workbooks where this class is defined as well It would be better if i could define the class and make it "global" so that all workboods could create instances of that class without having the class module definition reside in that same workbook. Any suggestions would be greatly apprecaited. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing classes defined in another workbook
Bing,
Not possible directly. What you can do is to create a public function in the workbook with the classes that accesses the class like so Public Function CreateClass1() As clsClass1 Set CreateClass1= New clsClass1 End Function and access that from your other workbook, like so Dim myClass as Object Set myClass = CreateClass1() You need to use a generic object type as the class object is not known in that workbook, and you will need to have set a reference to the class containing workbook in your other workbook (VBEToolsReferences), and it will need to have been saved -- HTH RP (remove nothere from the email address if mailing direct) "Bing" wrote in message ... Hi, This may be more of a deployment issue than a programmatic one. My question is: I have defined a number of useful classes that i would like to use in various excel workbooks (ie. VBA projects). How can i programmaticaly use these classes without having to define individual and identical class modules for each workbook (VBA project). Doing it this way makes maintenance a nightmare if i have a change in the class definition; that means i'll have to change all the workbooks where this class is defined as well It would be better if i could define the class and make it "global" so that all workboods could create instances of that class without having the class module definition reside in that same workbook. Any suggestions would be greatly apprecaited. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing classes defined in another workbook
Hi Bob,
Very smart!!! It worked. Created the classes in an Add-in. Thanks for the response Regards, Bing C. "Bob Phillips" wrote: Bing, Not possible directly. What you can do is to create a public function in the workbook with the classes that accesses the class like so Public Function CreateClass1() As clsClass1 Set CreateClass1= New clsClass1 End Function and access that from your other workbook, like so Dim myClass as Object Set myClass = CreateClass1() You need to use a generic object type as the class object is not known in that workbook, and you will need to have set a reference to the class containing workbook in your other workbook (VBEToolsReferences), and it will need to have been saved -- HTH RP (remove nothere from the email address if mailing direct) "Bing" wrote in message ... Hi, This may be more of a deployment issue than a programmatic one. My question is: I have defined a number of useful classes that i would like to use in various excel workbooks (ie. VBA projects). How can i programmaticaly use these classes without having to define individual and identical class modules for each workbook (VBA project). Doing it this way makes maintenance a nightmare if i have a change in the class definition; that means i'll have to change all the workbooks where this class is defined as well It would be better if i could define the class and make it "global" so that all workboods could create instances of that class without having the class module definition reside in that same workbook. Any suggestions would be greatly apprecaited. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing classes defined in another workbook
Bing,
An alternative that I should have mentioned would have been to create a VB dll that does all of the work, and then the classes would be methods of that dll. Of course you would need a full VB version for this, but it is a bit simper than this method. -- HTH RP (remove nothere from the email address if mailing direct) "Bing" wrote in message ... Hi Bob, Very smart!!! It worked. Created the classes in an Add-in. Thanks for the response Regards, Bing C. "Bob Phillips" wrote: Bing, Not possible directly. What you can do is to create a public function in the workbook with the classes that accesses the class like so Public Function CreateClass1() As clsClass1 Set CreateClass1= New clsClass1 End Function and access that from your other workbook, like so Dim myClass as Object Set myClass = CreateClass1() You need to use a generic object type as the class object is not known in that workbook, and you will need to have set a reference to the class containing workbook in your other workbook (VBEToolsReferences), and it will need to have been saved -- HTH RP (remove nothere from the email address if mailing direct) "Bing" wrote in message ... Hi, This may be more of a deployment issue than a programmatic one. My question is: I have defined a number of useful classes that i would like to use in various excel workbooks (ie. VBA projects). How can i programmaticaly use these classes without having to define individual and identical class modules for each workbook (VBA project). Doing it this way makes maintenance a nightmare if i have a change in the class definition; that means i'll have to change all the workbooks where this class is defined as well It would be better if i could define the class and make it "global" so that all workboods could create instances of that class without having the class module definition reside in that same workbook. Any suggestions would be greatly apprecaited. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing classes defined in another workbook
Bob Phillips wrote:
Public Function CreateClass1() As clsClass1 Set CreateClass1= New clsClass1 End Function You need to use a generic object type as the class object is not known in that workbook If the class's Instancing property is changed to PublicNotCreatable then it is known and can be declared as the class type e.g. Dim myClass As VBAProject.clsClass1 where VBAProject is the name of the VBA project to which the reference has been set (optional but recommended). In fact, I think the class in your example must already be PublicNotCreatable, otherwise I think you would have to declare the return type of your CreateClass1 function as Object. Jamie. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing classes defined in another workbook
Thanks Jamie,
"onedaywhen" wrote in message oups.com... Bob Phillips wrote: If the class's Instancing property is changed to PublicNotCreatable then it is known and can be declared as the class type e.g. Dim myClass As VBAProject.clsClass1 I knew that I should have been able to, and was sure that I had, but I had forgotten how, so I went safe :-). The joy of the NGs. In fact, I think the class in your example must already be PublicNotCreatable, otherwise I think you would have to declare the return type of your CreateClass1 function as Object. I don't think so. That function is in the same workbook as the class, and so knows about the class implicitly. I juts tested again with the Instancing property set to Private and it works fine. BTW, why have you reverted to OneDayWhen? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing classes defined in another workbook
Bob Phillips wrote:
I juts tested again with the Instancing property set to Private and it works fine. OK, guess number 2: your function resides in a standard module <g. When testing, I made mine a member of ThisWorkbook and got a compile error when the return type was a private class (solved either by declaring the return type as Object or changing the instancing to PublicNotCreatable). BTW, why have you reverted to OneDayWhen? I'm posting via google groups as usual, but since it went 'beta' they're somehow picking up my old 'nickname'. I've updated my account but it's still appearing in posts. BTW why are you no longer looking over the Purbecks? Jamie. -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing classes defined in another workbook
"onedaywhen" wrote in message
ups.com... OK, guess number 2: your function resides in a standard module <g. When testing, I made mine a member of ThisWorkbook and got a compile error when the return type was a private class (solved either by declaring the return type as Object or changing the instancing to PublicNotCreatable). That must be it, mine was in a standard module. Remember that thought for later! I'm posting via google groups as usual, but since it went 'beta' they're somehow picking up my old 'nickname'. I've updated my account but it's still appearing in posts. Don't like the new beta. Couple of good things, but I find the general thread view less than it was, and I hate the highlighting of all selected words. I did write and tell them the first time they tried, but they didn't change :-) BTW why are you no longer looking over the Purbecks? It's winter, I can't see them! Seriously, I had to rebuild my laptop a few weekls/months ago, and I didn't add that moniker then. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing classes defined in another workbook
Thanks all for their very helpful response. I got it working based on all
input here! Thanks! One final question. I defined the class in an add-in and intend to distribute the a workbook project, and add-in to 3rd parties. I can programmatically load the add-in but is the reference to the add-in from the workbook project still valid because when the workbook project is opened by 3rd party, the reference was already defined before the add-in can be programmatically loaded? "Tushar Mehta" wrote: For something that puts together everything in this discussion, see an article I wrote some time ago: How to use a class (object) from outside of the VBA project in which it is declared http://support.microsoft.com/default...b;en-us;555159 -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi, This may be more of a deployment issue than a programmatic one. My question is: I have defined a number of useful classes that i would like to use in various excel workbooks (ie. VBA projects). How can i programmaticaly use these classes without having to define individual and identical class modules for each workbook (VBA project). Doing it this way makes maintenance a nightmare if i have a change in the class definition; that means i'll have to change all the workbooks where this class is defined as well It would be better if i could define the class and make it "global" so that all workboods could create instances of that class without having the class module definition reside in that same workbook. Any suggestions would be greatly apprecaited. Thanks |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing classes defined in another workbook
Bing,
You can add a reference like so, just change the project file name to suit Dim sPath sPath = Workbooks("Personal.xls").FullName Application.VBE.ActiveVBProject.References.AddFrom File Filename:=sPath I would suggest that you add this to the workbook open to ensure it gets done early. You will need to play with this though, my limited testing have not recognised the new class any better than before. -- HTH RP (remove nothere from the email address if mailing direct) "Bing" wrote in message ... Thanks all for their very helpful response. I got it working based on all input here! Thanks! One final question. I defined the class in an add-in and intend to distribute the a workbook project, and add-in to 3rd parties. I can programmatically load the add-in but is the reference to the add-in from the workbook project still valid because when the workbook project is opened by 3rd party, the reference was already defined before the add-in can be programmatically loaded? "Tushar Mehta" wrote: For something that puts together everything in this discussion, see an article I wrote some time ago: How to use a class (object) from outside of the VBA project in which it is declared http://support.microsoft.com/default...b;en-us;555159 -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi, This may be more of a deployment issue than a programmatic one. My question is: I have defined a number of useful classes that i would like to use in various excel workbooks (ie. VBA projects). How can i programmaticaly use these classes without having to define individual and identical class modules for each workbook (VBA project). Doing it this way makes maintenance a nightmare if i have a change in the class definition; that means i'll have to change all the workbooks where this class is defined as well It would be better if i could define the class and make it "global" so that all workboods could create instances of that class without having the class module definition reside in that same workbook. Any suggestions would be greatly apprecaited. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Accessing VBA subroutines in another workbook | Excel Programming | |||
Accessing workbook info from a different workbook macro | Excel Programming | |||
Accessing workbook info from a different workbook macro | Excel Programming | |||
calling from one workbook the classes and functions in another | Excel Programming | |||
Using classes defined in an add-in | Excel Programming |