Forum Discussion
Recursive Lambda: Unpacking LET function
Wow, that is a stunning effort! Perhaps the Microsoft Research Team might like to take a look at this to see where their 'baby' is being taken!
LAMBDA: The ultimate Excel worksheet function - Microsoft Research
I wasn't really serious in suggesting that the task should be performed, simply making the point that it could be performed. I was simply expressing the idea that reducing a LET function to traditional spreadsheet formulae (that I know and loath) is a purely mechanistic process. I had thought of using Lambda functions and even taken a step or two in that direction. After all, if Lambda functions make Excel Turing-complete and, given that functional programming is well-suited to the task of parsing data for compiler writing, I thought it should be possible.
The thoughts that led me to suggest Python were that I knew that Lori uses Python more than Excel and the development environment should be more productive. I did have one advantage over you, namely I use Charles Williams's FastExcel which includes regular expression support, so I was able to search for strings that were bounded by word breaks and only replace those with no adjacent word characters.
I completely freaked out at the possibility of matching paired parentheses, so I guess I wasn't sufficiently committed to the task. After all, I am the one that has declared I would sooner consign Excel to the trash-can than go back to spreadsheeting as it used to be! To me the non-LET versions of the formulae are a mess and to be used only as a last resort.
To finish, I can only reiterate my opening sentence; Wow ...
PeterBartholomew1Much appreciated! I did make several false starts with regard to matching opening and closing chars, but I think the saving grace is that I assumed it only should work on formula that Excel has already properly parsed and calculated; I think building a formula validator might be an interesting exercise as well.
I'll have to check out FastExcel, I've used regex in VBA with the appropriate reference, but having it available in the worksheet itself would be nice. If I recall correctly building regex for bracket matching is a bit complicated with a novice's understanding; I know that my balanced tests would fail if a brace was within a pair of double-quotes without a corresponding matching brace before the next formula-level brace.
I agree that non-LET will be a thing of the past after some time, just wish we'd had it sooner, would've made spreadsheet modeling much less aggravating at times.
