Local scope Lambda randomly produces #NAME

Question

Looking for experiences from readers here.

We have a large-scale Excel production environment and I am presently engaged in reprogramming it to take advantage of new SPILLs and LAMBDAs.

Unlike AFE, which seems to have no time for local scope Lambdas, I very much embrace them as a way of turning a Worksheet into a kind-of object. So we might imagine a Workbook made up of different Worksheets, each holding a particular database. These database structures can be completely different from one another, but if each Worksheet offers a local Lambda eg "TimeSeries" then a database user (in another Worksheet) does not need to concern himself with the structure of the underlying databases but can simply call DBase1!TimeSeries(...) or DBase2!TimeSeries(...) and delegate the responsibility for producing a particular data structure (the timeseries) to DBase1 and DBase2 (who know the laypout of their respective databases).

This all works very well. Until it doesn't. Because the call to DBase!TimeSeries suddenly produces #NAME. I have found a way for repairing the issue. Until it suddenly appears again.

The way I fix the problem goes like this:

I rename "TimeSeries" in "DBase1" to "TimeSeriesA". Calc. All errors disappear. I rename "TimeSeriesA" back to "TimeSeries" and all is good.

Has anyone stumbled upon something similar? Figured out what's provoking this behaviour? Thanks.

jkpieterse · Answer

Host of questions:Have you sent a frown to the Excel team about this? Do you have repro steps and/or a sample workbook in this state? Does Control+Alt+Shift F9 get rid of the #Name! errors?What does the offending lambda (and dependencies) look like?

ecovonrein · Answer

No, and no. I do not bother to report errors that I cannot reliably (and demonstrably) reproduce. This one comes and goes as it sees fit. The Lambdas are very challenging. I realize that now that my 4-month development is coming to an end. My new model clearly pushes the boundaries of what Excel can currently support. I have a feeling (no more!) that this all relates to this:
https://techcommunity.microsoft.com/t5/excel/excel-bug-limited-willingness-to-copy-lambdas-from-local-scope/m-p/3722676#M177974
I am actually now sitting on the finishes model that I cannot copy between workbooks. Unreal.
(I am about to send "a frown" about that. I urgently need that copy bug fixed.)

I only reported it here in the vain hope that someone (like yourself) might have stumbled upon the same problem and found a way to reproduce it.

jkpieterse · Answer

I've reported these issues to the team directly, see what happens 🙂

ecovonrein · Answer

Dear Microsoft,&nbsp;The penny has finally dropped on what is happening here.&nbsp;We are an international team.&nbsp; Our production environment is fragmented into different Excels to as much as possible avoid people clashing when updating (their) data. *)&nbsp; There is a final production Excel which sources data thru any number of links to all these feeder files.&nbsp;The disastrous issue is this:&nbsp; As many will know from PAINFUL experience, each Windows user installation creates a unique file tree (in C:\Users) for that user.&nbsp; This plays havoc with any links between Office applications when the files are exchanged between different users (be that by email or OD).&nbsp; Since all our users have laptops, there is never more than one user profile on a computer and all computers are hence supposed to be configured with the same user name, to resolve the issue.&nbsp;Alas, some are not 😞&nbsp;To work around the issue, our Excels in their Auto_Open "sense" the file tree of the local user and relink any Excel links according to the user's configuration.&nbsp;Here is what transpires:When you create an external reference such as someData='[someWB.xlsm]someWS'!someName where someWB is inside your OD and another user sharing that same OD opens that same Excel, even before anything else can happen, the (unresolved) reference will now read:&nbsp;someData='C:\Users
ameOfAuthor\...\someWB.xlsm'!someName&nbsp;It is clear that my efforts to repoint someData to the appropriate path for someWB in my OD must fail because the crucial reference to someWS has already been lost.&nbsp;PLEASE FIX AS A MATTER OF URGENCY.&nbsp;*) We were initially very excited about Online Collaboration but our experience with it in our complex production environment has been a massive disappointment.&nbsp; Nomadic (offline) users in particular were extremely badly served.&nbsp; We hence stick with Online Collaboration turned off.&nbsp;

Forum Discussion

Local scope Lambda randomly produces #NAME

Share

Resources