Expose class in Excel XLA add-in

Question

I have written an XLA add-in for Excel. This add in includes a class module. I was wondering how I can expose this class module for use. I have searched around and found a way to do it with Visual Studio, but sadly I do not have visual studio. Is there any way to do this just from XLA?

Answer 1

You can make a public function in the XLA that returns an instance of the class. Then any workbook that has a reference to the XLA can call that function. Assume you have Book2 which contains Class1 and Book3 where you want to use Class1. Change the Class1 Instancing property to Public Not Creatable (F4 while in the class module).

In Book2 in a standard module create a function

Public Function InstantiateClass1() As Class1

    Set InstantiateClass1 = New Class1

End Function

In Book3, call the function

Public Sub DoStuff()

    Dim clsClass1 As Book2Project.Class1

    Set clsClass1 = instantiateclass1

    clsClass1.prop = "something"

    Debug.Print clsClass1.prop

End Sub

Class1 has one property called prop in this example. Book2's VBProject name was changed to Book2Project.