PDA

View Full Version : How to Use a Class Module from an Excel Addin in VBA



mapkn
04-21-2008, 12:43 PM
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
04-21-2008, 02:03 PM
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



Option Explicit

Public Name As String

Public Function GetName()
GetName = Name
End Function


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


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


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



Public Sub Test()

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

End Sub

rory
04-22-2008, 05:07 AM
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:
Dim objClass As AddinProject.ClassName

HTH

mapkn
04-22-2008, 03:11 PM
Thanks xld and Rory for your replies. Both solutions work fine...although the second seems easier.

Many thanks,

Mitul