What it does
Uses an auto-complete search box to find any Lambda custom functions you’ve saved to your library.  It displays the date last updated, the Lambda formula, and any comments as you browse through the different saved Lambda formulas.  Once you find the one you want, click Enter to add it to your active workbook (or add multiple from the same group).  You can optionally have any new Lambda formulas saved to your library automatically when you save your workbooks.

When to use it
When you want to reuse one or more Lambda custom functions that have been saved to your library.

Why to use it
Lambda is a great way to simplify formulas and make them highly reusable.  This macro (and the related Bulk Edit Lambda Formulas macro) allow you to easily save your Lambda custom functions to a central library, where they are easy to access, reuse, and share with colleagues.

Default shortcut
None

Other Details

• Category: Formulas / General
• Difficult: 2/5
• Usage/frequency: 3/5
• Automation factor: 4/5 (estimated 30 seconds saved each time used)
• Type: Shortcut
• Date added: 12/24/2024
• Tags: Lambda, custom, formulas, functions, search, bulk

Related Macros
Formula Picker
Bulk Edit Lambda Formulas

Other Articles
None

None

Prerequisites
Add Lambda custom functions to your workbooks.  If enabled, they will automatically be added to your library when you save files (see below on how to toggle this).  Alternatively, you can add them to your library in bulk with the Bulk Edit Lambda Formulas macro (see below for instructions on the bulk edit approach).

Instructions – Search Lambda Formulas
With Lambda functions in your library, run the Search Lambda Formulas macro.  It will show you an auto-complete search box, and as you type, it will display Lambda functions you can add from your library, displaying the date last updated, the formula, and any comments as you browse through the Lambda functions matching your search.

With the full name of the Lambda function displayed in the search box, click the Enter button.  It will be added to your active workbook.  If you set group names for your Lambdas, you can optionally add all Lambda functions with the same group at the same time – just check the box that says “Add all Lambdas in group.”

By default, when you install the XLEV8 Excel Add-in, each Lambda function will be saved to your library when you save your workbooks.  You can toggle this on and off using the checkbox on the search box.  You can remove a record or all records using the buttons on the right side of the search box.  You can also launch the bulk edit form using the bottom button on the right.

Instructions – Bulk Edit Lambda Formulas
To manage your Lambda function library, where you can add, edit, delete, review, or share the library (or add Lambda functions to your workbook from it), run the Bulk Edit Lambda Formulas macro.  It looks for the Bulk_Edit_Lambda_Formulas sheet within your active workbook.  If not found, it is added.  Any existing Lambda functions in your library will be displayed.  Make any edits to the columns described below, then run the Bulk Edit Lambda Formulas macro again to process your contents and save the Lambda functions in the sheet to your library.  These are the columns within the Bulk_Edit_Lambda_Formulas sheet to review or fill in:

• Column B – Date Updated (Optional): to document the date you built or last updated a Lambda function, enter a date in this column.
• Column C – Name (Required): give each Lambda a unique name to help search for them and tell them apart.
• Column D – Formula (Required): enter the Lambda formula you want to save, starting with an apostrophe then “=LAMBDA(“. For troubleshooting – especially with complex formulas – it’s often best to test them by typing them out in the place you intend to use them.
• Column E – Comment (Optional): include comments help add documentation and context to your Lambda formulas for future reference.
• Column F – Group (Optional): enter a group name to organize your Lambda formulas and enable you to add all Lambdas from a group to your workbook at one time with the Search Lambda Formulas box.
• Column G – Add to Workbook (Optional): to add new or existing saved Lambda formulas to the active workbook, select Add in this column and they will be added to this workbook if not already included.

TBD…