How to use a class (object) from outside of the VBA project in which it is declared

Author:

Tushar Mehta MVP

COMMUNITY SOLUTIONS CONTENT DISCLAIMER

MICROSOFT CORPORATION AND/OR ITS RESPECTIVE SUPPLIERS MAKE NO REPRESENTATIONS ABOUT THE SUITABILITY, RELIABILITY, OR ACCURACY OF THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN. ALL SUCH INFORMATION AND RELATED GRAPHICS ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT AND/OR ITS RESPECTIVE SUPPLIERS HEREBY DISCLAIM ALL WARRANTIES AND CONDITIONS WITH REGARD TO THIS INFORMATION AND RELATED GRAPHICS, INCLUDING ALL IMPLIED WARRANTIES AND CONDITIONS OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, WORKMANLIKE EFFORT, TITLE AND NON-INFRINGEMENT. YOU SPECIFICALLY AGREE THAT IN NO EVENT SHALL MICROSOFT AND/OR ITS SUPPLIERS BE LIABLE FOR ANY DIRECT, INDIRECT, PUNITIVE, INCIDENTAL, SPECIAL, CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF USE, DATA OR PROFITS, ARISING OUT OF OR IN ANY WAY CONNECTED WITH THE USE OF OR INABILITY TO USE THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN, WHETHER BASED ON CONTRACT, TORT, NEGLIGENCE, STRICT LIABILITY OR OTHERWISE, EVEN IF MICROSOFT OR ANY OF ITS SUPPLIERS HAS BEEN ADVISED OF THE POSSIBILITY OF DAMAGES.

Tips

Author
The author of this tip is Tushar Mehta.  You can reach him through his web site:
 
Introduction
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.
 
Option Explicit
Dim sName As String
Property Get Name() As String
    Name = sName
    End Property
Property Let Name(uName As String)
    sName = uName
    End Property
 
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:
 
Option Explicit
Public Function New_clsEmployee() As clsEmployee
    Set New_clsEmployee = New clsEmployee
    End Function

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.
 
Option Explicit
Sub UseExportedClass_EarlyBinding()
    Dim anEmployee As ClassProvider.clsEmployee
    Set anEmployee = ClassProvider.New_clsEmployee
    anEmployee.Name = "Tushar Mehta"
    MsgBox anEmployee.Name
    End Sub
Sub UseExportedClass_LateBinding()
    Dim anEmployee As Object
    Set anEmployee = Application.Run("'g:\temp\class provider.xls'!new_clsEmployee")
    anEmployee.Name = "Tushar Mehta"
    MsgBox anEmployee.Name
    End Sub
 
Final Note
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

Properties

Article ID: 555159 - Last Review: 14 Feb 2017 - Revision: 1

Microsoft Visual Basic for Applications 6.0

Feedback