You could create your own Excel functions using VBA since long time ago, but there is a new way of creating them: LAMBDA functions.
As of today, this option is available for the general public, but this option was only available for Office Insiders participants when this post was released on 2022.
The LAMBDA function official documentation is great but let me show you how this works with an actual example. Imagine that you have a requirement where you need to calculate something as easy as the days that are left in the month for a given date. You could achieve this with this formula, if your date is stored in cell A1:
=DAYS(EOMONTH(A1,0),A1)
In order to create a lambda function, you need to use the formula LAMBDA . This formula requires one or several parameters and the code that will be applied using those parameters.
For our example, we need one parameter that is going to be called date and the second parameter is going to be the formula that we want to apply, but instead of using A1 or another reference, we need to use the name of our parameter, in this case, date
All of this is great, but the real power of LAMBDA functions is to be able to reuse them and to be called like we called the rest of the formulas. Instead of repeating this calculation many times, we want to build our own formula and be able to call it with something easier to remember, such as DAYSLEFTINMONTH. In order to do it, we can use and add-in called advanced formula environment as explained in this post by Chris Gross.
If we click on the + symbol, we can create our brand new formula, giving it a name and typing the code.
The next step is to inform the name manager that we have a new formula that need to be included in our toolbelt. In order to do so we must synchronize our functions with the name manager, clicking on the upper left button. Doing so will display our new function in the Name Manager where we could review it and also include a description.
Reusing formulas
Now that we now how to create our own formulas, it will be awesome if we could reuse them across devices or even share them with your colleagues. Instead of directly typing the formula in the advance formula environment, we can import a Github Gist containing our code. This is one of the many features of this add-in, besides adding comments, formatting our code and even use intellisense writing your formulas.
I have created another example. In this situation, I want to calculate the age of a person given a birth date. The Excel formula will be something like this:
INT(YEARFRAC(A1, TODAY())))
I created a gist that contains this calculation in a lambda function called AGE. If we import it using the advance formula environment we can see that the new AGE function is available.
In Chris Gross’s post there is a more complete gist where you can find a collection of several functions. I suggest to import that gist and check them. We can use a namespace to organize them, so in case that we have multiple functions from different sources we can distinguish them.
