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.

killall Xcode

Xcode doesn’t handle swapping git branches very well if you use the command line for git. My experience is that I’m often changing branches using my terminal and then I see the Xcode icon start jumping angrily in the dock, when I goto Xcode I see this prompt

Neither option does what I want

• Keep Xcode Version is not right because I’ve just changed branch so I want to load the new version of the project
• Use Version on Disk is the right option but it never works because Xcode gets into a state where it just won’t build

Dealing with the above is slow and results in me needing to close Xcode to reopen the project fully. To add to the annoyance I have to navigate to Xcode, dismiss the UI alert and then close because the UI alert prevents me from pressing cmd + q whilst cmd + tabing over the app.

Hacky solution

YMMV but I’ve taken to running killall Xcode as soon as I see the Xcode icon bounce in the dock. It means I don’t have to waste time navigating to Xcode to handle the UI blocking alert before quitting Xcode. Also because I’m on terminal already I’m only a few keystrokes away.

Updating git-edit

Around 3 years ago I wrote a post showing my git-edit command that helps me very quickly perform a rebase where I want to edit a specific commit in my history. This script has served me well but the original version no longer works when trying to edit history whilst preserving merge commits (merge bubbles), which is the behaviour I want. It’s a pretty small fix and is a classic case of me over thinking the original implementation and that resulting in it being more brittle than it needed to be.

tl;dr here’s the updated script

Quick recap

Given this history:

* c3b7967 (HEAD -> main) Add greet function
* b80d6e2 Add contributors list
* 38eccff Add readme

When I call my git-edit command with the second commit:

git edit b80d6e2

The following will happen

• Git will perform a git rebase --interactive b80d6e2~
• My git-edit script will be handed the file path of the git todo list and make the following change

- pick c3b7967 Add greet function
+ edit c3b7967 Add greet function

  # Rebase b80d6e2..c3b7967 onto b80d6e2 (1 command)
  #
  # <git rebase instructions here>
  #

• Now I make any edit I need and then run git rebase --continue to finish

Preserving merge bubbles - the problem

The issue with the above is that invoking git rebase will flatten merge bubbles unless you pass the --rebase-merges flag.

For example if I have this repo

*   5e327d2 (HEAD -> main) Merge branch 'feature/add-greet-function'
|\  
| * 6c4ea2d (feature/add-greet-function) Add greet function
|/  
*   9b18f79 Merge branch 'feature/add-contributors'
|\  
| * e5d86e3 (feature/add-contributors) Add contributors list
|/  
*   6716452 Merge branch 'feature/add-readme'
|\  
| * a9754cb (feature/add-readme) Add readme
|/  
* c3b7967

When I call git edit a9754cb and make changes the result would be the following

* ab45054 (HEAD -> main) Add greet function
* 07fc0f8 Add contributors list
* a9754cb (feature/add-readme) Add readme

As I alluded to earlier my original implementation was actually more complicated than it needed to be and introduced brittleness - here it is:

#!/usr/bin/env ruby

if ENV['GIT_SEQUENCE_EDITOR'] != __FILE__
  exec "GIT_SEQUENCE_EDITOR='#{__FILE__}' git rebase --interactive #{ARGV.first}~1"
end

todo_list = ARGV.first

lines = File.readlines(todo_list)
  .lazy
  .take_while { |line| line != "\n" }
  .map        { |line| line.gsub("fixup", "pick") }
  .to_a

lines[0] = lines[0].gsub("pick", "edit")

File.write(todo_list, lines.join)

There are two things to call out in this implementation that went wrong.

1) I have hardcoded the assumption that the line I need to edit is the first in the file
2) I’m iterating over the todo list line by line and stopping on the first empty new line

If we look at the todo list for the previous repo when adding the --preserve-merges flag we can see that both points above are troublesome

label onto

# Branch feature-add-readme
reset onto
pick a9754cb Add readme
label feature-add-readme

# Branch feature-add-contributors
reset onto
merge -C 6716452 feature-add-readme # Merge branch 'feature/add-readme'
label branch-point
pick e5d86e3 Add contributors list
label feature-add-contributors

# Branch feature-add-greet-function
reset branch-point # Merge branch 'feature/add-readme'
merge -C 9b18f79 feature-add-contributors # Merge branch 'feature/add-contributors'
label branch-point-2
pick 6c4ea2d Add greet function
label feature-add-greet-function

reset branch-point-2 # Merge branch 'feature/add-contributors'
merge -C 5e327d2 feature-add-greet-function # Merge branch 'feature/add-greet-function'

# Rebase c3b7967..5e327d2 onto c3b7967 (16 commands)
#
# <git rebase instructions here>
#

The format is completely different to the simple example above because git needs more details to know how to rebuild history. The new format includes empty lines in a few places and the line to edit is no longer the first line 🤦🏼.

Preserving merge bubbles - the fix

The simplest fix is to be less strict - instead of treating this as a line by line operation like a person would I can just be fairly slapdash with substitutions to get the same result.

#!/usr/bin/env ruby

if ENV['GIT_SEQUENCE_EDITOR'] != __FILE__
  exec "GIT_SEQUENCE_EDITOR='#{__FILE__}' TARGET_SHA=#{ARGV.first} git rebase -i #{ARGV.first}~1 --rebase-merges"
end

sha = ENV["TARGET_SHA"]
todo_list_file_name = ARGV.first

transformed_todo_list = File.read(todo_list_file_name)
  .gsub(/^fixup/, "pick")
  .gsub(/^pick #{sha}/, "edit #{sha}")

File.write(todo_list_file_name, transformed_todo_list)

In the above I don’t read the lines of the file I just YOLO it and swap any fixup at the beginning of a line with pick and more importantly I change any pick followed by my sha to an edit. The other subtle change is that the when my script is handed the todo list I don’t know what sha the command was invoked with. To get around this when I invoke the rebase I first set an environment variable TARGET_SHA so I can pull it out when updating the todo list. With these changes in place my git-edit command now works for the simple case and in the less common case where I’m editing further back in history with merge commits.

Bonus

When changing history I have two common cases

1) Making edits to source
2) Updating a commit message

The mechanism for doing either of these is identical apart from needing to use the todo list command reword instead of edit. To avoid writing an identical script I instead create a single script called git-edit and then symlink to it with another file called git-reword.

I can then tweak my script to look like this

#!/usr/bin/env ruby

if ENV['GIT_SEQUENCE_EDITOR'] != __FILE__
  exec "GIT_SEQUENCE_EDITOR='#{__FILE__}' TARGET_SHA=#{ARGV.first} git rebase -i #{ARGV.first}~1 --rebase-merges"
end

command = File.basename($0).split('-').last
sha = ENV["TARGET_SHA"]
todo_list_file_name = ARGV.first

transformed_todo_list = File.read(todo_list_file_name)
  .gsub(/^fixup/, "pick")
  .gsub(/^pick #{sha}/, "#{command} #{sha}")

File.write(todo_list_file_name, transformed_todo_list)

In the above the command is determined by which git command was invoked e.g.

Calling git edit invokes the git-edit script and calling git reword invokes the git-reword script. The rebase command is then extracted from the script’s file name using

command = File.basename($0).split('-').last