TABLE OF CONTENTS
Using =eval to Call a Lambda with Fixed Parameters
Use =apply to Call a Lambda with variable parameters
Experiment with a Lambda definition in the REPL
Switching between a Lambda and a Named Function using CTRL-SHIFT-X
Create an Excel Name for a Definition
Now let’s explore the user interface features more thoroughly and from a task-oriented point of view.
We will start with a few basics: What is a “Lambda”? and The Lambda Functions.
What is a “Lambda”?
All programming languages in use today, including Excel’s formula expression language, are based on a branch of mathematics called the “lambda calculus.” Simply stated, lambdas are custom functions which receive input and generate an output - just like normal Excel functions. Microsoft’s newest version of Excel is going to make this more explicit with a new function called =LAMBDA(...), to make it possible to write user-defined functions “without programming” by using the familiar Excel formula expression language. LAMBDA makes the formula expression language of Excel “almost Turing complete” which means, in effect, it is now a first-class programming language, albeit still domain-specific.
While our support for “lambda” expressions compliments Microsoft’s exposure of this concept to Excel users, it involves integrating a truly Turing-complete language built explicitly on top of lambda calculus into the Excel environment. The language we integrated into Excel is, as we have mentioned before, called IronScheme, a relatively new member of the venerable Lisp family of languages that runs on top of .NET. We expose the full power of that language through four functions that give you a lot of flexibility in terms of where you save your logic.
It is important to take a moment to understand just how valuable adding a proper Scheme based on .NET into Excel in this manner really is. Bringing this powerful, first-class language to your fingertips in a manner that can access and extend the full .NET Framework gives you access to the vast .NET ecosystem of libraries, not previously available in Excel without writing your own XLL libraries.
The Lambda Functions
The four Lambda Functions provided by AcceλerateTM for Microsoft 365 that eliminate the need ever again to write XLLs and provide vastly more power than writing custom functions in VBA are as follows :
=λ(“<lambda-expression>” [,a1,a2,...,aN])
Given a lambda expression and zero or more arguments (as determined by the arity of the expression, and not to exceed 60 in number), return a value to the current cell.
Functions that can take a variable number of parameters are referred to as variadic functions. There are a handful of Excel built-in functions that are variadic in this sense (e.g, SUM), and VBA custom functions can be written in a way to permit optional arguments in a similar sense as well.
=define(“<form-name>”,”<lambda-expression>”[,a1,a2,...,aN])
Given a name for the form in quotation marks, a lambda expression, and zero or more arguments (as determined by the arity of the expression, but not to exceed 60 in number), return a value to the current cell.
In most cases, unless the form itself returns a lambda function or you pass in explicit parameters when defining it, this value will be a representation of the defined form in the following format:
lambda:<form-name>/<form-arity>@<hash-code>
The form name and arity are useful confirmation that your form got the name you requested, and has the number of parameters you expect based on the expression you entered. The hash code is simply how we tell Excel you changed the code whenever you need to edit or modify it. Whenever you modify the code, the hash will change, thus triggering Excel’s engine to recompute any cells with a named or relative reference to the cell in which the form is defined. However, for all intents and purposes, you can ignore the hash code. Refer to the Tutorial for practice using this function.
=eval(“<form-name-or-lambda>”[,a1,a2,...,aN])
Given either the name of a defined form in quotation marks (or the Range name as defined using Excel’s “Define Name” functionality that refers to the cell where the form is defined), and zero or more arguments (as determined by the arity of the expression, and not to exceed 60 in number), return a value to the current cell.
=apply(“<form-name-or-lambda>”,<range-or-array>)
Identical to =eval except the 2nd and only other allowed argument must be a range or array that provides all the arguments to the named form or lambda expression, which is understood to be variadic. Importantly, and unlike =eval, the lambda must have an arity of 1 (meaning it takes only one parameter), and that parameter must be a list in the Scheme sense. Thus, you can pass in either a reference or a literal array as values to that one parameter. We convert ranges and literal arrays to the list form needed by a function intended for use with =apply.
The manner in which we use these functions are not quite the same as how they are used in traditional Scheme or Lisp. Our goal with =apply is to provide some user guard rails around evaluating both named and unnamed lambda expressions. It’s basically like the =λ function, except it can also alternatively take the name of a form that has been defined previously and call it.
These Lambda Functions are the only Excel functions you’ll ever need to extend your spreadsheet with powerful new computational capabilities. The following section provides an overview of how to create and use these functions. For more in depth, practical examples, please refer to our tutorials on Creating Reusable Solutions and How to Understand and Use the =apply Function.
Using the Lambda Functions
How to Create a Lambda
To create a lambda expression, start by clicking on the Acceλerate tab in the Ribbon, then the λ Lambda button. A drop down menu appears with four choices: Insert Lambda, Define a Named Lambda, Apply a Region to a Lambda, and Evaluate a Named Lambda. Choose, Insert Lambda:
The Lambda Editor box will appear on the right side of your screen, prefilled in the Text Editor field with a standard Lambda example. In this example, we have automatically populated a snippet of code, =λ("(lambda () (ultimate-answer))"). As you can see, the Result of our suggested lambda expression - 42 - appears in both the Lambda Editor and in your spreadsheet, however any further changes that you make will not be made until you have accepted the result in the Lambda Editor. This allows you to test the formula before making it a part of your spreadsheet, thereby reducing the risk of your formula creating issues throughout your spreadsheet or workbook. Press CTRL-SHIFT-T to exit the Text Editor section and update the formula preview. Once you are happy that your lambda expression provides the result you expected, click the Accept button at the bottom of the Lambda Editor. You will see a lambda expression in the formula bar for that cell, while the answer to the formula appears in the cell. We have set this “boilerplate” lambda expression as a default when you open the Lambda Editor to assist you in writing your own lambda expressions going forward.
For a more specific definition of what a lambda expression is, please refer to the The Lambda Functions section of this document. To see this Insert Lambda Function capability used in a practice, refer to our tutorials on Creating Reusable Solutions and How to Understand and Use the =apply Function.
How to Create a Definition
You may find that the lambda expression you created is a function that you will want to reuse throughout your workbook. You will use the Lambda Function =define to achieve this task. By using =define, your lambda expression will be stored in your local database library for use only in this workbook. They cannot be accessed in other workbooks without taking additional steps to make them available outside the workbook in which they are defined. These steps are highlighted in our tutorials on Creating Reusable Solutions and How to Understand and Use the =apply Function.
It is important to note that to be able to reuse a lambda expression in other lambda expressions, you must provide it a Scheme name. This is different from an Excel name. Scheme names are available within Scheme code and can be used to call that defined lambda expression into another lambda expression, or in the REPL. However, a Scheme name cannot trigger downstream computation when, for example, you change the code of the lambda expression. To do that, you’ll need to also give the function an Excel name, which will be bound to the cell in which it’s defined.
Excel defined names are typically used when you want to trigger change in downstream =eval or =apply invocations. You give a defined lambda an Excel name, and refer to that in the downstream invocation inputs, when you expect to tweak the code and want to be sure that those downstream consumers of the named lambda update as soon as the code changes. Recall, a change in the code will result in a change to the hash code associated with your definition. This in turn will trigger reapplication of the inputs to downstream =eval and =apply invocations that refer to the Excel name instead of the Scheme name.
In short, if you are calling functions from stable libraries that rarely change, you’ll want to use the Scheme name when calling =apply and =eval. Otherwise, it’s better to use the Excel name, at least until you promote your definition to a library file—a process we describe in our tutorial.
To define a lambda expression with a name that can be called from other lambda expressions (a Scheme name), start by clicking on the Acceλerate tab in the Ribbon, then the λ Lambda button. A drop down menu appears with four choices: Insert Lambda, Define a Named Lambda, Apply a Region to a Lambda, and Evaluate a Named Lambda. Choose, Define a Named Lambda, as shown below:
Again, the Lambda Editor appears on the right side of your screen, with the same sample code used in the Create a Lambda section of this document. Using the sample code example, you will see the function =define has already been chosen (Fn: define). Next to the Fn field, you will see a field Form, and in this example the name we gave this lambda - “deep-thought” - appears. This is the Scheme name, which can be used in other lambda expressions and Scheme code.
Looking at the formula bar to the related cell, you will see the formula =define("deep-thought","(lambda () (ultimate-answer))"), which is the defined lambda expression in your Lambda Editor. In the cell, you will see the same Result as that indicated in the Lambda Arguments box.
Try to edit this example with a different formula in the Text Editor. Then when you have finished your changes, press CTRL-SHIFT-T. When you look in the Lambda Arguments section of the Lambda Editor, you will see the name you assigned next to the arguments input field, as well as what the Result will be when you accept the lambda. Then press the Accept button, to save your defined lambda expression, and see the new Result in the related cell.
Now, we also need to define an Excel name. To do this, go to the Formulas tab, and click Define Name.
The Define Name box appears. Enter the Excel name you wish to give your lambda expression, and click Save.
For a more specific definition of what a lambda expression is, please refer to the The Lambda Functions section. To see this Lambda Function used in a practice, refer to our tutorials on Creating Reusable Solutions and How to Understand and Use the =apply Function.
Using =eval to Call a Lambda with Fixed Parameters
Next we will look at how to use the =eval Lambda Function to call a lambda with fixed parameters. To start, go to the λ Lambda button on the Acceλerate tab from the Ribbon, and choose Evaluate a Named Lambda.
The Lambda Editor box will appear on the right side of your screen, once again prefilled in the Text Editor field with a standard =eval example. In the Lambda Editor, you will see the Fn field shows “eval”. You can type your Lambda expression in the text editor, then press CTRL-SHIFT-T to move down to the Lambda Arguments section:
Look over in the cell, and you will see your formula in the formula bar. Highlight and copy the expression between the quotation marks:
and in this example I have replaced it with “my-answer”, which is the defined lambda expression we created above. You will see the result is the same as it was for "(lambda() (ultimate-answer))". You have successfully evaluated the defined lambda expression my-answer.
For a more specific definition of what a lambda expression is, please refer to the The Lambda Functions section. To see this Lambda Function used in a practice, refer to our tutorials on Creating Reusable Solutions and How to Understand and Use the =apply Function.
Use =apply to Call a Lambda with variable parameters
You can also call a lambda with variable parameters—that is, zero or more. Functions in Scheme that take zero or more functions are referred to as variadic functions. The syntax for defining a variadic function in Scheme is slightly different from the normal syntax for creating functions with a fixed number of parameters; but usually you’ll be using =apply with standard variadic functions already defined in Scheme. You do this by using the =apply Lambda Function.
To use the =apply Lambda Function, go to the λ Lambda button on the Acceλerate tab from the Ribbon, and choose Apply a Region to a Lambda:
In the Lambda Editor, the Fn field should say “apply”. You will notice that in the Form field is listed “+”. That’s the name of Scheme’s standard “plus” function, which is variadic: with zero inputs it returns zero; otherwise it adds all the parameters up and returns their sum, just like the standard SUM function in Excel. This has been set as the default function to assist you in using the =apply function.
Now tab through to move to the Lambda Arguments section. In the arguments field, enter the variables you wish to apply. In this case, we have pre-populated this with “{1,2,3,4}”. By providing this input in what is known as Excel’s “literal array syntax,” we are saying to apply the function “+” to the values 1, 2, 3, 4, where you get a result that equals 1+2+3+4, or 10.
For a more specific definition of what a lambda expression is, please refer to the The Lambda Functions section. To see this Lambda Function used in a practice, refer to our tutorials on Creating Reusable Solutions and How to Understand and Use the =apply Function.
Experiment with a Lambda definition in the REPL
A feature we have provided in AcceλerateTM for Microsoft 365 is the use of a REPL - otherwise known as a “Read-Evaluate-Print-Loop”. The REPL lets you experiment with your formulas before implementing them into your spreadsheet or workbook. Here is an example of how you can use the REPL.
First, from the Acceλerate tab, choose the REPL button. Three options show up in the drop down menu: Show/Hide REPL Pane; Open ADB REPL in Windows Terminal; and Open Windows Terminal (see the note related to the use of Open Windows Terminal in the Known Limitations and Issues). As these choices indicate, you can use the REPL in multiple ways - right there in the spreadsheet, or via the Windows Terminal for those who prefer more of a command line look/feel. For this example, we will work within the REPL Pane, so choose Show/Hide REPL Pane.
The REPL pane appears to the right of your screen.
Follow the instructions in the first box - enter code in the editor. In this example, we entered (ultimate-answer) . Press CTRL-SHIFT-T.
As you can see, the results to your code appears in the REPL as [book1] => 42
The REPL operates in sort of a “Request/Response” fashion that allows you to try out different expressions, test the value of symbols, and otherwise iterate toward a solution in a “bottom up” fashion that is popular with dynamic scripting languages like Scheme. For a more detailed walk through of “REPL-driven” development using Acceλerate for Microsoft 365, see our Advanced Topics, particularly the section on integrating with .NET/CLR frameworks.
Switching between a Lambda and a Named Function using CTRL-SHIFT-X
Both =apply and =eval functions allow you the option of either calling a named lambda (defined in another cell in the spreadsheet or in a loaded library), or calling an in-place unnamed lambda expression, which you define in the Lambda Code Editor in the same way as when you use the =λ function to do it. The boilerplate =eval function uses “inline lambda” mode; whereas the boilerplate =apply function calls a named function with canned inputs.
However, you can decide to swap to the opposite mode using either the keyboard shortcut CTRL-SHIFT-X from inside the lambda code editor or the Fn: dropdown box, or by using the “swap mode” button between the Fn: dropdown and the Cell: text box.
Create an Excel Name for a Definition
Excel has its own concept of naming cells and regions to be able to refer to them by name instead of by cell reference. We can leverage that ability when we define named lambdas that we consider “volatile” — that is, subject to frequent change while we’re still working on our spreadsheet model. By also giving them an Excel name, bound to their physical cell location, and referring to them by their Excel name in =apply and =eval invocations, we take advantage of Excel’s change propagation engine each time we modify the code in a defined lambda. This is the only real advantage of also adding an Excel name; however, it’s an important advantage.
The name we give a lambda as part of the =define function, as we explained earlier in Create a Definition, is its Scheme name - that is, the name by which it can be invoked from within Scheme code, either in another lambda expression or in the REPL. Scheme names are appropriate when you are calling Create a Definition (Lambda) [Insert define, Lambda Editor] apply or eval on built-in or custom names that have been moved to or provided by external Scheme libraries, and you don’t expect them to change very often, if ever.
The dual names may sound confusing, but when you understand their practical uses, and follow simple conventions, you’ll intuitively know when to use which name.
The following terms are registered trademarks of the Microsoft group of companies and are used in accordance with Microsoft’s Trade and Brand Guidelines: Microsoft, Microsoft 365, Microsoft Office, Microsoft Excel, Microsoft Edge, Microsoft Edge WebView2, Microsoft Windows, Excel, Office 365
Copyright © 2022. Apex Data Solutions, LLC. All Rights Reserved.