Working with AddIns
Hi --
I would like to create a workbook with generic vba routines and classes available to all other workbooks and their vba code. To do this, I created a new AddIn (TestAddIn.xla) and a test workbook (Test.xls). In the AddIn, I created a module with a function getHello that returns the string "Hello".
If I put:
= getHello()
in a worksheet cell, it displays "Hello" as expected.
If I create a public sub in a Test.xls module with
debug.print getHello
and run the sub in the vba editor, nothing happens unless the project for the workbook has a reference to the TestAddIn. Is that correct? Are AddIns visible to vba code only when a reference is created? Does that mean that at the level of a workbook module, it is incorrect to talk of AddIn's and you can only talk of references?
If I create a new class in the AddIn (called AddInClass) with Instancing 1-Private, the class is not visible to the Test.xls vba code - eg the following in a sub fails:
Set AddIn = New Test_AddIn.AddInClass
where Test_AddIn is the project name of TestAddIn.xla. The following also fail:
Set AddIn = New TestAddIn.AddInClass 'using root of file name
Set AddIn = New AddInClass
All 3 get the error message:
Compile Error: User defined type not defined
If I change the Instancing to 2-PublicNotCreateable and try:
Set AddIn = New Test_AddIn.AddInClass
Set AddIn = New AddInClass
they both fail with the message:
Compile Error: Invalid use of New Keyword
Thus I can use the class, I just can't create it.
If I create an AddIn function to return a new instance of AddInClass:
Public Function getNewAddInClass() As AddInClass
Set getNewAddInClass = New AddInClass
End Function
then the xls vba code can get a new instance of the AddIn class and use it just fine.
Is there any way to have a workbook module create a new instance of an AddIn class directly without the AddIn needing a function to return the new Instance?
Thanks,
Barry
|
