Referencing other workbooks and using thier classes

[maagen]

Hi,

I have created a class module, and it works fine when I call from the VBA project in which I have written the class module. However, I would like to access my class, from other workbooks as well, so I have tried to reference the workbook through Tools -> References. Now I have access to functions in the referenced workbook, and after I changed the Instancing in the properties window of the class module to "2 - PublicNotCreatable" it seems I have access to my class DataAccessZor.

But when I try to instantiate the class like this (In the new workbook):

dim MyDataAccessZor As DataAccessZor
Set MyDataAccessZor = New DataAccessZor


I get the error message "Invalid used of New keyword".


Why is this? I can see that it might have something to do with me setting the instancing of the class module to "2 - PublicNotCreatable", but thw other option is "1 - Private" in which case I can't access the referenced workbooks class.

Thanks in advance!!

Mads

 

[Colin Legg]

Hi,

The 2 - PublicNotCreatable settings means that you can declare a variable of that class type from another project, but you cannot create an instance of that class from another project.

You'll need to add a function to the workbook containing the class; that function will create the class instance and would be called by your other workbook.

Example on Chip Pearson's site:
http://www.cpearson.com/Excel/Classes.aspx


Hope that helps...

 

[maagen]

It did, thanks a lot!

Seems strange to me why VBA doesn't have the option to make instancing public.

 

[RoryA]

You can actually do it by setting the VBComponent's Instancing property to 5 in code, but it's not supported as far as I know and I have no idea how stable it would be!