LAMBDA Recursion

This is for technical professionals (sorry) who, when it comes to LAMBDA, are not on Sergei Baklan or Charles Roldan's level (that includes me) but would like to catch up - as best we can.

Those who follow my work know I am trying to bring dynamic arrays (DAs) to financial modeling (FM). DA's in FM require LAMBDA. Most financial modelers do not want to learn LAMBDA. 5G was started to enable DAs in FM without knowing LAMBDA. We accomplish this by creating user defined functions (UDFs) with LAMBDA that look and feel like native Excel functions. And this is where I ran into a problem when it comes to recursion. My first recursive LAMBDAs did not look or feel like native Excel functions. Here was what they looked like (not really necessary to view for this discussion).

If you watched the video you would have seen the function's arguments and one of those arguments, AverageDebt, should not be seen by users. Here is the code (Prototype appended to function name):

AverageDebt must be passed to the second and all subsequent recursive calls but must be omitted when first called. That's a problem since 5G is meant for use by non-technical Excel users, including novices. If they see something they can change, they will, bad things will happen, and they will blame me, not themselves, for the frustration that ensues. We need to prevent that.

How to get rid of arguments users shouldn't see?

An update came in my inbox from a blog I monitor (Microsoft's Excel Blog). The update posted by Sergei and Charles had what I needed. Sergei called it anonymous recursion. It is a way to wrap our routine inside another routine, and, if needed, add arguments. After applying their technique, my function now looks like this:

The offending AverageDebt is gone from the new function. After the arguments are declared, our new function starts by naming a LET() step: Recursion. The name is not important. But what it defines is. In Recursion's LET() step we see our original prototype embedded in another LAMBDA() function with the offending AverageDebt. But that's okay because the users will not see it and, thus, cannot mess things up.

Following Sergei's convention, I renamed my prototype function: fn (for function) and, within the new LAMBDA, replaced all occurrences of my prototype's name with fn. Again, the name is not important. What is important is we now have our original LAMBDA named internally as Recursion and we can call that LET() step like any other LET() step and it will call itself recursively as needed.

Summary

If you are like me, you find this difficult to wrap your head around. And that is why I'm posting it here - so I will remember how it is done. Fortunately, the steps to make this work are simple.

1. Create a prototype of your recursive function with the arguments that are necessary for recursion.
2. Test it (see video).
3. Copy the prototype function starting with the first argument and all the way to the end.
4. Create another LAMBDA with just the arguments we want users to interact with.
5. Add a LET() step name followed by ", LAMBDA( fn, " and paste what we copied from our prototype.
6. Change all occurrences of our prototype's name to fn.
7. Add a LET() step to call the named step (Recursion in this example) and return that result.