Domain Specific Languages – Part 1

I’ve long been a bit of a fan of the Ruby language (yes, there is more to life than VBA). I’m currently reading Metaprogramming Ruby, from the excellent Pragmatic Programmers. This is all about taking a very dynamic, data-driven view of programs, customising or extending class/object capabilities at run-time. The language design owes a lot to Smalltalk, which followed the everything-is-an-object philosophy (so classes are objects, and therefore need their own classes: metaclasses).

Anyway, in the Ruby community there’s interest in Domain Specific Languages: problem-specific ‘dialects’ of Ruby that offer higher level expressions for particular domains. Which led me to wonder what a DSL would be like in Excel/VBA.

Now, Excel has a sort of DSL in its formula language. It’s specific to the domain of functional calculation and lookup, with built-in functions for various categories. Some categories, like the financial functions, are focused on a particular domain; other categories are more general.

With VBA, we can clearly add custom functions relevant to a particular domain: financial, engineering, whatever. However, a formula typically produces a single value and puts it in a single cell, so the effect is quite small scale. While we can have functions that return arrays, these can be unwieldy, and can be very dependent on worksheet layout. Also, there are manageability limits to the complexity of formulas. So it could be difficult to use formulas alone to build and manipulate complex structures or models.

For example, there are numerous problems that require a node-link graph structure: workflow, scheduling, resource tracking, etc. Although such a structure could be represented as data on a worksheet, it would be quite cumbersome to manipulate it in this form.

An alternative approach would be treat the worksheet data as instructions, which are interpreted to build an internal (VBA) object model. Other types of instruction would be commands to manipulate the model, and queries of it in various ways. This implies that we have some sort of ‘worksheet interpreter’ (in which we might guess that the Evaluate and Run procedures will come in handy).

The example I’ve been looking at so far is what you might call ‘payroll type’ processing. There is a set of entities (employees, say), each of which needs to be processed, in order to determine a final value (their monthly salary payment). There is a set (sequence, really) of rules, which need to be applied to each entity, possibly modifying the value. For example: if the employee grade > 3 and they’ve worked more than 1 year and it’s December, pay them a bonus of £100. Handling all the exceptions and modifications is a classic problem for payroll systems.

Now, it’s not impossible to do this with formulas alone, but you’d be knee-deep in IF() functions. The intermediate values would be visible (presumably in columns, with the employees in rows), leading to a humungous worksheet. Another issue is that this type of processing needs to be done at a specific date/time (so we can work out things like length-of-service). We don’t want the calculations to be dynamic; we want a nice big ‘Run’ button.

I’ll describe the solution so far in the next posting.


6 Responses to “Domain Specific Languages – Part 1”

  1. 1 John Tolle July 31, 2009 at 8:25 pm


    I’ll be looking forward to seeing more on this. I’m quite interested in the idea of Excel formulas as a sort of DSL. On a recent project where we had to build a complex model, and it had to be 100% Excel+VBA, a colleague of mine and I experimented with parameterizing formulas and turning them into functions. We also built a little FSM that used these as state transition functions to run some inherently procedural logic in the context of our worksheet. Since then, I’ve been playing around on my own time with making the functions act like they were “first-class” Excel values.

    I’m not much of an Excel power user myself, so I’m still exploring the Excel+programmer community out there to see what other similar ideas are out there. (I literally put your blog in my reader a few days ago, so great timing!) I know from reading through Simon Murphy’s archives that he has written some on the formula-as-function topic as well, and how writing VBA UDFs or XLLs departs a lot from the development model of a typical Excel user.

    Just in the past few weeks we’ve been talking about whether or not anyone would be interested if we started a blog or just put some (quite raw) code out there. So maybe I can hijack your comment thread and ask…

  2. 2 Elizabeth December 21, 2013 at 1:26 am

    Helpful information. Lucky me I discovered your site by
    chance, and I am shocked why this coincidence did not took place earlier!

    I bookmarked it.

  3. 3 adiphene weight loss June 19, 2014 at 7:29 pm

    This basically suggests that you will be able to eat anything that
    you want without the worry of gaining weight.
    This alone offers it some credit score as a authentic contender
    within the eating regimen complement arena; at the moment oversaturated with
    poor high quality diet products. One of the essential explanations why numerous hyper-propelled individuals never
    appear to realize their weight loss objectives on time is Emotional Eating.

  4. 4 domain checker August 1, 2014 at 3:15 pm

    Valuable information. Fortunate me I found your web site accidentally, and I’m surprised why
    this accident did not came about earlier! I bookmarked it.

  5. 5 pooltable911 pat September 23, 2014 at 3:43 pm

    Your style is really unique compared to other
    folks I have read stuff from. Thank you for
    posting when you’ve got the opportunity, Guess I will
    just book mark this web site.

  1. 1 Domain Specific Languages – Part 2 « Roy MacLean’s VBA Blog Trackback on August 4, 2009 at 9:13 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

July 2009
« Jun   Aug »

%d bloggers like this: