The author of this tip is Tushar Mehta. You can reach him through his web site:
One of the benefits of using Object Oriented Programming (OOP) is the reusability of code. The typical way to do so is to instantiate an object of an already defined class with the Set variable = New ClassName syntax. VBA programmers use such objects whenever they use any object within their application platform. Examples include the Workbook object in Excel, the Presentation object in PowerPoint or the Document object in Word. In addition, VBA programmers use userforms, which are objects that belong to a special kind of class. All these objects are defined in libraries that are outside of the VBA developer's project. Click on the Tools | References… menu item to see the list of external libraries that the Visual Basic Editor (VBE) automatically establishes on behalf of the developer.
In this tip, we explore how to access a custom class in an external library created by us. While the example used below is based on Excel 2003, the tips applies to other MS Office products that support VBA and is applicable to versions 2000 or later.
This is an intermediate/advanced level tip and it presumes a certain comfort level with VB(A) programming.
There are two distinct set up steps required before one VBA project can access an object declared in the class module of another VBA project. The first set makes the class module usable outside of the project in which it is declared and provides a means by which an object can be instantiated. The second set deals with how the client project uses this class.
Set up the project that contains the class definition
By default a class module has the Instancing property set to Private. That means that only the project which contains the definition of the class can instantiate an object of that class. The only other choice that VBA supports is Public, not creatable. What that means is that an external project can use an object of this class, but it cannot instantiate it. Might seem strange, but that's the way it is. The way to change the property from the default value is to select the class module in the VBE Project Explorer, select the class module of interest, access the Properties Window (if necessary, use F4 to make it visible), and change the Instancing property to 2-PublicNotCreatable.
To follow along with the example in this tip, create a class module, name it clsEmployee, change its Instancing property, and add the following code to it.
Dim sName As String
Property Get Name() As String
Name = sName
Property Let Name(uName As String)
sName = uName
Next, since the Instancing property of the class is PublicNotCreatable, the project must provide a way for a client to instantiate the object. Add a new function in a standard module:
Public Function New_clsEmployee() As clsEmployee
Set New_clsEmployee = New clsEmployee
where clsEmployee is the name of the class of interest. Also, this should not be a private module.
One final change will make life a little easier. Rename the project from the default VBAProject to ClassProvider. To do so, select the project in the VBE Project Explorer, then select Tools | VBAProject Properties… | General tab | and in the Project Name field enter ClassProvider.
Save this file, say, as Class Provider.xls.
Next, move on to the client project.
Set up the project that will use the exported class
The client project uses the class very much as it would a class defined in any other external library (such as an userform) -- with one key difference. Since it cannot instantiate an object of that class, it must use the New_clsEmployee() function declared above. As with any other external library, decide whether to use early binding or late binding. The code below demonstrates both. Remember that to use the early binding code, the client project must include a reference (Tools | References…) to the Class Provider.xls file.
Dim anEmployee As ClassProvider.clsEmployee
Set anEmployee = ClassProvider.New_clsEmployee
anEmployee.Name = "Tushar Mehta"
Dim anEmployee As Object
Set anEmployee = Application.Run("'g:\temp\class provider.xls'!new_clsEmployee")
anEmployee.Name = "Tushar Mehta"
The external library doesn't have to be a normally saved file. It can be in a application-specific add-in (such as a file saved with the suffix .xla or .ppa). Just remember that such an add-in is opened not through the normal File | Open… method but loaded with the Tools | Add-Ins… command.
References: The only article found on this subject in the MSKB:
How To Make VBA Class Modules Available Out-of-Process
Article ID: 555159 - Last Review: Feb 14, 2017 - Revision: 1