Best practice for the use of LAMBDA and GET-CodePudding

I am a VBA programmer - trying to follow KISS (keep it simple and small), DRY (don't repeat yourself) and "One task per function/sub".

I am not a formula hero but have a good intermediate knowledge of the basic functions. I definitly don't like those formulas with multiple nested levels. To me they are - most of the time - neither very robust nor maintainable.

But now - with LAMBDA and LET - I think I should change my mind.

Chris neilsen gave me a great answer on how to use LAMBDA/LET to create the missing UNION-function (https://stackoverflow.com/a/69861437/16578424).

I am wondering

How would I share such a generic LAMBDA-function over different projects?
How would a LAMBDA-library might look like? (Like a code snippet library)
What kind of generic LAMBDA functions will make sense - when does it get to complicated again?
How could patterns like KISS, DRY, good naming convention etc. be applied to LAMBDA functions.
…

I know, this is not exactly a classic programming question - but somehow "creating" a LAMBDA function comes very close to programming.

CodePudding user response：

I want to answer your first question:

How would I share such a generic function over different projects?

You can put your VBA code into a single blank spreadsheet and save it as an EXCEL add-in (.XLAM) file. Then go into File..Options..Add-Ins and install that .XLAM file into your EXCEL.

Now, every time you start up Excel your VBA code will be automatically imported and available. (I did this with the Regex wrapper functions to enable Regexes as user functions, now I can write regexes in my formulas.)

This solves the problem of how you can use it in future projects, but if you want to share with others you will still need to distribute your. XLAM file and get them to install as add-in too.

Note that this does not address the use of LAMBDA functions in EXCEL, just VBA code, but there's a lot you could do with VBA wrappers...

CodePudding user response：

I'm a VBA programmer too, but I am finding lambda functions quite exciting.

Library - I would keep a spreadsheet with all your reusable lambdas in it

Generic lambdas - would be those that are useful because they aren't too specific, and because you will use them a lot

Complexity - this is a balancing act, between compressing a lot of functionality into a single formula, and totally mystifying anyone who needs to change it later. One way to manage this is to nest lambdas, creating one for each part of a calculation and then creating one to rule (I mean combine - couldn't resist that reference) them all. Then at least the user can make sense of each lambda and test them separately if necessary

Sharing - I read somewhere that the developers are working on a way to share lambdas, but I haven't seen anything yet. In the meantime, sharing lambdas from workbook A to workbook B is as simple as copying any sheet (even blank) from workbook A to B (and then deleting it if you wish).

There is one nasty drawback - if the lambda already exists in B, Excel will add A's version after the existing one, so you will have two identical lambdas. Workbook B will use the first one in its calculations, which will be the one it already had. So if you ever want to share a change or correction to a lambda, sheet copying will not do it. My code below instead loops through all the lambdas to be copied, and if they exist in the target workbook, deletes them.

I've done quite a lot of work on lambdas and am happy to discuss further.

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