Lambda function

xlsgen > overview > Lambda function

Introducing the lambda function

Lambda function is a mechanism in Excel that is meant to reduce the complexity of spreadsheets. It was introduced in 2021 as part of Office 365 subscriptions, is not available in any downloadable Excel version, but xlsgen supports it.

Usually, cell with calculations end up with multiple functions nested at multiple levels, making it hard to understand and maintain over time. The fix for this is usually to either factorize a static piece of it into a defined name, or to externalize it to a VBA macro function, both of which have limitations. The limitation of static defined names is that parameters can't be passed to the defined name so a dynamic call is impossible. A limitation which isn't in VBA macros, except that VBA is a different thing than Excel functions, a different language and that it's been years that running VBA macros has become a security problem.

So this lays down the path for something else, and that's lambda functions, that's how it is named in Excel. Lambda functions can be seen as a mechanism for overcoming the limit of not being able to pass arguments to defined names, while remaining in the Excel function context.

Introducing lambda functions is actually straight forward. Let's assume we have a mathematical function y = x + 3. We could specify this function like this as well : x ==> x + 3, or rather function(x; x+3). Back to Excel context, if we create a defined name that we shall call y, and whose definition is =lambda(x;x+3), that's all we need then to make a call anywhere in the spreadsheet of the form y(1) and it would return 4.

When we create that defined name, we can use the description to explain further details about what the function accomplishes, what the parameters are, etc. which carries a meaning to anyone who will be tasked to understand and maintain the spreadsheet over time.

Anywhere in the spreadsheet where this x + 3 calculation is stored in formulas, it can be replaced by the y(x) function call and doing so this removes complexity to formulas across the entire spreadsheet.

Here is another example. Assuming we have data layed down like this :

In C12, we have a calculation whose only point is to obtain the price of a coffee type of a given size. Allegedly, all what we want for this is a handy function of the form CalcCoffeePriceForSize(coffee_type; coffee_size), but in Excel we usually end up with a string of functions like this :

=VLOOKUP(A12;Table1_data;MATCH(B12;Table1_headers;0);FALSE)

where A12 is the coffee type and B12 the coffee size. The MATCH function is there to find the appropriate coffee size column, and the VLOOKUP finds the coffee price for the coffee type given its coffee size.

A string of Excel functions like this isn't particularly self-describing, and it would get far worse if we added error handling, etc.

And that's where the lambda function mechanism comes handy. Let's create a new defined name, called CalcCoffeePriceForSize, then paste the formula above in there.

Then edit this definition and replace it by :

=VLOOKUP(coffee_type;Table1_data;MATCH(coffee_size;Table1_headers;0);FALSE)

Then edit again and prefix the above with the =LAMBDA function call along with parameters :

=LAMBDA(coffee_type;coffee_size;VLOOKUP(coffee_type;Table1_data;MATCH(coffee_size;Table1_headers;0);FALSE))

Don't forget to describe this function and parameters in the Comment area. And click OK. Of course if you are doing this in Excel, this won't work unless a subscription-based Excel product is being used. In other words, it does not work with Excel 2016, Excel 2019 or Excel 2021.

What is being done here is pass the list of all function parameters, and then we have the calculation itself.

Now back in cell C12 where the calculation occurs, replace the previous string of formulas with :

=CalcCoffeePriceForSize(A12;B12)

That's it. Cell C12 is a calculated formula and the intention is self-describing. If the user needs to know more about the actual calculation, it's available, but not shown unless needed. Complexity has been lowered without compromising the maintainability of the spreadsheet.

So that's a brief introduction to lambda functions.

in xlsgen, creating the lambda function is done like this :

C++ code


xlsgen::IXlsWorksheetPtr worksheet = workbook->WorksheetByIndex[1];

// create a lambda function
xlsgen::IXlsDynamicRangePtr dynrange = worksheet->NewDynamicRange(L"CalcCoffeePriceForSize");
dynrange->Formula = L"=LAMBDA(coffee_type;coffee_size;VLOOKUP(coffee_type;Table1_data;MATCH(coffee_size;Table1_headers;0);FALSE))";
dynrange->UserDefinedFunction = TRUE;
dynrange->Description = L"CalcCoffeePriceForSize computes the price of a coffee for a given coffee type and a given coffee size.";

// use the lambda function
worksheet->Formula[12][3] = L"=CalcCoffeePriceForSize(A12;B12)";

Other lambda functions

Office 365 introduces more than the LAMBDA() function :

BYROW()	Applies a Lambda function to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
BYCOL()	Applies a Lambda function to each column and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 3 columns by 1 row.
MAKEARRAY()	Returns a calculated array of a specified row and column size, by applying a Lambda function.
SCAN()	Scans an array by applying a Lambda function to each value and returns an array that has each intermediate value.
MAP()	Returns an array formed by mapping each value in the array(s) to a new value by applying a Lambda function to create a new value.
REDUCE()	Reduces an array to an accumulated value by applying a Lambda function to each value and returning the total value in the accumulator.
ISOMITTED()	Checks whether the value in a Lambda function is missing and returns TRUE or FALSE.

Those functions basically iterate over the cells in a particular way, either cell by cell, row by row, and so on, and what they do is apply the lambda function on each cell value. The result is either written back to a new cell, with most functions above, or accumulated, as in functions SCAN() and REDUCE().

Example : =BYROW(A1:C2; LAMBDA(x;MAX(x)) )

Example : =BYCOL(A1:C2; LAMBDA(x;MAX(x)) )

Example : =MAKEARRAY(2; 3; LAMBDA(r; c; r*c) )

Example : =SCAN(""; A1:C2; LAMBDA(t; u; t&u) ). t is an accumulator with an initial value of "".

Example : =MAP(A1:C2; LAMBDA(a; IF(a>4;a*a;+a) ))

Example : =REDUCE(0; A1:C2; LAMBDA(t; u; IF(u>4; t+u; 0)) ). t is an accumulator with an initial value of 0.

As for the ISOMITTED() function, it works like this :

C++ code


xlsgen::IXlsWorksheetPtr worksheet = workbook->WorksheetByIndex[1];

// create a lambda function
xlsgen::IXlsDynamicRangePtr dynrange_xy = worksheet->NewDynamicRange(L"MyFuncXY");
dynrange_xy->Formula = L"=LAMBDA(x;y;IF(ISOMITTED(y);\"y param is omitted\";x&y))";
dynrange_xy->UserDefinedFunction = TRUE;
dynrange_xy->Description = L"(x,y) function";

worksheet->Formula[10][6] = L"=MyFuncXY(4;2)";
_bstr_t s106 = wksht->Label[10][6]; // returns "42"

worksheet->Formula[11][6] = L"=MyFuncXY(4)";
_bstr_t s116 = wksht->Label[11][6]; // returns "y param is omitted"