2

Here's what I am trying to achieve with a Excel Addin. Excel user selects a range and he deletes it. I want to write cell data (whatever he deletes) in that range to a log file. Multiple questions on SO say that you can't get deleted range in SheetChange event handler, you need to store it. I am storing range in a global variable LastRange in selection change event method handler. I need LastRange in SheetChange event handler.

The problem is I get COM exception --

COM object that has been separated from its underlying RCW cannot be used.

I know somewhere that LastRange is getting freed and hence the exception. I referred to another SO question here. Problem persists even though I have strong references to the delegate.

private  ADXExcelSheet_EventHandler sheetChangeEventHandler;
private  ADXExcelSheet_EventHandler sheetSelectionChangeEventHandler;

private void InitializeComponent()
{
    sheetChangeEventHandler = new AddinExpress.MSO.ADXExcelSheet_EventHandler(SheetChange);
    excelEvents.SheetChange += sheetChangeEventHandler;
    sheetSelectionChangeEventHandler = new ADXExcelSheet_EventHandler(SheetSelectionChange);
    excelEvents.SheetSelectionChange += sheetSelectionChangeEventHandler;
}

//....

Range LastRange;

public void SheetSelectionChange(object sender, object sheet, object range)
{
    LastRange = (range as Range);
}

public void SheetChange(object sender, object sheet, object range)
{
    ClassX.Method1(range as Range, LastRange);
}

Not sure why LastRange is still null inside Method1. LastRange is correctly passed from SheetChange to Method1(). Any other ideas to get this working?

Community
  • 1
  • 1
user1
  • 4,031
  • 8
  • 37
  • 66
  • Is excelEvents something from addinexpress? The Application.SheetSelectionChange event handler is a bit different – Slai Apr 05 '17 at 10:27
  • Yes. excelevents is from Addinexpress – user1 Apr 05 '17 at 10:48
  • It looks like Add-in Express releases all COM objects passed to an event handler right after the event handler finishes. Any workarounds? I want to store deleted range object and reuse it in a different event handler. – user1 Apr 06 '17 at 03:23
  • I confirmed with Add-in express team. Add-in Express releases all COM objects passed to an event handler right after the event handler finishes. Only workaround in this case is, store all rows inside Range object and pass it to Method(). – user1 Apr 06 '17 at 11:43
  • I don't see any use of the Range if the values are deleted. Range is just a pointer to the location and values. To save the cell data, something like `object LastRangeValue = (range as Range)?.Value` – Slai Apr 06 '17 at 13:04

1 Answers1

0

What about if you make a copy of the range?

var sheetConcrete = (Worksheet)sheet;
var rangeConcrete = (Range)range;
LastRange = sheetConcrete.Range[rangeConcrete.Address]

(you might need to play with the syntax to get it working)

The add-in should have no way of tracking that COM object, so it should stay alive.

satnhak
  • 9,407
  • 5
  • 63
  • 81
  • So what happens when you make a copy? Does it throw an exception when you try to access it? – satnhak Apr 06 '17 at 06:10
  • Yes, Looks like copied object is invalid. – user1 Apr 06 '17 at 06:24
  • 1
    Anyway, I figured out the issue here. I am just drafting an answer. Thanks for your help. – user1 Apr 06 '17 at 06:25
  • 1
    No probs, glad you got it working. Sometimes all you need is a bit of moral support. I often find that just asking the question makes me think about it more and gets me closer to an answer. – satnhak Apr 06 '17 at 07:00