Copying Excel Lambda functions between workbooks
There is some complication around what happens if worksheets are copied across from one workbook to another, and an identically named Lambda function already exists in the target workbook.
- If you copy any worksheet from a workbook containing Lambdas, the Lambdas are copied across to the second workbook. The copied worksheet can be blank, it doesn’t need to have the Lambda called (used in a cell) on the worksheet.
- The outcome then depends on whether or not the second workbook contains a duplicate copy of the same Lambda function:
- No duplicates: if there are no duplicates, the copied Lambdas will be added, maintaining the scope that they were created with (if created with worksheet only scope, that setting will remain).
- Identical duplicates: if Lambdas with the same name already exist in the second workbook, compiled with the same formula, duplicate copies of the imported Lambdas will be created. In this scenario, the scope of the imported Lambda is restricted to the worksheet that it was imported with, even if created originally with workbook scope. (Surprisingly, Excel keeps the names identical too which creates potential confusion!)
- Duplicates with the same name but different formula: if an identical Lambda is already in the second workbook, but an amended/updated copy is imported from the first workbook:
- New instances of the imported copies of the Lambda function can only be created on the imported worksheet, even if they were created in the first workbook with workbook scope (they won’t be available on another worksheet to select from the functions list when you start typing after ‘=’).
- Instances of the second workbook’s existing version of the Lambda function will keep using that version of the formula, not the formula that’s imported in the duplicate copy. So you can’t update Lambda functions simply by copying across new versions from another workbook.
- Both copies of the function can be loaded into the imported worksheet, and the tooltip will show the scope of each function.
IMPORTANT! If the imported worksheet is deleted, the imported copy of the function will also be deleted from the workbook.- On worksheets other than the imported worksheet, only the Lambda functions created in the second workbook will be available. You’ll also notice a small thing, that the tooltip won’t appear for a duplicate function (presumably because there are two copies of the function saved in the Name Manager, and therefore two potential comments loaded; the comments text is used in the function tooltip).
Keep Excel Lambda functions in a separate blank workbook
If you’re going to use Lambda functions regularly then it’s good practice (based on the section above) to keep the latest versions of your functions in a separate blank workbook and create new workbooks from this template.
There are also helpful solutions such as this one linked below, to create a macro which copies a sheet and its Lambdas into all other open workbooks, adding and replacing lambdas as applicable: https://stackoverflow.com/questions/69872165/how-to-share-generic-lambda-functions-over-different-projects
Comment: it does seem odd that Excel creates two versions of a function with identical names … no wonder there are unexpected and unwanted side effects!
Related topics
- Python Lambda functions
- More from Microsoft support on Excel Lambda functions