VBHelpers Build 3

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.

In this code fragment, I loop through all the code lines looking for lines that are LIKE this constant: . I started out looking for Property Let statements, but ran into trouble. When I create a class that I know will come from a Range, I generally type in the Public variables in the order they appear in the Range. I can control that order, but I have less control over the order of the Property Let statements and frankly I don’t want to control it. I also can’t be sure there wasn’t a Property Let written later that isn’t a simple getter/setter.

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:

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

7 thoughts on “VBHelpers Build 3

  1. 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.

  2. Hey Dick, love VBHelpers, been using it since you posted the original a while back.

    FYI, the new download isn’t working for me…

  3. 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?

  4. 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.

  5. I didn’t think about using a disconnected recordset.
    I’ll tidy up my code, then post it up :)

  6. Question: Can a Microsoft 1998 Technical Basic program be converted to a Visual Basic program? Thanks


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.