Forum Discussion

ecovonrein's avatar
ecovonrein
Iron Contributor
Jan 23, 2023

Excel Bug: Limited willingness to copy Lambdas from local scope

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.

 

7 Replies

  • ecovonrein's avatar
    ecovonrein
    Iron Contributor

    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 😞

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      I've reported this to the Excel team directly.
      • ecovonrein's avatar
        ecovonrein
        Iron Contributor
        I did a frown. Perhaps if we both gang up on MS, we get some response at some point. Thanks.
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    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?
      • JKPieterse's avatar
        JKPieterse
        Silver Contributor

        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.

Resources