Hasn’t it been a great class module week? It all started with Rob’s post about custom collection classes, or what I call parent classes. If I make a Person class, I want a People class. If I make an Invoice class, I want an Invoices class to hold all of them.

Back in 2008, I started working on a VBA Framework utility. From that came some code to create a parent class automatically. I could select a class, and it would create a new class module in the same project and put in all the code necessary. It was sweet. After reading Rob’s post, I realized that I never use default properties or For Each constructs with my custom classes, because I’m too darn lazy to export to a text file, type in the appropriate Attribute lines, and reimport. No, instead I’m happy using For Next and calling out each property explicitly.

Then it hit me. If I don’t do something because it’s tedious, why don’t I automate the tedium? I am a programmer, after all. It didn’t take long to realize that I didn’t need to automate the import/export process. Instead, I need to create the parent class as a text file with the Attributes already in there, and just import. And as long as I’m automating tedium, I need to automatically add Rob Bruce’s code to the child class.

I completely refactored my parent class generator code. And here it is:

Here’s what it does: It creates a text file with a .cls extension and puts in all the code that I want in my parent class. To get all those strings out of my main procedure, I created CParent. Most of the properties of CParent just kick out strings, but it makes it more modular and cleaner looking. There is some string manipulation in there, so it was better than using a bunch of constants. Once the text file is filled, I import it into the project.

Next, I add some code at the end of the declarations section of the child class. This string wasn’t too unwieldy, so I just left in in my main procedure. The next bit of code, the Parent Get and Set properties, was a bit long, so I stuck in the CParent class. Both are inserted into the child via the InsertLines method.

Here are a few examples of properties from CParent

You might notice that I don’t have a Remove method in my parent class. Remove is tricky, I think. It seems to be different for every project and every class. Often, I’ll use a soft delete method, wherein I will set a deleted flag rather than actually removing it from the collection. Then when I store the data (to a text file or an Access database or whatever), I don’t write the records with the deleted flag set. It can get hairy, so I don’t generate that automatically and just hand code it every time.

Finally, I test it all out in another workbook. I create a CPerson class with some made up properties. Then automatically create the CPeople class. Then run this code

I didn’t show a lot of the code that’s in the file because it would make you all more sleepy. But…

You can download VBHelpers.zip