How was that video? Pretty super-awesome, huh?
You can download VBHelpers.zip
It’s rough, to say the least, but if you want to mess around with it, have at it. Here’s some more information on what was happening in that video:
0:00 First I insert a class module. Inserting modules is one of those activities that bugs me. I do it enough that the extra few steps get on my nerves. Normally, I Alt+I+M/C/U. Then I F4 to open the properties box and change the name. The utility that I use now does a few things. It prompts for a module name. Based on the first letter of the module name, it creates the appropriate module.
M = Standard Module and adds a private constant to the module called msMODULE with the module’s name. This is for the error handling stuff that I copied out of Professional Excel Development
C = Class Module and adds the line
Public ModNameID As Long The CreateParent code relies on the presence of this property.
U = Userform and adds nothing.
0:15 I add some more public variables to the class
0:34 The Convert Public to Property finds all the public variables in the class and converts them to private variables and Property Get and Let/Set statements. This is hardcoded to my personal preferences. Namely, all module level variables start with “m” and another prefix indicating the data type. If you don’t like those preferences, you won’t like this add-in.
0:40 Create Parent Class make a class module whose name is the plural of whatever class module is active. If you’re in CPerson, it create CPeople. CCar spawns CCars. And so on. It creates a text file in your My Documents folder and puts all the ATTRIBUTE goodness so you can use For Each and you can avoid using Item. It uses a collection to store the child instances of the class. People seem to prefer to dictionaries, but I think the extra reference dependency makes it not worth it. It generates an Add method, a property that returns an Item (named after the child class) and a Count property. Then it imports that text file into your project. Already have a class with that name? You’ll probably get an error and the error handling is pretty weak right now.
0:53 Create FindBy There are a couple of things that I find myself typing over and over. One of those is a FindBy property in the parent class. This utility sets it up for you. It prompts you for a space delimited list of colon delimited strings (got that?). In the video, I want a property that returns a CEmployee instance given the EmployeeName property. I type EmployeeName:String to create the code. I could also have created a FindBy property for two or more properties. If I had typed
EmployeeName:String HireDate:Date, it would have generated a FindBy that looks like this:
Public Property Get FindByEmployeeNameAndHireDate(sEmployeeName As String, dtHireDate As Date) As CEmployee
Dim clsReturn As CEmployee
Dim clsEmployee As CEmployee
For Each clsEmployee In Me
If clsEmployee.EmployeeName = sEmployeeName And clsEmployee.HireDate = dtHireDate Then
Set clsReturn = clsEmployee
Set FindByEmployeeNameAndHireDate = clsReturn
And that’s it for class creation. Parent, child, and FindBy in about one minute. The rest of the video is writing a procedure to show that it compiles and works.
The other code that I’m constantly writing but don’t want to is FillFromRange code. If I have a list of employees in a spreadsheet, I want to generate the code that creates all the CEmployee instances and adds them to the parent class. Coming soon I hope. Enjoy and let me know how it goes.