Forum Discussion
Getting the previous spill range of a dynamic array function
Hi, when writing a worksheet function that returns an array, how can I get the spill range of the previous result? In a Function, Application.Caller.HasSpill and Application.Caller.SpillingTo...
My (not-yet-implemented) design for formatting dynamic arrays does this by storing the spill dimensions of the spill ref as cell metadata using the aftercalculate event.
The cell metadata could be stored in various ways. To make it persist maybe define a Name for the spill ref and then use the Name as a key to an array/list/dictionary/collection thing that could be persisted as custom xml parts. Or for a non-persistent kludge use Cell.ID
Hi Charles,
thanks for that. Yeah, I was hoping to do it without having to add any metadata to the sheet. I already do that for some other features though, so it's not the end of the world. A map of cells to spill ranges in a CustomXMLPart is probably the least worst solution 🙂
Thanks again,
Tony
