Excel Bug: Limited willingness to copy Lambdas from local scope

[ecovonrein]

I just post here in the hope that someone from M$ takes an interest in this blog.  If you know of, or have, a way of reporting bugs to M$, please bring this post to their attention.

Background:

We maintain global Lambdas centrally.  Where we need Lambdas in the local scope, we use a simple wrap structure, like this:

Global Lambda, not meant to be called from formulas:

Private.mySUM = LAMBDA(x;y;z;x + IF(ISOMITTED(z);0;z))

Local Lambda, supplying the Worksheet "Bug" context ("Bug!Codes") to the global Lambda:

Bug!mySUM = LAMBDA(x;[z];Private.mySUM(x;Bug!Codes(x);z))

In this way, it is possible to have centrally maintained Lambdas in the local scope.  A call in the spreadsheet like

=mySUM(1;4)

will yield 5.  Hurrah!

The bug:

Although Codes is not used at all, it is important to prompt the bug.    It is defined like so:

Bug!Codes = LAMBDA(r;SAMEROWS(Bug!$B:$B;r))

And although Codes is without interest, the definition of SAMEROWS seems crucial to the bug, so here it is in all its glory:

SAMEROWS = LAMBDA(super_range,sub_range, LET( _
	subRow1, ROW(INDEX(sub_range,1,1)), _
	subRowN, ROW(INDEX(sub_range, ROWS(sub_range),1)), _
	supRow0, ROW(INDEX(super_range,1,1)) - 1, _
	INDEX(super_range,subRow1-supRow0,1):INDEX(super_range,subRowN-supRow0,COLUMNS(super_range)) _
))

Insert these Lambdas into a blank worksheet "Bug" and call the formula =mySUM(1;4) somewhere in that worksheet to get 5.

Now open a new blank workbook (CTRL-N) and COPY the mySUM formula into that workbook. 

You will not succeed.

What should happen

In the copy operation, all missing Lambdas should be pulled across.  Minor modifications to the setup will also make this happen, eg

mySUM = LAMBDA(x;[z];Private.mySUM(x;SAMEROWS(Bug!$B:$B;x);z))

succeeds.  Similarly, when we define

SAMEROWS = LAMBDA(x;x)

life is good.

Very odd.

[#2]

Could you share an Excel file with the right setup to repro this bug? If you cannot upload a file here, perhaps share a link to the file after uploading to e.g. OneDrive and sharing it?

[#3]

@Jan Karel PieterseVoila

[#4]

@ecovonrein I have notified the Excel team.

Next time (even better: now!), please click Help, Feedback and select "I don't like something" to send comments like these in a more direct manner. If you share your email address and (if applicable) a screen-shot that will help them trouble-shoot and perhaps get hold of you in case there are suggestions for workarounds, or to get clarification on the issue.

[#5]

You told me this in the past, and I did it before. Zero feedback. Perhaps you have a better track-record with them. When I used the dialogue last time, I only thought "This isn't serious. With that kind of dialogue they get a million pointless messages."

[#6]

A quick update for the record. I have just completed a 4-month wholesale rewrite of a core model taking full advantage of Lambdas and SPILLs and I now have a clean model which, it turns out, I cannot instantiate by copying across Workbooks because Excel point-blank says "We couldn't copy this sheet."...

Just great :(

[#7]

I've reported this to the Excel team directly.

[#8]

I did a frown. Perhaps if we both gang up on MS, we get some response at some point. Thanks.