In programming, a namespace is a grouping for procedures, methods, objects and other code that are related to each other.
We can store Excel Lambda functions in namespaces to keep them organized.
The Advanced Formula EnvironmentTo create a namespace, you will need the Advanced Formula Environment (AFE).
If you don’t have AFE, you can download and install the free add-in here.
Creating a namespace
There are two ways to create a Lambda namespace in Excel.
1: From scratch
If you haven’t changed your ribbon, after you install AFE it will be on the far right-hand side of the Home tab.
Hit the Editor button.
Hit the New button.
Enter a unique and meaningful name for your namespace. The name must not conflict with another namespace.
In the namespace I’m creating here, I plan to create some functions that will transform a vector of data, so I’ve called the namespace “transform”.
Hit Add when you’re ready. You’ll see the new namespace appears as a tab in the AFE Editor.
Now try adding a function to your new namespace.
I’ll add a function that accepts a vector as a parameter, then returns the natural logarithm of that vector. In case a user selects more than one column, the function will force the return of the transformation of just the first column (hence: TAKE).
LN = LAMBDA(vector, LET( _v, TAKE(vector, , 1), LN(_v) ) );
To synchronize this and make this function available for use in the workbook, we hit the Synchronize button:
After doing so, we can use the function in the workbook.
But why would we do this? The LN function already allows us to select a range and will apply the function to each row in the range.
There are some important benefits of saving a function in a namespace:
- All of the functions we add to this namespace can be found by typing =transform. This helps us stay organized.
- We can use function names that are already in use by Excel (such as LN). By saving them in a namespace, we avoid naming conflicts and retain meaning.
Look what happens if we save the same function outside of a namespace (i.e. in the Workbook scope).
We now have two functions to choose from when we type =LN, and we can’t tell which is Excel’s native LN function and which is our Workbook-scoped Lambda function.
Further, when we type the opening parenthesis, Excel defaults to using the native function (as can be seen by the parameter name “number”).
It gets even worse if our LN function has a different number of parameters to the native Excel function.
You can see below that I’ve added a parameter “keep_cols” to the Workbook-scoped LN function.
The Intellisense shows us two identically named functions when we type =LN, but doesn’t show us the parameter names for one of them.
If we try to enter two parameters in an attempt to tell Excel we want to use the Lambda and not the native function, it doesn’t work.
If we put our customized LN function in a namespace, we can easily have a different number of parameters.
In addition to the above benefits of using a namespace, by saving a Lambda version of a native function, we can now pass the function name into another lambda function as a parameter. To learn more about why we would want to do that, watch this.
2. Importing from a gistThe second way to create a namespace is when importing functions from a gist.
A gist refers to a page on https://gist.github.com that can be imported directly into AFE.
As an example, consider this gist.
We can import all of the code on that page in the AFE by hitting the Import button.
When we do that, we paste the gist’s URL (the address in the address bar of the browser) and we have an option to select Add formulas to new namespace? and then give the namespace a name. In this example, I’m importing the formulas to a namespace called list.
After hitting Import, I now have a new tab in the AFE Editor with all the formulas from the gist page.
After I hit Synchronize, all the new formulas are available for use in the workbook:
We saw how and why to use namespaces for Excel Lambda functions.
We saw there are two methods for creating a namespace:
- Directly in the AFE
- When importing from a gist
I hope this quick post was useful to you so you can get started using namespaces for your Excel lambda functions.