This post explains how you can make your own functions in Excel without VBA. Suppose you have a spreadsheet like this:

Ugh! That is an ugly formula! Wouldn’t it be nicer if we could write something like =getCity(A2)? Of course, we could make a user-defined function (UDF) with VBA. VBA is powerful enough to create functions, but it is a bit hard to get into, especially if you have never done it before. Wouldn’t it be nice it we can make a function with formula? The idea to have users define functions is by no means mine, it was proposed by Simon Peyton Jones in 2003, but his idea never made it into Excel yet.

So can we hack this? Yes we can! If we use named ranges is a funky way, it is actually doable it a relatively elegant fashion.

First, let’s refactor the formula a little bit. =FIND(“, “, A2) occurs three times in the formula, so if we extract it, the formula gets a bit more readable:

The =FIND(“, “, A2) is now located in B2:

Now, here comes the naming trick. I assume you are familiar with the idea of naming a range. We use the name manager to introduce a new name, and here we copy the exact formula. Because there are no dollar signs (like Excel adds if you click a cell) the name is now relative, and if you drag it down, it updates. Watch me do it in this video:

 

 

 

 

 

If you click the formula, it even shows where the arguments of the function are located:

Unfortunately, we can only apply our self-defined function on a fixed cell (like in this example: the cell to the left of the function) We could ‘fix’ this by making the formula a bit more tricky, and define the convention that the columns following our self-defined names always refer to the arguments:

 

Attribution

There are two distinct reasons for this post. The first was a Twitter discussion between me and Corey Haines, expressing his desire to create higher order functions in Excel. Secondly Rik Nijessen, a student in my IN4400 course asked how to define functions. Thanks go the both of you for the motivation!