Forum Discussion
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?
I have no solution or workaround, but that's better to discuss with AFE team here Issues ยท microsoft/advanced-formula-environment (github.com).
- ecovonreinIron Contributor
SergeiBaklanThanks for replying to my post. My bugbear is not with AFE. It would be nice if it respected local Lambdas but the real problem here is with Excel.
When it's better to separate. AFE is not part of the Excel, actually that's third-party product. Even if Cambridge is under Microsoft umbrella.
If you add directly in Name Manger, like myLamda=LAMBDA(x, x+1), it could be in sheet or workbook scope, as we define. AFE always use only workbook scope.