Accessing classes defined in another workbook

[Guest]

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

 

[Bob Phillips]

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 (VBE>Tools>References), and it
will need to have been saved

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Bob,

Very smart!!! It worked. Created the classes in an Add-in. Thanks for the
response

Regards,
Bing C.

 

[Bob Phillips]

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)

 

[onedaywhen]

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.

--

 

[Bob Phillips]

Thanks Jamie,

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?

 

[onedaywhen]

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.

--

 

[Bob Phillips]

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.

 

[Tushar Mehta]

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.aspx?scid=kb;en-us;555159

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

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?

 

[Bob Phillips]

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.AddFromFile 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)