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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.