How to Use a Class Module from an Excel Addin in VBA

[mapkn]

Hi all,

I'm a bit new to this so hoping someone can help!

I have an Excel Addin where I have defined a class module. In another VBA project I wish to create an object instance of the class module defined in the addin in a (new) separate module.

Does this make sense and can anyone tell me what code I should use in the new module to do this please?

Thanks,

[Bob Phillips]

You need some helper functions in the project that contains the class module to call into, which in turn access the class.

Here is an example.

This is a simple class in Book1 say, which I called clsAddin

[vba]

Option Explicit

Public Name As String

Public Function GetName()
GetName = Name
End Function
[/vba]

Here are some helper functions, also in Book1, that can access the class

[vba]
Option Explicit

Private myObject As clsAddin

Public Sub SetClassHelper()
Set myObject = New clsAddin
End Sub

Public Sub SetNameHelper(Name As String)
myObject.Name = Name
End Sub

Public Function GetNameHelper()
GetNameHelper = myObject.GetName
End Function

Public Sub UnsetClassHelper()
Set myObject = Nothing
End Sub
[/vba]

and here is an example of how to use it, from within another workbook

[vba]

Public Sub Test()

Application.Run "Book1!SetClassHelper"
Application.Run "Book1!SetNameHelper", "Bob"
MsgBox Application.Run("Book1!GetNameHelper")
Application.Run "Book1!UnsetClassHelper"

End Sub
[/vba]

[rory]

You can also have a function in a normal module of your add-in that returns an instance of the class for you to work with. If you set the class' Instancing to PublicNonCreatable and set a reference to the add-in, you can then declare a variable as having your class' interface:
[VBA]Dim objClass As AddinProject.ClassName[/VBA]

HTH

[mapkn]

Thanks xld and Rory for your replies. Both solutions work fine...although the second seems easier.

Many thanks,

Mitul