Copying Lambdas between workbooks and keeping them updated

[Dermot]

Dermot submitted a new Excel article:

Copying Lambdas between workbooks and keeping them updated - Copying Lambdas between workbooks and keeping them updated

Lambdas are held in individual workbooks. How can we copy them between workbooks, and keep them up to date if they change?

Based on the current Excel beta version 2111
* copying any sheet (even blank) from workbook A containing lambdas to another workbook B, will also copy those lambdas to B. The copied sheet can be deleted immediately after copying, ie it does not need to be retained in B.
* if the lambdas already exist in B, Excel will duplicate them, ie the Name Manager will list...

Read more about this Excel article...

 

[tboulden]

Andy Gordon and Jack Williams from Microsoft Research revealed that the LAMBDA editor add-in they've been working on should be available via the Microsoft Store sometime in the near future. Video should be available here, video should be available under the "ACCESS" section, first session, around hour 10 of the Teams recording. I've gotten to test an earlier version, but what they demo'ed today has syncing with GitHub, which would be helpful in managing LAMBDA versioning.

 

[jdellasala]

I have a macro in my PERSONAL workbook that adds my LAMBDA functions, but it's much more simple. First, every LAMBDA function starts with an underscore (_). Each function uses 3 lines of code:

ActiveWorkbook.Names.Add Name:="_LambdaFunctionName", RefersToR1C1:= _
"=LAMBDA(LambdaParameters and Functions Here)"
ActiveWorkbook.Names("_LambdaFunctionName").Comment = "Defined Name Comment Here"

The nice thing is that if a change is needed to a function, it can be done in the VBA and then updated as needed as existing functions will be overwritten with the updated version.