Forum Discussion

ecovonrein's avatar
ecovonrein
Iron Contributor
Jul 09, 2024

Despair over external references to local scope Lambdas

There must be something weird about local scope Lambdas.  As far as I can tell, they are a perfectly valid concept.  On a noddy level, they also work as advertised.  But eg the developers behind AFE clearly disapprove of them - AFE simply ignores them.

 

Leave the realm of noddy coding and Microsoft no longer appears to love them either.

 

Suppose sheets A and B with a Lambda myFunc in the local scope of B.  In A, we will write

 

=B!myFunc(...)

 

and life is good.  No problems whatsoever.

 

Next, move B into a new Workbook such that in A we now see the reference

 

='[Book2]B'!myFunc(...)

 

That works at first.  But save Book1 (with A) and Book2 and re-open Book1 and really anything can happen in that cell.  The least harmful thing I get is

 

='Book2'!myFunc(...)

 

which cannot work.  But my Book2 has multiple Worksheets (B ... Z) all with their own instantiation of myFunc and I have seen Excel open Book1 with the original reference to B replaced as

 

='[Book2]M'!myFunc(...)

 

which is NOT FUNNY at all because, well, that Lambda does exist but, of course, returns a different result to the one in B.

 

This is totally insane.  Has anyone experienced this issue before?  Any solutions or workarounds?

 

Resources