Forum Discussion
ecovonrein
Jul 09, 2024Iron Contributor
Weird behaviour when replacing a cached Lambda
I am experiencing some weird behaviour in a production sheet which I have not unfortunately succeeded in replicating in a simple Excel. The problem goes like this, conceptually:
Some_Work = LAMBDA(p, IF(Flag,LET(a, ..., b, ..., c, ..., f, LAMBDA(x,Big_Work(a,b,c,x), IF(p>0,f(p),f(-p))), Little_Work(p)))
I get the impression that Excel is very smart going about this. It seems to realize that Flag is a global constant and it hence realizes early on that Some_Work will either always mean Big_Work or Little_Work. When Flag means Big_Work, Excel also appears to precalculate a, b and c.
Something weird happens (in my production sheet) when I replace (in VBA) Big_Work and Little_Work. The replacement works in every case thru 1) Delete and 2) Add (a new Name). When I replace Little_Work, Excel has no problem at all. But when I replace Big_Work, Some_Work return #N/A!.
It is really very strange. I am guessing - and this is a pure guess - that Some_Work is somehow precompiled such that the dependency on Big_Work no longer registers?
Does this ring any bells with anyone?
For the record, after hours of debugging it transpires that little of what I worried about in the OP had anything to do with the problem. Well - besides perhaps somehow triggering a bug in Excel. We will never know.
Anyway, the killer was an innocuous assignment inside LET like ..., c, Sheet!$S800. That is, Some_Work should refer to column S in which-ever row it is called from. (Some_Work exists in the scope of a Worksheet to provide local context to the Lambdas Big_Work and Little_Work in the scope of the Workbook.)
It failed. Somehow. (I have encountered similar issues in the past with fully relative Names like "=!S5".)
The resolution to the problem was my coding LET( ..., colS, $S:$S, c, @colS, ...).
- ecovonreinIron Contributor
For the record, after hours of debugging it transpires that little of what I worried about in the OP had anything to do with the problem. Well - besides perhaps somehow triggering a bug in Excel. We will never know.
Anyway, the killer was an innocuous assignment inside LET like ..., c, Sheet!$S800. That is, Some_Work should refer to column S in which-ever row it is called from. (Some_Work exists in the scope of a Worksheet to provide local context to the Lambdas Big_Work and Little_Work in the scope of the Workbook.)
It failed. Somehow. (I have encountered similar issues in the past with fully relative Names like "=!S5".)
The resolution to the problem was my coding LET( ..., colS, $S:$S, c, @colS, ...).