In the last few posts we’ve looked at how to manipulate an Excel table through VBA code. In the Table Patterns post, we saw that you can reduce the description of an Excel table to two table-describing tables. In this post, we’ll see how to write VBA code that writes other VBA code that manipulates our table.
There are two sites that contributed to my knowledge and enabled me to create the CodeBuilder: Chip Pearson’s Programming The VBA Editor and Paul Kelly’s Excel VBA Dictionary – A Complete Guide. I can’t thank them enough for the knowledge they’ve imparted to me. I highly encourage anyone interested in writing VBA code for Excel to spend a lot of time on these two sites. They’re two of the best!
In order to run the CodeBuilder, you must make two changes.
- From Chip Pearson’s Programming The VBA Editor: First, you need to set a reference to the VBA Extensibility library. The library contains the definitions of the objects that make up the VBProject. In the VBA editor, go the the Tools menu and choose References. In that dialog, scroll down to and check the entry for Microsoft Visual Basic For Applications Extensibility 5.3. If you do not set this reference, you will receive a User-defined type not defined compiler error. [End Quote]
- In the Excel 2016 version that I’m using, under the File menu choose Options, then Trust Center, then Macro Options, and check the box next to Trust access to the VBA project object model. In your version of Excel you can search the Help file for “access to the VBA object model” and follow the instructions there.
Again from Chip Pearson’s Programming The VBA Editor: CAUTION: Many VBA-based computer viruses propagate themselves by creating and/or modifying VBA code. Therefore, many virus scanners may automatically and without warning or confirmation delete modules that reference the VBProject object, causing a permanent and irretrievable loss of code. Consult the documentation for your anti-virus software for details. [End Quote]
As you might expect, there’s a lot going on in a program that creates other programs. You can download the CodeBuilder here. There are two files in the .zip file. The first is the Table Code Builder itself. this is an Excel file that contains the VBA code to build table manipulators. The second, Table and Field Descrips.xlsx, is a data file that describes the tables and fields we’re going to build. Let’s start with Table and Field Descrips.
There are two sheets each containing one table in Table and Field Descrips. One table describes the tables we’re going to manipulate. The second describes the fields in the tables we’re going to manipulate. Let’s start with the sheet called Tables containing the table called TableTable. The name means, “this is a table that describes tables.” As we saw in the Table Patterns post, it takes six fields to describe an Excel table. There is a description of each field in that post. The second sheet in Table and Field Descrips is called Fields. It holds a table called FieldTable for, “this is a table that describes the fields in a table.” Again, the fields are described in the Table Patterns post.
The example provided here contains descriptions of four tables:
- EmployeeTable: this is the table we’ve been describing in the last few posts
- TableTable: this is a description of the table where the description resides. In other words, this is a description of the table on the Tables sheet.
- FieldTable: this is a description of table on the Fields sheet
- SampleTable: this is a table that we used as we built the codebuilder.
As work on the CodeBuilder progressed, I used early versions of the CodeBuilder to build the code that manipulates the TableTable and the FieldTable. If you look at the code in CodeBuilder, you’ll see that the CodeBuilder generated portions of the code in the TableTableDict and FieldTableColl modules.
The main module, CodeBuilder, contains two primary procedures. BuildCode asks the user for a data file then iterates through TableTable build a module for each table. BuildModule builds one code module for each table. We’ll examine BuildModule since that’s where the code building action takes place. I won’t quote it completely here; you should download it to examine all the code. We’ll go step by step.
First, the header, declarations, and initializations:
Private Sub BuildModule(ByVal TableName As String, VBProj As VBProject, ByVal Dict As Scripting.Dictionary)
Dim ShtName As String: ShtName = TBLTBL_Get_SheetName_TableName(TableName, Dict)
Dim ModName As String: ModName = TBLTBL_Get_ModName_TableName(TableName, Dict)
Dim TblAbbrev As String: TblAbbrev = TBLTBL_Get_TblAbbrev_TableName(TableName, Dict)
Dim PrimKey As String: PrimKey = TBLTBL_Get_PrimKey_TableName(TableName, Dict)
Dim UpperPref As String: UpperPref = UCase(TBLTBL_Get_CodePref_TableName(TableName, Dict)) & "_"
Dim LowerPref As String: LowerPref = LCase(UpperPref)
Const DQUOTE = """" ' one " character
Const SQUOTE = "'" ' one ' character
Dim Line As String
Dim LineNum As Long
Dim Key As String
Dim Fields As Collection
Dim Fld As Variant
Dim CodeMod As VBIDE.CodeModule
- TableName is the name of the table we’re building
- VBProj is the name of the Project where the code will go
- Dict is pre-populated with one dictionary entry for each table. Each dictionary contains all the data from one row of TableTable and a collection. The collection contains all the FieldTable data for the table.
- ShtName: the name of the sheet where our table will go
- ModName: the name of the module that will contain our code for this table
- TblAbbrev: this is what we will call our table in ModName’s code
- PrimKey: if our table will have a key that we can use for searches, this is the field name of that key. The field name must appear in the FieldTable. If there is no primary key for the table, enter “None”.
- UpperPref: the prefix all our table variables will use in ModName. Remember that in Table Manipulation Code we use upper case prefixes to denote procedures that are Public and lower case prefixes to denote Private procedures.
- LowerPref: the Private version of the prefix
- DQUOTE and SQUOTE: constants that make it easier/clearer when we’re inserting a double quote or single quote character in ModName
- Line: the string variable that will contain each line of code we’re inserting into ModName
- LineNum: the position in ModName where Line will go
- Key: Variable containing PrimKey
- Fields: the collection containing the field data for this table
- Fld: one Field from Fields
- CodeMod: the VBA name for NodName
We use a convention to help illustrate each line of generated code. Code that generates code can be hard to read; it’s difficult to separate what’s being written from what’s doing the writing.
'Private smp_SampleAbbrevSheet As Worksheet
'Private <LowerPref><TblAbbrev>Sheet As Worksheet
Line = "Private " & LowerPref & TblAbbrev & "Sheet As Worksheet"
AddLineToModule Line, CodeMod, LineNum
The first commented line, line 1, is how the code will look in ModName. This is where we used SampleTable; to illustrate the generated code. The second commented line, line 2, is pseudocode showing how the variable information will be inserted into the line of code. The next line, line 3, populates Line with the text of the generated line of code. The final line, line 4, adds Line to the module called CodeMod at position line LineNum.
Build Module starts by outputting the declarations at the beginning of CodeMod. There’s a pattern that’s repeated three times.
For Each Fld in Fields ... Next Fld iterates through all the fields in the table to output the column title constants, the range variables, and the column numbers. We’ll see this pattern repeated many times throughout BuildModule.
Next we generate the Initialize procedure where we see the
For Each Fld in Fields ... Next Fldpattern twice to populate the range variables and the column numbers.
The NumCols and NumRows procedures are straightforward.
If there’s a primary key in our table, we generate an Exists function. The user of this code will use the Exists function to ensure a primary key value exists in the table.
If there’s a primary key we next iterate through the fields using our pattern to generate Get and Let routines for each field in the table. We skip the Primary key field; there’s no reason Get it since we’re using the primary key value to do the search. If we already have the primary key, we don’t need to search for the value of the primary key.
Finally, we generate a CheckStructure function that the user could use to confirm their table has the proper structure. We haven’t discussed this function in any of the previous posts on this subject.
For the final step, we return to the BuildCode routine to generate skeleton tables matching the table descriptions in the data file. This gives the user a starting point to populate their tables.
This is the end of my series of posts on Excel table manipulation. Please sign up for our email list to receive notification when we post something new.
VBA Class Collections
Postscript: As I’ve studied and learned about class module collections, I’ve decided that there may be a more elegant way to write the CodeBuilder and the code it generates. We could have Table, Tables, Field, and Fields class modules patterned after these three examples: number one from Microsoft, number two from Chip Pearson, and number three from Rob van Gelder in Dick Kusleika’s Daily Dose of Excel. There may be a CodeBuilder version 2 somewhere in my future. In the cases where there is a primary key field, I plan to implement them as VBA Dictionaries rather than collections to enable direct access by keyword.
Added 7/24/17: The code for this blog entry can be found in GitHub.