How one VBA project can use an object declared in the class

AuthorThe author of this tip is Tushar Mehta. You can reach him through his web site: www.tushar-mehta.comIntroductionOne 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, VBAprogrammers use userforms, whichare 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 withhow the client project uses this class.Set up the project that contains the class definitionBy 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 onlyother 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 theProperties 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 ExplicitDim sName As StringProperty Get Name() As String Name = sName End PropertyProperty Let Name(uName As String) sName = uName End PropertyNext, 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 ExplicitPublic Function New_clsEmployee() As clsEmployee Set New_clsEmployee = New clsEmployee End Functionwhere 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 tothe client project.Set up the project that will use the exported classThe 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 ExplicitSub UseExportedClass_EarlyBinding() Dim anEmployee As ClassProvider.clsEmployee Set anEmployee = ClassProvider.New_clsEmployee anEmployee.Name = “Tushar Mehta” MsgBox anEmployee.Name End SubSub 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 SubFinal NoteThe external library doesn’t have to be a normally savedfile. 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-Processhttp://support.microsoft.com/default.aspx?scid=kb;en-us;244075等待故人的归来。山上的树，大多数是松树比较突出。