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 ?

Arthur Attout
  • 287

2 Answers2

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.

harrymc
  • 498,455
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.

Arthur Attout
  • 287