• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk. If you have any questions regarding an article, please use the Article Discussion section.
Dermot

Copying Lambdas between workbooks and keeping them updated

Excel Version
  1. 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.

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
Author
Dermot
Views
2,727
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from Dermot

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top