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

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,

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]

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

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

Many thanks,

Mitul