6

Since an update I have from Excel, which induces the automatic insertion of the implicit intersection operator @, my formulas have changed behavior in Excel workbooks.

I noticed the problem appeared with user-defined formulas.

I have narrowed-down the issue to an MCVE with the following scenario :

Let CustomRange be a range referring to 8 rows, one of which is empty:

Alpha
Bravo
Charlie

Echo
Foxtrot
Golf
Hotel

Let CustomFunction be a user-defined function which takes an integer N as parameter and return the Nth element of that custom range

Function CustomFunction(Num As Integer)
 Dim R As Range
 Set R = Range("CustomRange")
 CustomFunction = R(Num, 1)
End Function

Let’s compare the results returned by the formula by including and omitting the implicit intersection operator:

=IF(@CustomFunction(4)="","EMPTY","NOT_EMPTY")
=IF(CustomFunction(4)="","EMPTY","NOT_EMPTY")

The first one will yield NOT_EMPTY, while the second one will yield EMPTY.

Since Excel is adding automatically the @ onto my old workbooks, my formulas do not work anymore. If I find & replace all @ with nothing, my formulas start working as expected again (so the sentence from the article stating that "your formulas will continue to calculate the same way they always have." is incorrect).

I would like Excel to stop altering my formulas, is there a way around this ?

8
  • Microsoft has partially rewritten its engine. I don't think there is a way to disable this new feature.
    – harrymc
    Feb 22, 2021 at 14:59
  • 1
    This doesn't sound good. Maybe the fact that it is a breaking change could induce a fix in an upcoming version (because this is actually easily fixable). Feb 22, 2021 at 21:35
  • If possible edit the post & share WB with us on cloud and let us examine!! Feb 23, 2021 at 5:27
  • 1
    Sharing the workbook wouldn't be of any help because it depends on the Excel version being used. I also described an MCVE which would not take more than 2 minutes to implement in a blank workbook. Feb 23, 2021 at 8:47
  • I have seen many posts where users complained about this change invalidating all of their Excel jobs, which Microsoft seems to be ignoring - not a good sign for the future. The complaints were for Office 365, with earlier Excel versions not suffering from the problem. As a temporary solution, you may drop back to an earlier version, until a better solution is found (by Microsoft or by workaround from users).
    – harrymc
    Feb 23, 2021 at 20:58

2 Answers 2

0

The Implicit intersection operator: @ was added in Excel 365 for Windows version 1907 build 11901.20176 as part of the Dynamic array formulas rewrite of the Excel engine, and it is here to stay.

Essentially, Microsoft did away with the concept of array functions (though they will still work), instead allowing almost all functions, including VLOOKUP, to return an array of values. If the array of returned values won't fit in the available space, you get the new #SPILL! error.

You may avoid the error by using the @ operator, as described in the article Disabling #SPILL! Errors. However, this requires modifying your formulas.

The good news is that if the @ operator breaks your formula and you delete it manually, and it will stay deleted after a Save. Excel only does the conversion of the formulas once per spreadsheet. Unfortunately, we don't currently know how to manually turn on this flag to avoid the conversion in the first place.

There are not too many options known actually for undoing formula conversion:

  • Stay with a previous version of Excel until Microsoft supplies a way to avoid the conversion
  • Write a VBA macro that will delete the @ operator from all formulas (taking the risk of getting the new #SPILL! error)
  • Manually fixing your formulas.

None of these methods is particularly satisfying, but I haven't found a better one.

1
  • Could you just edited the file version number in the XML markup files somewhere inside the unzipped .xlsx file? This might make Excel think it has done the conversion already? Of course this doesn't help if your old files are still in .xls. Mar 30, 2021 at 22:52
0

A bit late to the party, but I can confirm the build 14026.20246 fixed the issue. While it is not explicitely stated in their release notes, some local tests lead me to believe this isn't an issue anymore.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .