RESEARCH

Spreadsheet Programming

I have been a long-time fan of using spreadsheets to design and prototype algorithms. By having different cells and regions of cells represent the different segments of the algorithm, spreadsheets offer a powerful way to view many of the global intermediate behaviors of the algorithm simultaneously. Once the algorithm has been implemented in a spreadsheet, implementing the algorithm is generally very easy to translate to procedural or functional (or imperative) code. Furthermore, the testing is also very easy, as it can be validated against the results from the spreadsheet. I have designed some of the more complex algorithms I’ve dealt with in spreadsheets first, and at least qualitatively feel like I saved significant time.

This is not to say that all algorithms lend themselves to design ease in spreadsheets. Spreadsheet designing/prototyping doesn’t work in every case. I have always stuck with the built-in functions of spreadsheets to implement the algorithms, because the benefit comes from using small formulae over many values.

The Lambda Function in Excel

Since the spreadsheet is effectively a spatial representation of functional programming, it is often useful to be able to specify one function to operate on a block of data. Unfortunately, changing the function/formula frequently is cumbersome; it requires changing one cell and then applying it to all the rows and then all the columns, perhaps even to multiple sheets. To make this much easier in Excel, I implemented a lambda function in Visual Basic (VBA). When I initially searched the web, I was surprised that I could not find an implementation of this. It is pretty straight forward.

Using the Visual Basic editor that comes with MS Office/Excel, add a new module. In it, put the following code:

Function lambda(f As Variant, Optional a As Variant, Optional b As Variant, Optional c As Variant, Optional d As Variant, Optional e As Variant) As Variant
If Not IsMissing(a) Then frepl = Replace(f, "$1", a)
If Not IsMissing(b) Then frepl = Replace(frepl, "$2", b)
If Not IsMissing(c) Then frepl = Replace(frepl, "$3", c)
If Not IsMissing(d) Then frepl = Replace(frepl, "$4", d)
If Not IsMissing(e) Then frepl = Replace(frepl, "$5", e)
lambda = Evaluate(frepl)
End Function


To use the lambda function, enter “=lambda(expr, param1, param2)” into a cell. The most useful way is to have expr be a reference to a cell containing an expression string, using $1 to $5 as the parameters. For example, the expression cell could contain =”exp(-$1*$2)*$3+$4″, while the cell to be evaluated could contain =lambda($F$1,$A$5,$A$6,A14,A15). Using string manipulation functions, the expressions themselves may even be generated dynamically. To add more possible parameters, just add more input parameters and corresponding replace conditions.

Other spreadsheet programs such as OpenOffice Calc, should be able to do this in a similar fashion. Though I don’t have VBA that uses Microsoft’s .NET 2.0, I did come across some documentation that IsMissing is depricated, and that the preffered method is to use function overloading. That way, one function is needed for every number of arguements.