Detecting new Excel Spilling mode and resetting it

Question

Excel has a new Spill mode with a special @ function to replace implicit intersections. It is not quite compatible with existing Excel. So it needs to be explicitly enabled. OK.

I am not sure what enables it, but I suspect a vague prompt about office insider does this. There is no obvious option in the UI to enable or disable this breaking change!

Does anyone know where in the registry this is changed? Can it be unset back to the old mode?

Further, is there a good programmatic way to test for this? I just look for ActiveCell.Formula2 -- if it says no method then it is pre Excel2016 build whatever, if it says "misc error" that means that it could enabled but is not, and if it returns a string then it is enabled. At least that is what happens today on my version of Excel. But something more principled would be better. In the good old days there would be Application.ImplicitIntersectionDisabled or similar.

P.S. The error code for Formula2 is not reliable, 462? Not Available for old versions of Excel, generic 1004 Something Went Wrong for current versions not in Insider program.

Answer 1

Afaik there is no particular spill mode, but spill is the output of a dynamic array. So every dynamic array function produces spill. This was introduced late 2018.

Spill functions cannot write to cells that are populated, this produces an error-message. Also previous matrix functions should be replaced, according to the ms-documentation.

The following functions returns spill.

• FILTER Filter data and return matching records
• RANDARRAY Generate array of random numbers
• SEQUENCE Generate array of sequential numbers
• SORT Sort range by column
• SORTBY Sort range by another range or array
• UNIQUE Extract unique values from a list or range
• XLOOKUP Modern replacement for VLOOKUP
• XMATCH Modern replacement for the MATCH function