Getting the previous spill range of a dynamic array function

[pyxll]

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.SpillingToRange return False and Nothing respectively. Is this intentional? Is there another way to get the spill range (maybe using XLM or the C API instead)?

I need this for automatically formatting the result of a dynamic array function. With CSE array functions the Caller refers to the entire range. I schedule a function to run after Excel has finished calculating and apply formatting to the Caller range. By then it's possible to get the new spill range, but I also need to get the old one so that any previous formatting can be cleared before applying the new formatting (in the case where the spill range has shrunk).

Thanks in advance!

Tony

ps. I only need this to work on Windows

[#2]

@pyxll 

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

[#3]

@fastexcel 

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