Strategies for managing lambda functions

Congrats from my side, too. Important question/issue as soon as your lambdas become more complicated,

i.e.,, longer than 1 line of code. For example, in my current project I have 2 or 3 lambdas that are 3-4 times

larger than your example. It is quite easy to get lost in such formulas leading to syntactic errors caught by

the lambda engine but not always with detailed enough feedback to enable you to quickly recover from

them. So just putting the final lambda expression (including a lot of not-so-easy let's) into your document

is not enough for future users (including yourself) who need to understand the lambda function and perhaps

want to adapt it to their (or yours) different context. My experience with some of the add-in formula editors

(even Microsoft's own Advanced Formula Editor) is not quite positive. In particular, they are not helpful for

the strategy that you propose here, because they don't provide a way to export the structured lambda's.

In my case, i.e., rather long lambda expressions with a lot of in-formula LET functions, I decided to use MS

WORD to get the sort of structured expressions to keep track of the numerous parameters and variables in

the lambda function. This is clearly suboptimal. I guess I will follow your advice and put all that information

in the Excel workbooks themselves, so that those functions can be easily transferred to other workbooks

and the power of Excel may be used to swap between structured renderings of lambda's and pure-string

lambda's, i.e., those that will eventually be saved in the Name Manager.

Here is an example from my project's catalogue of "large lambda's" in WORD, first the structured version

and then the pure-string version:

In WORD, one must apply multiple REPLACE commands to get the pure-string rendering. That could be automated

by a single VBA script, or, who knows, by a (large) lambda function. The reverse way would be much more complicated,

probably would still involve some improvements by hand.

Initially, writing the structured _SYNCH function directly in WORD, one would use the tab stops, but that turns out to be

quite clumsy. In Excel, it would be easier, using distinct columns to imitate the tabs. The "***" comments can be moved

to another column to the right. Beware, that the same function can usually be realized in different ways. The challenge

is, to find a "factoring" of the function such that the LET variables can be defined on a single line, and they build up one

after the other, so that the final expression (here: SWITCH) uses a minimal number of LET variables.

Paulus,

Your first point:

So just putting the final lambda expression (including a lot of not-so-easy let's) into your document is not enough for future users (including yourself) who need to understand the lambda function and perhaps want to adapt it to their (or yours) different context.

does a really nice job of stating the reason for why I posted this as a discussion.

Like you, I too recall trying the Advanced Formula Environment with a similar reaction. I have not investigated if that team has done any further development.

Using MS Word to document expressions seems like a viable and natural way to approach the problem. I like the way you use the LET function alias the meaningful names of the inputs with more compact variables for the calculations.

Thanks for sharing your insights.