Unit tests with Excel Lambdas

Disclaimer: I do not personally use Excel for anything so this may be completely useless but it was an interesting exploration for me.

Excel added a LAMBDA function, which allows you to define your own functions without diving into the complexity of learning a new programming language (VBA) and worrying about increased security permissions to run your workbook. A quick google for “Unit testing in Excel” doesn’t come up with many interesting results which suggests it’s not a common practice but I wonder with the introduction of LAMBDA if this is something that could be more accessible to people.

The problem

Let’s start with an expression that prints a greeting and includes the name of the person to greet from a cell.

The formula being repeated (even though simple) doesn’t sit right with me as there are many possible ways I can mess this up. The idea of DRY (Don’t Repeat Yourself) is being violated and it feels like this expression is a bit adhoc when maybe there is a named concept I could use instead.

The other issue is that there is no validation that I wrote this function correct in the first instance or any of the places it is duplicated. Having someone review my formula just confirms that someone else assumes my formula is valid but there is no proof that it calculates as expected.

Taking the time to give this expression a name means there will be one authoritative place where this logic is defined, it’s easy to change and know that all uses will be updated. Also if the naming is done well it will communicate intent without later readers needing to parse the formula to understand what calculation is being done.

Let’s go step by step to test driving a new function that will replace the inlined expressions above.

Setting up a basic test function

To test drive the function I’m going to need some way to repeatedly verify that the behaviour is correct as I iterate the development of my GREETING function. I’m going to create an assertion function that will check that evaluating a formula returns an expected value and print a helpful error if not. I’m going to start with nothing more complicated than this (using the “Editor” mode in the Advanced Formula Environment)

Assert.Equals = LAMBDA(actual, expected, if(actual=expected, "", "Expected '" & expected & "' but got '" & actual & "'"));

Let’s try this out to see if it behaves as expected

In the above we can see that for Row 2 the Actual and Expected match so the Result is left clear (we don’t want to make noise when things are working). In Row 3 the Actual and Expected don’t match so we print an error stating what went wrong.

There are probably better ways to structure the above but it will do for my example. We have a mechanism where we can build up examples across different inputs/outputs and check that the behaviour is as expected.

Test Driving GREETING

Now we have an assertion let’s turn back to our GREETING function. I’ll start by setting up the assertion for a basic example:

The first failure I get isn’t related to logic as we haven’t defined the GREETING function yet so we are getting a name error.

I’ll define a stub function so that the error is related to logic and not a missing function.

GREETING = LAMBDA(name, "");

With this my error moves as expected

For the sake of following every introductory TDD (Test Driven Development) text I’m going to do the pointless task of making this test pass with a silly implementation (please don’t scream at your screen).

GREETING = LAMBDA(name, "Hello, Paul");

Now the test passes

With a passing test we can now introduce another example that will fail in row 3

At this point we can reintroduce the original formula to get the expected behaviour

GREETING = LAMBDA(name, "Hello, " & name);

Digging deeper

The above might seem like a long way to get the same result but before we continue let’s just take stock of what we have done.

• Introduced a named concept “GREETING”
• Added two examples to check the function behaves as expected
  • This alerts us to changes to the function that will have unintended consequences
• The examples also act as documentation for future readers to understand the original authors intent when writing the expression

Let’s make one further expansion that makes the greeting slightly more complicated, which we can now do safe in the knowledge if we break existing behaviour our test will fail. We want to remove the “name” from the greeting if it is empty - we start by adding a failing test.

We can get the new test passing by updating our function

GREETING = LAMBDA(name, if(name="", "Hello", "Hello, " & name));

This is great we introduced new behaviour and we can provably say that our existing expectations have not been effected. Although the final function is still fairly simple I think copying this in all rows like we had in the beginning adds more mental overhead for future readers than I’d personally feel comfortable with and allow mistakes/typos to sneak in.

Conclusion

¯\_(ツ)_/¯ I’m not an Excel user so I’m not sure how useful this is but it seems like a way to introduce some good software development practices into a space where it might not have previously been so easy to do.