Excel Lambda Library

Lambda is a powerful new function in Excel that lets you create your own functions. Once you have a few of these in your toolbox, you’ll want to add them to other workbooks and use them there. Manually adding them can be tedious. This article explains how to maintain a ‘library of lambda functions’ and easily import them with a press of a button.

For an introduction to Lambda functions, see this article.

Note: Lambda is currently only available in Excel 365.

UPDATE: Part 2 + Download the PERSONAL.xlsb file

Backstory

I recently completed a course in Corporate Finance. Students were allowed to use “anything we create” in our exams, so I investigated what options were available within Excel. I created sample problems (templates) for each problem type and made a hyperlinked index to quickly find and jump to example problems. This was OK and probably would have been enough but I wanted a solution that didn’t require opening up a specific workbook. I also wanted something that I could continue to use long after my exam was completed. The next approach I tried was using VBA functions. These could be saved in a shared macro workbook (personal.xlsb) and would be available across every workbook. I wrote a few functions but felt VBA was clunky. I also didn’t like that the function name needed to be prefixed with “personal.xlsb!Module1”.

For example:

=PERSONAL.XLSB!Module1.REAL_RATE_EXACT(5%,3%)

It would be much cleaner if just the function name were required:

=REAL_RATE_EXACT(5%,3%)

Enter Lambda functions! It is used exactly the same way as a normal function (no ugly prefix).

Basic Usage

There are two ways to use a lambda function. You can use it directly in a cell or make it a named range (which allows it to be used like a function).

Example, in a cell:

=LAMBDA(a, b, a + b)(1, 2)

Example, as a named range:

Create and use a Lambda function as a named range

Create a Lambda Library

The most appropriate place to store lambda functions seems to be in the shared workbook named ‘personal.xlsb’. This workbook might not initially exist. The easiest way to create it is to record a macro and save it to this workbook.

Steps to create your ‘personal macro workbook’:

1. Show developer tab in Excel
2. Record a macro and save it to ‘Personal Macro Workbook’

Create ‘Personal Marco Workbook’ by recording a new macro

This should create a new ‘Excel Binary Workbook’ on your system. For interest, on my system it is created here:

C:\Users\USERNAME\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB

Now that your workbook exists, you can view it by ‘unhiding it’:

Unhide Personal Macro Workbook

The next step is to create a sheet and start adding your lambda functions. I use a few naming conventions. The sheet is prefixed with ‘LAMBDA_FUNCS_’. I also like to group related functions together.

Some examples include:

LAMBDA_FUNCS_FINANCIAL
LAMBDA_FUNCS_IMPROVE_EXCEL

Example sheets to group lambda functions

At a minimum, you should add the following columns: name, formula, comment.

Minimum columns

However, over time you may want to improve your functions. To help detect if you have broken the function when changing it, you could also add some columns to test it is still working.

Get Gareth Stretton’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

These would include: a column that uses the function (test), columns for input values (v1, v2, etc.), a column containing the expected value (expected) and finally a column to test if the expected value matches the function result (matches).

Unit test lambda function

At this point, we have a place to save the function. The next step is to load the function into Excel. This can be achieved with a macro.

Create a Macro to Load the Lambda Library

To add a macro, first open the visual basic editor:

Open visual basic editor

Then create a new module and paste the following code:

Code:

Sub AddAllLambdaFunctions()
    AddLambdaFunctions "LAMBDA_FUNCS_FINANCIAL"
    AddLambdaFunctions "LAMBDA_FUNCS_IMPROVE_EXCEL"
End Sub
Sub AddLambdaFunctions(sheet As String)
    Dim rng As Variant
    Set rng = Workbooks("PERSONAL.XLSB").Worksheets(sheet).Range("A1").CurrentRegion
    
    Dim iRow As Integer
    iRow = rng.CurrentRegion.Rows.Count
    
    If iRow < 2 Then Exit Sub
    
    Dim new_name, refers_to, comment As String
    For i = 2 To iRow
        new_name = rng.Cells(i, 1).Value
        refers_to = rng.Cells(i, 2).Value
        comment = rng.Cells(i, 3).Value
        
        ActiveWorkbook.Names.Add Name:=new_name, RefersToR1C1:=refers_to
        ActiveWorkbook.Names(new_name).comment = comment
    Next i
End Sub

The code has two functions. The function named ‘AddLambdaFunctions’ accepts the name of a sheet from which to load lambda function definitions. It checks if there is data beneath the heading, and if so, will create a named range lambda function from each row. The other function simply calls it with each of the sheet names from which you wish to load lambda functions.

Run the Macro

You can run the macro from the developer tab as follows:

Run Macro

At this point, all of your Lambda functions have been loaded and are usable. You can see the complete list of functions in the Name Manager.

Show loaded lambda functions

The final and optional last step is to add a button to Excel that will run the macro. I prefer doing this instead of running the macro from the developer tab.

The steps to add a macro button are explained here and demonstrated below.

How to add a button to run a macro

Sign-off

Hope this makes your life a little bit easier. Please feel free to suggest improvements to my post or provide other feedback.

… and in my exam, I didn’t end up using these lambda functions but it gave me confidence that I had something to fall back on.

Helpful links

These functions are very helpful when writing Lambda functions…

• LET — Define your own variables in a formula
• ISOMITTED — Verify user input
• SCAN — Process each cell as input
• BYCOL — Process each column as input
• BYROW—Process each row as input
• MAP — Transform each value by applying a function
• REDUCE —Accumulate by applying a function

—

Post-Publish Update

1 1salamander7 suggested on r/Excel to use the Advanced Formula Environment add-on. This is a great tool to edit and import lambda functions (from a GitHub gist or from a text file). It lets you easily use namespaces and comes with ‘intellisense’ to help write your functions. (Screenshot below)

Advanced Formula Environment

2 1salamander7 also suggested on r/Excel to use an Excel Template. When a template is opened a new document is made and the name manager retains the lambda functions. To do this, save your workbook as file type “Excel Template (*.xltx)”. In this case, it would be optional to include the sheet describing the lambda functions but they could be included as a hidden sheet.