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 ?