- Excel Version
- 365
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 two of each
* when there are duplicates, Excel appears to apply the original lambdas and not those copied across
This obviously causes a problem if you want to replace lambdas in an existing workbook
The solution seems to be to delete any existing lambdas (that would otherwise be duplicated) before copying the worksheet. It seems that any existing formulae using those lambdas will update correctly to use the replacement lambda
My approach (so far) is to have a workbook with the master lambdas, including a reference sheet which lists them and how to use them. The macro below will copy this sheet (and the lambdas) into all other open workbooks, adding and replacing lambdas as applicable.
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 two of each
* when there are duplicates, Excel appears to apply the original lambdas and not those copied across
This obviously causes a problem if you want to replace lambdas in an existing workbook
The solution seems to be to delete any existing lambdas (that would otherwise be duplicated) before copying the worksheet. It seems that any existing formulae using those lambdas will update correctly to use the replacement lambda
My approach (so far) is to have a workbook with the master lambdas, including a reference sheet which lists them and how to use them. The macro below will copy this sheet (and the lambdas) into all other open workbooks, adding and replacing lambdas as applicable.
VBA Code:
Sub CopyLambdas()
Dim wb As Workbook, n, List
'make a concatenated list of lambdas in this workbook
List = "|" 'delimiter is |
For Each n In ThisWorkbook.Names
If InStr(1, n.Value, "lambda", vbTextCompare) > 0 Then
List = List & n.Name & "|"
End If
Next n
'process all open workbooks (except this one of course)
For Each wb In Workbooks
If Not wb Is ThisWorkbook Then
With wb
For Each n In .Names 'look for lambdas
If InStr(1, n.Value, "lambda", vbTextCompare) > 0 Then
'if this lambda has a name that's in our list, delete it
If InStr(1, "|" & n.Name & "|", n.Name, vbTextCompare) > 0 Then n.Delete
End If
Next n
ThisWorkbook.Sheets("Lambdas").Copy After:=.Sheets(.Sheets.Count)
End With
End If
Next wb
End Sub
