In this iteration, I
- Create a generator for a FillFromRange method of the parent class
- Get rid of those unisightly blank lines when converting Public variables to Property statements
- Change FindBy to ChildBy (thanks Steve J)
If I have a range of records that I want to put in a class, which happens a lot, I wanted to create a quick way to generate the code to fill the class. It’s not particularly tricky code to write, but I get sick of typing all the Offset statements. Most of the code is string concatenation and isn’t very interesting. The meat of the procedure is identifying which properties to create lines for.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
For i = 1 To mChild.CodeModule.CountOfLines If mChild.CodeModule.Lines(i, 1) Like sPRIVATE Then lStartLine = 0: lEndLine = 0: lStartCol = 0: lEndCol = 0 vaSplit = Split(mChild.CodeModule.Lines(i, 1), Space(1)) sFind = "Public Property Let *" & Mid$(vaSplit(1), 2, Len(vaSplit(1))) & "*" bFound = mChild.CodeModule.Find(sFind, lStartLine, lStartCol, lEndLine, lEndCol, , , True) If bFound Then sProc = mChild.CodeModule.ProcOfLine(lStartLine, vbext_pk_Let) If Not (Right$(sProc, Len(sID)) = sID Or sProc = sPARENT) Then scode = scode & String(3, vbTab) & "." & mChild.CodeModule.ProcOfLine(lStartLine, vbext_pk_Let) & _ " = rCell.Offset(0, " & lCnt & ").Value" & vbNewLine lCnt = lCnt + 1 End If End If End If Next i |
In this code fragment, I loop through all the code lines looking for lines that are LIKE this constant:
So I look for the private variables, then look for the corresponding Property Let statement. If I find it, I grab the ProcOfLine, which gives me the string I would call using the class in a procedure; msModel becomes just Model. I ignore any property that ends in ID and the Parent property because I set those in the Add method. However, it should be noted that while I want this code to compile right out of the gate, I don’t have any problems with editing it when necessary. If I want to assign the ID property explicitly, I can still do it with an edit and most of the rest of the code is still generated for me.
The sCode variable is extended to include the new line, which is in a With block accounting for it starting with the period. The convention is that the private variables in the child class are in the same order as the data appears in the range. Again, if that’s not right it’s still easier to edit than to write from scratch.
If I have a CCar class with Make, Model, and Year properties, it generates a method like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Public Sub FillFromRange(rRng As Range) Dim rCell As Range Dim clsCar As CCar For Each rCell In rRng.Columns(1).Cells Set clsCar = New CCar With clsCar .Make = rCell.Offset(0, 0).Value .Model = rCell.Offset(0, 1).Value .Year = rCell.Offset(0, 2).Value End With Me.Add clsCar Next rCell End Sub |
I added a couple of functions to find 1) the first Property statement and 2) the first blank line after the first Property statement. If I add properties later, it would put the newly created Property statements one line away (or at the bottom). I didn’t like it, so I keep them all together and get rid of the blank lines.
Finally, Steve commented that I shouldn’t be using verbs in Property Get procedures, and he’s absolutely right. I changed the FindBy statement to ChildBy. So instead of FindByMakeAndModel As CCar, it generates CarByMakeAndModel As CCar.
Next up on my todo list for this addin:
- Refactor the code so I can do all this stuff at once as well as one at a time
- Automatically create a class from an Access table
- Generate a ChildrenBy method so I can return a filtered parent class. clsCars.CarsByMake(“Toyota”) would return a CCars instance with only Toyotas in it.
- Generate inter-class relationships. If I have CInvoices and CInvoiceLines, I want to generate the relationship between them automagically.
You can download VBHelpersBuild3.zip
How about creating an Interface for FillFromRange?
Minimally there would be the complementary routines, tentatively named:
Public Sub FromRange(ByVal rg as Range)
Public Sub ToRange(ByVal rg as Range)
Then any collection type that wants to could implement this feature, and any client code could do range/object data transfers in a uniform way.
Hey Dick, love VBHelpers, been using it since you posted the original a while back.
FYI, the new download isn’t working for me…
Link fixed. Thanks for the heads-up.
Hi Dick,
What’s your approach for sorting collections of items?
I don’t often need to sort, so my implementation has always been kind of clumsy and non-generic.
The last time I did it, I tried a little harder:
Created a CompareTo method in my item class ie Employee.CompareTo returns -1, 0 or +1
Created a Sort method in the collection class which performs an insertion-sort (I converted the wikipedia pseudocode to VBA) and got it using the CompareTo method.
Have you ever needed to sort? and if you do, how did you do it?
Same here, I don’t need to sort too often. Either I don’t care about the order or the class is filled in the order I want. If I need to do one sort and there aren’t a lot of items, I’ll do a simple bubble sort deleting the item and inserting it where I want. If I have a large collection or I need to do a lot of different sorts, I’ll but the items in a disconnected recordset and use that to sort.
I don’t like any of the ways I sort collections, but it doesn’t come up enough to bother me. That CompareTo code sounds like a blog post.
I didn’t think about using a disconnected recordset.
I’ll tidy up my code, then post it up :)
Question: Can a Microsoft 1998 Technical Basic program be converted to a Visual Basic program? Thanks