Spill functionality and volatile functions

Copper Contributor

I've been looking at the spill functionality for some time now (having first done something like it about 15 years ago at a large investment bank). I have two observations / questions:

1) I'm struggling to see how changes to the sheet topology fit with the Excel calculation model - changing the structure of a sheet should normally be done outside of the calculation (as it changes the dependency graph etc...). Is there any documentation on how this should work please?

2) The spill error seems to occur at random with volatile functions (or even non-volatile replacements written in VBA). A simple "=SEQUENCE(RANDBETWEEN(10,20),1,1,1)" on an empty sheet should never result in a #SPILL! error, but will (at random) give this result. That is, repeated recalculations will sometimes give an error, and sometimes not, even for the same value returned from the RANDBETWEEN (pull it out in to a separate cell to observe this). I've tried non-volatile VBA replacement, I've tried different machines and installs, and different patches. This really feels like a bug!

3 Replies
@Avsed, you mentioned it also happens on non-volatile cases. I'm curious, please clarify what non-volatile VBA code you are using where it is still happening.
It will probably be fixed in the next versions, as Google Sheets does not have this problem