Forum Discussion
Despair over external references to local scope Lambdas
I have no solution or workaround, but that's better to discuss with AFE team here Issues · microsoft/advanced-formula-environment (github.com).
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.
SergeiBaklanThe only workaround I can think of is to create a VBA macro which I can give the input "myFunc" and which scours all the Worksheets in my Workbook to create a new global Lambda bugFix_Worksheet_myFunc and defines that to be =Worksheet!myFunc.
This is blooming awful.
SergeiBaklanHere you go. Try it for yourself. Open Book2 and Book1. Book2 does nothing other than to provide the local Lambda myFunc. You will see that Book1 tries to call myFunc but returns #NAME! because the reference is invalid. Fix it. Save Book1. Close it. Re-open. Bang - f***ed again.
SergeiBaklanCorrection. The error in Book1 is more fundamental - it refers to the wrong Workbook. When I correct this, the little setup is unfortunately unable to reproduce the problem.
SergeiBaklanI know. I do not understand why you make this about AFE. My problem has NOTHING to do with AFE. The problem relates to Excel. I just pulled up such a file again and Excel has simply stripped all sheet references from the external link. This is OUTRAGEOUS. It is almost impossible to repair because it isn't obvious from the remaining Workbook reference what Worksheet was originally addressed. Absolutely crazy.
Nota bene: This happens WITHOUT my having so much as changed the source of the reference. Repair. Save. Close. Open - broken again.
