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
  • 217
  • 1
  • 15
  • Microsoft has partially rewritten its engine. I don't think there is a way to disable this new feature. – harrymc Feb 22 '21 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). – Arthur Attout Feb 22 '21 at 21:35
  • If possible [edit] the post & share WB with us on cloud and let us examine!! – Rajesh Sinha Feb 23 '21 at 05: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. – Arthur Attout Feb 23 '21 at 08: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 '21 at 20:58
  • Try to see if it works in OpenOffice, Libreoffice or similar... in the meantime waiting/hoping for a patch... – Hastur Feb 26 '21 at 15:17
  • There are claims that this only happens to `.xls` older files and not to `.xlsx`. If that's true, converting your older files to `.xlsx`, but not by using Excel, may help. The post [Batch convert XLS to XLSX](https://superuser.com/questions/524119/batch-convert-xls-to-xlsx) might help. – harrymc Feb 27 '21 at 08:45
  • Unfortunately this happens on my macro-enabled workbooks (`.xlsm`). – Arthur Attout Feb 27 '21 at 16:33

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
  • 455,459
  • 31
  • 526
  • 924
  • 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. – ExcelEverything Mar 30 '21 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.

Arthur Attout
  • 217
  • 1
  • 15