Excel automation. Need to get the previous selection when selection changes

Question

I'm working on an import (from Excel) dialog that uses automation to allow the user to select ranges of cells for import. When the range is selected, I'm using the event sink to catch the event and hilight the first row and first column. I need to have a way to unhilight the previous selection's first row and column. I don't think it's safe to just get the selected range at the time the selection changes, and remember it, such as (psuedocode for brevity and clarity):

OnSelectionChange()
 {
 if (m_PrevSelection)
    UnHilite(m_PrevSelection);
 HiliteCurrentSelection();
 GetSelectedRange(m_PrevSelection);
}

I'm guessing that just holding onto that range (obtained from _Application::Selection) without releasing it is going to cause all sorts of problems (but maybe I'm wrong). I haven't found a way to copy the range (IRange Copy just copies cell contents from one range to another).

I guess I could take the range's cell addresses and store those, then recreate a range from the when I need to do the unhilighting, but this would seem to me to come up so often I'm wondering if anyone else had a more elegant solution.

Answer 1

If you were working in Excel VBA, you could

Set Rng = Application.Selection

where Rng is an Excel Range object. I imagine you could replicate this object from where you are.

Or you could store the cell address in a string variable as you suggested, which of course doesn't require any objects.

Unfortunately, Excel doesn't keep a history of selections.

Answer 2

If you do not expect the range to move or change in size I would store the address and later use Range(myAddress) to return the range object for highlighting.

An address reference will always refer to a fixed area of the sheet whereas a range will be updated to reflect cell insertions/deletions. Either of these may suit your intentions but note that the range reference may become undefined if the cells it contains are deleted.

Answer 3

Whenever you detect a new range (which you're already doing, to highlight the first row and column), save details of that range to variables somewhere (assuming it's a single, rectangular range, store first and last row, and first and last column).

Whenever you detect a new range after that, you now have stored details of the previous range to use to clear the previous highlights (and this event will store the details for next time, and so on).