Cosine Jeremiah and his Musings

I wrote a little spreadsheet template language for Excel VBA (Visual Basic for Applications). What for? Say you have a report you create often, and you’re thinking of making a macro in Excel to help automate its generation. You want to report on the sales of your widgets from multiple locations, and you already have a template in mind for how the report should look:

Template of Widget Report in Excel

I thought it would be so nice to be able to do this that I wrote the VBA to do it. I call it TemplateKicker. To use it, I copy the TemplateKicker module and the TemplateKickerVariables class module into my spreadsheet from TemplateKicker.xls (this file also contains the example shown here). Then I write a macro to insert the values for each location I am reporting on:

Public Sub Widget_Report()
    Dim Vars As TemplateKickerVariables
    Dim LocationIDs As Variant
    Dim WidgetSales As Variant
    Dim Index As Integer
    
    LocationIDs = Array("A-12345", "B-22222", "C-33333", "D-2R2", "E-5555")
    WidgetSales = Array(34, 12, 15, 6, 39)
    
    Set Vars = New TemplateKickerVariables
    For Index = 1 To 5
        Vars.SetVar "location:" & Index & ".id", LocationIDs(Index - 1)
        Vars.SetVar "location:" & Index & ".widgets.sold", WidgetSales(Index - 1)
    Next Index
    
    KickWorksheet(ActiveWorkbook, Sheets("Sheet1"), Vars).Name = "Widgets"
End Sub

One run of the macro later, and I have a new worksheet with the results:

Widget Report after running macro

TemplateKicker also has the following features that I will showcase in upcoming blog posts:

• Nested ForEach loops
• Rows to insert between items being looped over
• Generation of named ranges for use in formulas

Even with all this, however, TemplateKicker does have some limitations:

• The template language is somewhat “fragile”—spaces must be exactly placed.
• Errors are not reported. Bad syntax or variable names produce undefined results.

Still, I think this is a great start to doing some pretty awesome things in Excel VBA. I do not know if I will develop it further, but if you find it interesting please let me know!

As a side note, CosineWave Technologies Inc. now also offers VBA solutions for business automation now. 😉 Please drop me a line if you have need for some tools to automate your workflow when using Microsoft products (or many other products for that matter)!