VBA Framework

In Create Classes… I had this todo list:

  • Create the class module rather than copy and paste
  • Name the class module based on the table name
  • Select the database and table from a list on a userform
  • Establish Parent Child relationships between two classes
  • Create functions to fill the class from a recordset
  • Create a userform to add/edit/delete records

I need to add

  • Create relationships between two child classes
  • Automatically set an ADO reference
  • Option to make functions instead of subs (for error handling)
  • Allow recreation of the classes and fill code

You can Download Payroll.zip. It contains the VBAFramework.xls workbook and a Payroll.mdb Access database.

Here’s where it all starts

Public Sub PickTable()
    Dim axCat As ADOX.Catalog
    Dim ufSelect As USelectTable
    Dim vaSelected As Variant
    Dim axTable As ADOX.Table
    Dim i As Long
    Dim sChildCode As String, sParentCode As String
    Dim sFillCode As String
    Set axCat = New ADOX.Catalog
    Set axCat.ActiveConnection = gadCon
    Set ufSelect = New USelectTable
    Set ufSelect.Catalog = axCat
    If Not ufSelect.UserCancel Then
        vaSelected = ufSelect.SelectedFields
        Set axTable = ufSelect.Table
        If ActiveWorkbook.Name = ThisWorkbook.Name Then Workbooks.Add
        ChildClassCode axTable, vaSelected, sChildCode
        MakeChildClass axTable, sChildCode
        ParentClassCode axTable, sParentCode
        MakeParentClass axTable, sParentCode
        FillClassesCode axTable, vaSelected, sFillCode
        MakeFillModule axTable, sFillCode
    End If
    Set axCat = Nothing
    Unload ufSelect
    Set ufSelect = Nothing
End Sub

Most of the code is bunch of string concatenation, which is pretty boring. But not as boring as actually typing the code. When you run PickTable, you get

It allows you to select a table and fields to include in your classes. The code creates a class based on the field and a collection class to serve as its parent. Then it creates (or modifies an existing) standard module that holds code to fill the classes.

There’s no customization options (it’s in pre-Beta). But if you happen to use the same conventions as me, it will work perfectly. First, it assumes you name your Access tables as plural and your classes exactly the same way (but singular for the child class). It assumes you have an ID field that is SingularTableNameID, so tblEmployees will have an EmployeeID field. That one is used to uniquely identify the record in the collection class, so it’s important. I should probably not allow the user to deselect that one in the listbox. Oh yeah, it also assumes you prefix your tables with “tbl”. Everyone does that, right?

I imagine I’ll add some customization options as I go so it’s useful to more than just me. So if there’s a convention that’s near and dear to your heart, leave a comment. But I don’t plan on making it fully customizable. I’m going the Ruby on Rails route of “this will work as long as you do it just like me”.

Let me know what you think.

Posted in Uncategorized

9 thoughts on “VBA Framework

  1. Love it. Love it love it LOVE IT. I took the original code from the “Create Classes” post and started using it immediately because I found it just so very useful. But this is great stuff! It takes so much of the grunt work out of it for you.

    I’m afraid I can’t offer any huge suggestions about what to add or keep at this point. You might want to let users chose whether the fields they’re selecting for classes are read-only or read-write (that’s all I’ve got off the top of my head), but whatever the additions, I can’t wait to see the next iteration of this thing.

  2. Good one on the read-only, Scott. I think I’ll also let the user choose the VBProject from a dropdown rather than write to the activeworkbook. I really want to make the Paychecks class a child of the Employee class, but I’m not sure the best way to implement it yet.

  3. Something else that came to mind this morning that would make it even more dynamic is to have an additional field at the top of the form where you can browse for the MDB you’re looking for using GetOpenFilename. That way you don’t have to hardcode the path and filename.

    Just thinking out loud, really.

  4. Again, thinking out loud, specifically about the parent/child class thing, I’m guessing you’d need to read the available classes of whatever your target VBE Project is (or will be) and, chose one (via combobox, maybe?), then, if you choose to make that a child of the selected parent, then you’d have to do a few additional steps:
    1. insert the child into the parent class
    2. (and this one would be optional, but handy) set up the parent pointer

    Also, looking through your example, what would be a great side-project to this one (ha, ha) would be to create the same kind of framework but for user forms (since they’re really just class objects, after a fact). But just thinking about how to go about that makes my head want to explode.

    But, you’d have one killer VBA Framework/Project Builder!

  5. Re parent/child: I think the class modules will be easy since they’re naturally encapsulated. But filling the classes presents the problem. I can definitely create code that gets an Employees instance as an argument and fill a Paychecks class for that employee, but from where would I call that sub? Would I modify the FillEmployeesClass to call FillPaychecksClass(clsEmployee as CEmployee)? That’s seems tougher particularly since the user could have modified FillEmployeesClass and I wouldn’t be sure just where everything is. I could just not call it and leave that step to the user. Or I could create the call code, put it outside of a module for the user to cut and paste into the proper place. I need to do just enough to make this useful without going overboard.

    Keep the suggestions coming.

  6. Something else I noticed (just a error handling piece), how do you handle the run-time error you get when you click the windows close button instead of the form close button.

    (I get this funky “callee, not server callee, may have been executed, blah blah, etc etc” message).

  7. I dont know where to put the question. But I m kind of bugged so I am putting it here. I am a Major In Metallurgy and Material Science but interested in excel. I need help on a particular topic.
    I have made a userform in excel which has 1 combobox and 5 textboxes.
    i have source data as an excel sheet in the same workbook.
    I initialized the userform to display all values of my combobox which is in the 1st column
    Now if I select a particular value from the dropdown list of the combobox I want the textbox fields to be automatically filled by their corresponding values in the spreadsheet. How to do this. Please Help???
    Thanking in Advance

Leave a Reply

Your email address will not be published. Required fields are marked *