SOLVED

Weird behaviour when replacing a cached Lambda

Iron Contributor

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?

1 Reply
best response confirmed by ecovonrein (Iron Contributor)
Solution

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, ...). 

 

1 best response

Accepted Solutions
best response confirmed by ecovonrein (Iron Contributor)
Solution

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, ...). 

 

View solution in original post