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
Exit For
End If
Next clsEmployee
Set FindByEmployeeNameAndHireDate = clsReturn
End Property
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.
That zip file appears to be empty.
Yes, nothing in the zip file.
I prefer dictionary too. But if your giving us the code and everything then I’m sure it would be easy enough to change around.
Professional Excel Development – Excellent book, I stopped programming in Excel until I could poor over all of it there was so much good stuff in there.
Thanks for giving this away.
Thanks for making that available.
Dick: I really like your approach. But I have (another) question for you: I had taken your older post about creating classes from a database table and added a couple of things around that (mainly creating a Parent Class). Any plans to fold that into this? Any reason you went away from that approach?
(And, yes, it does indeed appear that the zip file is empty. Unless you’re subtly trying to tell us something… :-) )
Zip file should be fixed now.
ScottL: When I did that post about the database table I was creating a ton of classes from Access tables. For whatever reason, I’ve done that far less recently and I’ve haven’t had the motivation to take it any further. It probably would not be too hard to fold that code in and make another menu item. That way you could do a generic class or build one straight from a table. It’s going on the list.
So what is the point of CopyMemory?
Jon: I got that little gem from Rob Bruce. When you have a parent-child relationship, you can run into a situation where you don’t release memory that you think you did. Since VBA uses a reference count to determine when to release an object, a child can have one reference to the parent and the parent can have one reference to the child. Setting either to Nothing doesn’t release them because of their reference count.
If you do it right, you can release them appropriately. But I like Rob’s method better. The child object only holds a Long (not an object) so it holds no reference to the Parent. You can still get to the parent because the Parent property uses the Long to find the object in memory – the Long is it’s address in the memory space. That trades one problem (memory leaks) for another (accessing an object by memory location when it doesn’t exits), but I think the latter is easier to avoid.
I wonder if you can write:
For Each FindByEmployeeNameAndHireDate In Me
If condition Then Exit Function
Next
Poor code I know, but curious to know how concise one could make it.
FindByEmployeeNameAndHireDate should be a Function not a Property; by convention, verbs (“Find”) are used for methods (Sub, Function). Property names should be nouns.
An alternative would be to call it simply “Item” or “Employee”.
The feature would also be more useful if HireDate were Optional.
Just make sure that you never hire two “John Smith”s
:)
DK wrote:
Interesting approach. And you should not have the problem of a child accessing a non-existent parent as long as the parent includes the correct clean up code in its Terminate event procedure.
Of course, you will have to modify every instance of this type of code for Excel 2010 / VB7 to conditionally use LongPtr (http://www.tushar-mehta.com/publish_train/xl_vba_cases/1016%20Office%202010%20VBA.shtml or http://www.dailydoseofexcel.com/archives/2011/08/12/office-2010-vba/)
Rob: I tried that. If it doesn’t find a match, it returns the last object in the collection instead of Nothing.
Dick: thanks for that! So I could write a few less lines of code.. I don’t know if I’d do it though :)
TM,
So really it would make sense to make it conditional at all times since we never know when our code will be used in newer versions of Excel (even if it is just used in our own code for personal use). Thanks for the links. I’ve read about that before in Walkenbach’s Power Programming with VBA book, so I usually try and avoid using api’s since there a bit of a pain, but with it automated, I imagine it’s not such a big deal.
Dick, thanks for the lovely wrappers!
-Elise =)
DK,
New to all this – looks like a great utility. Just having trouble making it work. First tried to add via Excel Add-in. Menu appeared but was unresponsive when you clicked on particular command. Also tried adding via VBE Editor Tools –> References, but menu did not even appear. Some guidance on making add-in work would be really appreciated. As a non-programmer, I love your guys work – learning so much!
Cheers,
GK
Gordon: Adding like any Excel add-in is the way to go. The reason the menu items seemed unresponsive is because you weren’t in the right context. And the add-in isn’t polished enough to say “The current module is not a class module” so you don’t get any clues why it didn’t seem to run. If you choose Convert Public to Property and 1) you’re not in a class module or 2) your class module doesn’t have any public variables the then it seems that nothing happens.
Dick – Much appreciated.
Hi Dick,
the video is unavailable. Could you make it public or viewable again?
OR
Could you post the entire code you wrote here? Without the video or code, this article doesn’t make sense….
Thanks in advance.
@Eijaz: it should be fixed now. Thanks for the heads up.