In the previous posting, I started musing on using Excel to host ‘domain-specific’ languages (DSLs) – that is, ways of tackling particular problem areas or types of problem. For starters, I’m looking at a ‘payroll’ type of problem, where we want to take each member of a set of entities (e.g. employees), and apply a sequence of condition-modification rules to them. There are potentially many rules for various special cases and exceptions. Using formulas alone would embed the rules in the conditional structure (IF() functions), and would be harder to read and modify.
One approach is to support the iteration and rule-application aspects in a simple (VBA) interpreter. The interpreter is generic with respect to the actual data involved. The rules themselves are expressed on a worksheet as Evaluate-able expressions.
Here are the entities (typically, a large number thereof):
‘grade’ and ‘startdate’ are two (of possibly many) attributes.
Here are the rules:
Column B is for the Boolean condition expressions.Note that some can be TRUE.
Columns C and D are for entity properties that are updated by some of the rules. These are effectively assignment statements. So C3 is equivalent to ‘payment = base’; C5 to ‘payment = payment + servicebonus’. The expressions can refer to entity properties (e.g. grade) and named parameters (e.g. grdincrement). The first rule adds an additional, calculated property daysemployed.
The outputs are selected properties of an entity, with the final values from application of the rule sequence:
Now the VBA side. An Entity is a class module that simply defines a Dictionary to hold the properties (i.e. as key-value pairs). Dictionary is from the Scripting library; it has a few advantages over Collection, such as an Exists(key) method. As it happens, there is only one entity at a time, so the class module isn’t strictly necessary, but it just feels better to have entities as objects.
The Interpreter has a number of iteration levels:
- initialize and process each entity
- for each entity, process each rule
- for each entity-rule:
- evaluate the condition
- if true, update each output property
- and at the end, put the final outputs on the Outputs worksheet.
The entity property names (i.e. the keys into the Dictionary) come from the header rows, which are retrieved at the start.
The condition and update expressions are evaluated using the Application.Evaluate function. Here’s the condition evaluation for a rule:
Sub ProcessEntityRule(ent As Entity, r As Integer) 'r is the rule number rulefields = _ Range("Rules!A1").Offset(r, 0).Resize(1, _ rulefieldcount).Value Dim condexpr As String 'condition expression Dim condexpr2 As String 'condition with substitutions Dim cond As Boolean 'condition result condexpr = rulefields(1, 2) condexpr2 = Substitute(ent, condexpr) cond = Evaluate(condexpr2) If cond Then Dim op As Integer Dim outname As String 'e.g. "payment" Dim outexpr As String 'e.g. "base" For op = 3 To rulefieldcount outname = ruleheaders(1, op) outexpr = rulefields(1, op) UpdateEntity ent, outname, outexpr Next End If End Sub
Substitute replaces any property names in condexpr with the corresponding property values. This means that the names have to be distinct from each other, and from parameter names. So, for example, ‘grade’ and ‘gradeincrement’ would clash (hence ‘grdincrement’).
Here’s the entity update:
Sub UpdateEntity(ent As Entity, outname As String, _ expr As String) Dim expr2 As String 'with substitutions Dim newval As Variant If Len(expr) > 0 Then expr2 = Substitute(ent, expr) newval = Evaluate(expr2) If ent.Properties.Exists(outname) Then ent.Properties.item(outname) = newval Else ent.Properties.Add outname, newval End If End If End Sub
So the rules can contain any expressions that can be handled by Evaluate. However, there seem to be a few strangenesses here. For example, on a worksheet, the formula:
=TODAY() – DATEVALUE(“01/09/08″)
evaluates to 337, treating the date as UK-style. However, in the VBA, the local date format is not applied: the date is treated as US style, giving 573. So entity #3 erroneously gets the service bonus of £50. Odd.
This example has gone only a little way beyond formulas, because it deals with one object at a time. What I’d like to try next is to have configuration instructions that construct or modify a multi-object model of some kind, which is then manipulated and queried. I need to find a good example – any suggestions?