Can't use classes from other workbooks

Posted by Christine H on May 26, 2001 2:27 PM

Hi,

I set up a workbook which includes a few class modules. One in particular carries out some complex calculations with data taken from an SQL Server database and outputs them to Excel worksheets. Now, I've attempted to set up another workbook which will utilise the class from the other workbook but I can't get it to work. I've set a reference to the workbook and used code like this: -

Dim ProductPrice as New DailyProfitability.DataAnalysis

However, when I try to use this VBA produces a run-time error with "Invalid Use of New keyword". It seems as though classes in one workbook can't be used in another workbook as the two instancing options are Private or Public Not Creatable.

I hope this makes sense. Can anyone help with this problem? I'd rather not copy the class module as it is changed frequently and this will mean having to update several workbooks each time a change is made.

Thanks for any help,
Christine.

PS Excel 2000, Windows NT 4.

Posted by Ivan Moala on May 26, 2001 5:57 PM

You may be missing one of the Reference libraries.
Just a quess but try referencing the
MicrosoftSQL parser Object Library 1.0 = SQLPARSE.DLL

ie. while in the VBE click on Tools / Reference
and search for this Dll.

HTH

Ivan

Posted by Dave Hawley on May 26, 2001 11:26 PM

Hi Christine

You should be able to reference the Class by referencing it via Tools>Reference. The reference should (I think) show in the "Project explorer" as a special entry.

Dave

OzGrid Business Applications

Posted by Christine H on May 27, 2001 1:20 AM

Ivan,
Thanks for your help but the problem isn't with any database drivers, it's with my own class. I just need to be able to use a class I've created in another workbook in a different workbook. I've tried the usual method of referencing the workbook and then trying to create an instance of that class but it's not working.

Regards,
Christine.

Posted by Christine H on May 27, 2001 1:24 AM

Dave,
Thanks for your input, but as I said in the original question I've already done this and yes, it does appear in a separate references category in the project explorer. The problem seems to be with actually creating an instance of the class in the same way you can with an ActiveX server, e.g. set Wd=New Word.Application.

Again, thanks for your input.

Christine.

Posted by Dax on May 27, 2001 2:22 AM

Christine,
You can't create new objects using class definitions from another workbook directly because as you say, the instancing property of classes in Excel 2000 is either private or non creatable. There is however, a way of getting around this...

First, set a reference to the other workbook. In the workbook that contains your class modules include a function like this in a standard module:-

Public Function ReturnClass() as ClassName
Set ReturnClass=New ClassName
End Function

Now from within the other workbook you can use something like this: -

Dim MyObject as ClassName

Sub SetUp()
Set MyObject=WorkbookName.ModuleName.ReturnClass
MyObject.DoThings
End Sub

You'll now be able to use MyObject properties and methods from within the workbook that doesn't contain the class.

HTH,
Dax.

Posted by Dave Hawley on May 27, 2001 2:45 AM

Hi again

Christine, have you tried using "Set" instead of Dim ?

...Or perhaps you need to pass refrence to your Class via a Function? EG;

Function PassMyClass() As DailyProfitability.DataAnalysis
Set PassMyClass = New DailyProfitability.DataAnalysis
End Function

Dave

OzGrid Business Applications

Posted by Dave Hawley on May 27, 2001 2:47 AM

Sorry Dax, didn't see your response!

OzGrid Business Applications

Posted by Christine H on May 27, 2001 6:31 AM

Thanks all

Thanks for your suggestions. The method of using a function works perfectly.

Christine.