Browse All Articles > Parent Class Builder Add-In for Microsoft Excel
Introduction
Visual Basic for Applications (VBA) developers of intermediate or higher skill level may often find it useful to implement custom classes in their applications. For some tasks, a single class instance may be all that is needed. However, there may often be a need to create and persist multiple instances of a class, and to manage these class instances through a "parent" or "collection class".
While the particular application for a parent class may vary according to the task at hand, the core functionality required for any parent class is fairly stable, and thus developers who frequently use parent classes may find it useful to automate part of the set-up work, thus leaving more time and attention for the more subtle aspects of the project.
This article introduces and describes a new add-in built for this purpose: the Parent Class Builder Add-In for Excel 2000 and higher.
Note: While the Parent Class Builder Add-In is an Excel component, you can still use it to create class modules in other Office applications. The technique for doing this is explained in the section Using the Parent Class Builder Add-In.
About Custom Classes and Parent Classes in VBA
This article is not intended as a general introduction to custom classes in VBA; that is a very rich subject deserving an article (or series of articles!) in its own right. For any readers who may need a refresher on class modules, you may want to start with this introduction by Chip Pearson. Rather, this article describes a very specific tool used as an aid and time-saver for developers who want to create their own parent classes.
In simple terms, what I refer to as a "custom class" is a user-defined object type. A custom class is defined by a class module in the VBA project, and the code in that class module defines the class’s properties and methods.
In a recent EE question, the Asker needed to find the information associated with the most recent update of a contract:
I have an Excel file that has 10K contract records. Each contract will have a name, a contract version, contract effective date, contract expiration date. For the purpose of reporting I only want the last version of any contract. How do I find the last version of a contract record, if there are multiple contract versions?
In this case, we can use a custom class, "clsContract", to model an individual contract as an object with three properties: version, effective date, and expiry date. Further, to gather up and manage the individual contracts, it is useful to create a "parent", or "collection class". Continuing with the example, using this add-in I created a parent class, "clsContracts", to form a collection of the individual contracts.
That is a relatively simple object model; complex data sets may require several nested levels. For example, in a past project, to model weekly work requirements, by activity, week, and branch, I need something like this:
Note how in the second example even the “child” nodes spun off additional parent/child pairs. For example, the clsBranch object has a property, Weeks, that is itself a clsWeeks collection class.
Note also that in both examples, the properties and methods of the parent classes are very similar. Indeed, it is intrinsic to the nature of parent classes that they will have the following properties and methods:
Add;
Clear;
Count;
Item; and
Remove.
Indeed, it is that degree of commonality that makes the process of creating parent and child classes susceptible to automation.
Installing the Parent Class Builder Add-In
To install the Parent Class Builder Add-In, please download the file below:
Parent-Class-Builder.xla
It does not matter where you save the file to. It is slightly more convenient if you save the file to Excel’s default add-in directory; this can vary a bit depending on your Office and Windows versions, but it will the same directory with the standard add-ins SUMIF.XLAM, EUROTOOL.XLAM, and LOOKUP.XLAM (in Excel 2003 and earlier, those extensions will be *.XLA).
Once you have downloaded the file, launch Excel, and enable the add-in:
In Excel 2003 and earlier, select Tools / Add-Ins from the menu. In the Add-Ins form, check the box for Parent Class Builder. If you do not see Parent Class Builder in the listed add-ins, then click the Browse button, and navigate to where you saved the file;
In Excel 2007, click the Office button, and then Excel Options. Click Add-Ins, and then in the Manage drop-down select Excel Add-Ins, then click the Go button. Then follow the directions above for Excel 2003 and earlier;
In Excel 2010, click the File menu and then Excel Options, and then follow the directions above for Excel 2007.
Note: If you wish to use the Parent Class Builder Add-In to actually create the class modules for you, then you must enable trusted access to the Visual Basic Project. Without this trusted access, the Parent Class Builder Add-In can still create *.cls files that you can import to create classes, but it cannot actually create the classes for you.
Using the Parent Class Builder Add-In
When the Parent Class Builder Add-In is enabled, you will see a new menu item for it:
In Excel 2003 and earlier, there will be a new item in the Worksheet Menu Bar, "Parent Class Builder", with one item below it, "Create Parent Class"
In Excel 2007 and later, in the Ribbon’s Add-Ins tab there will be a new item, "Parent Class Builder", with one item below it, "Create Parent Class"
To create your classes, use the following work steps:
Select Create Parent Class from the menu. Excel will then show you the following form:
Enter a name for the parent class (1)
Enter a name for the child class (2)
Indicate the child class property that you want to use as the key in the parent collection (3)
Use the checkbox to indicate whether you also want to create a child class (4)
If you want to create a child class, then indicate whether that child class will itself spawn another collection class (5)
If for (5) above you are including a "next level", then indicate the name to use for that "next level" class (6)
If for (5) above you are including a "next level", then indicate the property name you will use in the child class to refer to that "next level" class (7)
If you want to import the new classes into a workbook, check the appropriate checkbox, and use the drop down list to select the workbook to add the class(es) to; the list includes an option to import the classes into a new workbook (8)
If you wish to write *.cls files to a directory either instead of or in addition to importing the classes into a workbook, check the appropriate checkbox, and indicate which directory to save the *.cls file(s) to (9)
Indicate whether the class(es) should have case sensitive or case insensitive text comparisons by default. These defaults will use Option Compare Binary|Text, and as such can be overridden on a statement-by-statement level (10)
Indicate whether to include a comment for the module creation date (11)
If desired, populate a "byline" comment for the module (12)
Note: The Parent Class Builder Add-In cannot directly add classes to the VBA projects for other Office applications, such as Word, Access, or PowerPoint. However, if you need to create classes for these applications, you can run the Parent Class Builder Add-In to create the necessary *.cls files, and then import those *.cls files into your application’s VBA project.
In the VBA IDE, to import a *.cls file, you can use Ctrl+M, select File / Import File from the menu, or right click the project you want to import the file into in the Project Explorer.
In the VBA IDE, to import a *.cls file, you can use Ctrl+M, select File / Import File from the menu, or right click the project you want to import the file into in the Project Explorer.
You can use the Parent Class Builder Add-In in an iterative fashion to build up complicated object models. For example, to create the "Contracts" object model described in the section About Custom Classes and Parent Classes in VBA:
I used a single run of the add-in, with "clsContracts" as the parent class, "clsContract" as the child class, "Name" as the child key property, and no "next level".
After running the add-in, I fleshed out the object model by adding Import and Export methods to clsContracts, and EffectiveDate, ExpiryDate, and Version properties to clsContract.
Owing to its more complex structure, creating my "Workload" object model would require three steps:
First, run the add-in, with "clsBranches" as the parent class, "clsBranch" as the child class, and "BranchCode" as the child key property. Include a "next level", with clsWeeks as the next level class and Weeks as the next level property
Next, run the add-in again, with "clsWeeks" as the parent class, "clsWeek" as the child class, and "StartDate" as the child key property. Include a "next level", with clsActGroups as the next level class and ActGroups as the next level property
Last, run the add-in again, with "clsActGroups" as the parent class, "clsActGroup" as the child class, and "Name" as the child key property. For this last step, do not include a "next level"
As above, I would then have to flesh out the object model with any additional methods and/or properties my project required
How the Parent Class Build Add-In Does Its Work
The basic logical flow for the Parent Class Build Add-In is as follows:
Note that the Parent Class Builder Add-In always creates *.cls files. For example, if the user elects to have the *.cls files saved to a specified directory, then the files are saved there. However, if this option is not selected, the Parent Class Builder Add-In creates "temporary" *.cls files that are then deleted once they are imported.
The add-in does this because it is the only way to enable a default property for the collection class (e.g., the Item property), and it is the only way to enable For Each…Next enumerations of the parent class. Enabling both of these require using attribute modifiers, which is not supported in the VBA IDE. However, if you import *.cls files with the appropriate attribute modifiers into a VBA project, then VBA will honor the settings.
Internally, the add-in uses worksheets to store three code templates:
Parent Class
Child Class with Next Level
Child Class No Next Level
Each of these templates use tokens as placeholders for various user defined code elements. The add-in uses the following tokens:
Token Usage
-------------------------------------------------------------
<<clsLevel1>> Parent class name
<<clsLevel2>> Child class name
<<clsLevel3>> "Next level" class name
<<Name>> Child property used as key in collection
<<Level3>> Child property used to refer to "next level"
<<Byline>> Comment with customizable byline
<<CreatedDate>> Comment containing current date
<<Text>> Used to set Option Compare Text|BinaryIf desired, you can modify the code templates. To get to them, please use the following work steps:
If it is not already, open the Parent Class Builder Add-In, and go to the VBA IDE
In the Project Explorer, find the project ParentClassBuilder, and expand its node for Microsoft Excel Objects
Click ThisWorkbook, and in the Properties window set the IsAddin property to False
Switch back to Excel, and the worksheets for the Parent Class Builder Add-In should be visible
After making any desired modifications, be sure to go back to the VBA IDE, set IsAddin back to true, and save the file
Finishing the Code Generated by the Parent Class Build Add-In
The Parent Class Build Add-In may be useful to you in setting up the structure of your parent/child class modules, but it cannot do all of your work for you. The add-in will create the elements that are common to almost all such parent/child classes, but you will still have to add the elements required for your specific application.
For example, the add-in will create a parent class that looks similar to this result:
' Patrick Matthews
' Created 2012-10-28
' Parent collection class of clsChild
Option Explicit
Option Compare Text
' Container for all clsChild objects in the parent collection class
Private coll As Collection
Private Sub Class_Initialize()
Set coll = New Collection
End Sub
Private Sub Class_Terminate()
Set coll = Nothing
End Sub
Public Function Add(Name As String) As clsChild
' Adds a new item to the collection. Causes an error if an item with the same key already exists
' or if you pass a zero length string for the Name argument
If Name = "" Then
Err.Raise vbObjectError + 1002, , "Name property of clsChild object cannot be zero length string"
End If
Set Add = New clsChild
Add.Name = Name
' Raise an error and set return value to Nothing if we fail to add item to collection (most likely
' because an item already exists with the same key
On Error GoTo ErrHandler
coll.Add Add, Name
Exit Function
ErrHandler:
Set Add = Nothing
Err.Raise vbObjectError + 1003, , "Could not add item '" & Name & "' to clsParent collection"
End Function
Public Sub Clear()
' Recreates (and thus clears) collection
Set coll = New Collection
End Sub
Property Get Count() As Long
' Returns number of items in the collection
' Read-only
Count = coll.Count
End Property
Function Exists(Name As String) As Boolean
' Returns True if a clsChild member specified by the Name exists in clsParent parent collection
Dim TempItem As clsChild
On Error GoTo CleanUp
' Default return is False
Exists = False
' If item exists, then the Set operation completes without error
Set TempItem = coll(Name)
Exists = True
CleanUp:
Set TempItem = Nothing
End Function
Property Get Item(Index As Variant) As clsChild
' Default property. Returns an item from the collection. Index may be either ordinal position (Long) or Name (String)
' Read-only
On Error GoTo ErrHandler
Set Item = coll(Index)
Exit Property
ErrHandler:
Set Item = Nothing
Err.Raise vbObjectError + 1004, , "Item does not exist in clsParent collection"
End Property
Function Keys() As Variant
' Returns a 1-based array of the various strings used as Name key values for the clsChild items
' in the clsParent collection"
Dim Counter As Long
Dim Results() As String
' If there are no items in the clsParent collection then raise an error
If Me.Count > 0 Then
' Redimension array so there is one member per clsChild item in clsParent collection
ReDim Results(1 To Me.Count) As String
' Loop through clsParent collection and grab Name values for each clsChild item
For Counter = 1 To Me.Count
Results(Counter) = Me(Counter).Name
Next
' Set return value
Keys = Results
Else
' Raise error for no items
Err.Raise vbObjectError + 1005, , "Keys method failed: no clsChild items exist in clsParent collection"
End If
End Function
Public Sub Remove(Index As Variant)
' Removes an item from the collection. Index may be either ordinal position (Long) or Name (String)
coll.Remove Index
End Sub
Function NewEnum() As IUnknown
' Enables enumeration of the clsParent parent collection, i.e.:
'
' For Each Child In Parent...Next
Set NewEnum = coll.[_NewEnum]
End FunctionWhile the new parent class will have such familiar properties and methods as Add, Count, Exists, Item, etc., at a minimum you will have to add procedures for populating your parent collection, and performing whatever analysis and/or manipulations your application requires.
In addition, if you indicated that you also want to create a child class, then you will end up with a class module similar to this:
' Patrick Matthews
' Created 2012-10-28
Option Explicit
Option Compare Binary
' Container for read-only property
Private Safe_NextLevel As clsNextLevel
' Container for "write-once read-many" property
Private Safe_Name As String
Private Sub Class_Initialize()
Set Safe_NextLevel = New clsNextLevel
End Sub
Private Sub Class_Terminate()
Set Safe_NextLevel = Nothing
End Sub
Property Get NextLevel() As clsNextLevel
' Returns reference to item's clsNextLevel collection
' Read-only
Set NextLevel = Safe_NextLevel
End Property
Property Get Name() As String
' Returns item's Name value
Name = Safe_Name
End Property
Property Let Name(NameString As String)
' Sets Name value for item
' This makes the Name property "write once, read many". If the Name is a zero length string,
' the Property Let allows you to change it; if not, the procedure raises a user defined
' error. Basically, we cannot allow changes because we want this property to match the
' item's true key used when it was added to the parent clsParent collection
If Safe_Name = "" Then
Safe_Name = NameString
Else
Err.Raise vbObjectError + 1001, , "Cannot change Name property of clsChild object"
End If
End PropertyThe child class will look like this if you did not want to include a reference to a “next level”:
' Patrick Matthews
' Created 2012-10-28
Option Explicit
Option Compare Text
' Container for "write-once read-many" property
Private Safe_Name As String
Property Get Name() As String
' Returns item's Name value
Name = Safe_Name
End Property
Property Let Name(NameString As String)
' Sets Name value for item
' This makes the Name property "write once, read many". If the Name is a zero length string,
' the Property Let allows you to change it; if not, the procedure raises a user defined
' error. Basically, we cannot allow changes because we want this property to match the
' item's true key used when it was added to the parent clsParent collection
If Safe_Name = "" Then
Safe_Name = NameString
Else
Err.Raise vbObjectError + 1001, , "Cannot change Name property of clsChild object"
End If
End PropertyAs above with the parent class, you will almost certainly have to add other properties and/or methods to your child class to support your application’s needs.
Please note the following characteristics of the class modules the add-in creates, which reflect my own preferences in using classes. If your preferences vary, please feel free to modify my templates to better reflect your own coding practices.
The parent class’s Add method returns an object of type clsChild. Thus, creating a new child instance and setting it to a variable can be done in a single statement:
Set MyObjectVariable = ParentClass.Add("Unique Key Value")
Set MyObjectVariable = ParentClass.Add("Unique Key Value")
The parent class’s Add method takes at least one argument, which is used as the key value when adding the child instance to the collection. Passing a zero-length string or a duplicate value raises an error. If desired, you can embellish the Add method to include other properties for the newly created child class instance
The Item property is set as the default property of the parent class. Thus, the following statements are equivalent:
Set MyObjectVariable = ParentClass.Item(42) Set MyObjectVariable = ParentClass(42)
Set MyObjectVariable = ParentClass.Item(42) Set MyObjectVariable = ParentClass(42)
If you pass the Item property an index that does not correspond to an item in the collection, the Item property will raise an error
The parent class includes an Exists method, which you can use to see whether the class collection contains a member with the indicated index value. The method returns Boolean True if such a member exists, and False if not
The parent class supports enumeration using For Each…Next. Thus the following are equivalent:
For Each Child In clsParent Debug.Print Child.Name Next For Counter = 1 To clsParent.Count Debug.Print Parent(Counter).Name Next
For Each Child In clsParent Debug.Print Child.Name Next For Counter = 1 To clsParent.Count Debug.Print Parent(Counter).Name Next
The child class’s key property is set up as "write once, read many": the Property Let procedure for the key property raises an error if the key property has already been set. I do this to ensure that the child key property always matches up to the key value used when the child instance is added to the parent collection
Further Reading and Acknowledgments
I owe a large debt of gratitude to EE Member and fellow Microsoft Excel MVP zorvek for introducing me to the world of custom classes in VBA. A few years back I had a project that I knew would be much easier if I used classes, but I had no idea where to start. zorvek showed tremendous patience in explaining some key concepts to me. I still learned some of my lessons the hard way, but his help made the learning curve much shorter and much less painful to traverse.
One of the very best Excel blogs out there is Daily Dose of Excel, by Excel MVP Dick Kusleika. In a recent series that started with this entry on collection classes and continues on for several more posts, Dick and co-contributor Rob van Gelder write some terrific stuff regarding collection classes. Indeed, it was Dick’s statement that a lot of the donkey work associated with setting up parent and child classes could be automated that inspired me to create this add-in.
Yet another Excel MVP, Chip Pearson, whose web site is a treasure trove of Excel information, also covers collection classes in this article.
Lastly, in creating my own collection class template, I was substantially influenced by Francesco Ballena’s collection class module template.
Some of the functionality in this add-in can be obtained through the free, and indispensable, MZ-Tools add-in for the various flavors of VB, VBA, and VB.Net. For example, users can create code templates that correspond to the parent and child templates I used for the Parent Class Builder Add-In. However, if you wish to set the Item property as the default property of the parent class, you would still have to export the code module to a *.cls file, edit the file, and re-import. The Parent Class Builder Add-In automates that process for you.
=-=-=-=-=-=-=-=-=-=-=-=-=-
If you liked this article and want to see more from this author, please click here.
If you found this article helpful, please click the Yes button near the:
Was this article helpful?
label that is just below and to the right of this text. Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (26)
Commented:
Do the class description graphics need to reflect the new Exists method?
Author
Commented:Commented:
As long as the graphics are meaningful and in-sync with the original question, then there shouldn't be any need to change them.
Commented:
Author
Commented:Technically, that "Workload Object Model" was from an Access project. Work for hire, so I can't post the original here (it would be a royal pain to obfuscate it enough to post).
But my approach would have worked :)
View More