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.
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
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:
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
.Make = rCell.Offset(0, 0).Value
.Model = rCell.Offset(0, 1).Value
.Year = rCell.Offset(0, 2).Value
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