Search code examples
c#excelvstocom-interop

COM Interop RCW Reference Count for an Excel Range Object

As far as I'm aware, in COM Interop if we cross the .NET/COM boundary we get an increment in the RCW internal counter. So I created a VSTO Excel workbook (2013) app and ran the code:

private void RCWWorkbooks()
{
Excel.Workbooks wbs = Application.Workbooks;Excel.Workbook book1 = wbs[1];
Excel.Workbook book2 = wbs[1];
Excel.Workbook book3 = wbs[1];
Debug.WriteLine("Book3:= " + Marshal.ReleaseComObject(book3));
Debug.WriteLine("Book2:= " + Marshal.ReleaseComObject(book2));
Debug.WriteLine("Book1:= " + Marshal.ReleaseComObject(book1));
}

And the output is as I expected:

Book3:=2
Book2:=1
Book1:=0

i.e. we have 3 references, a total count of 3 within the RCW which each get decremented by 1 when I invoke the ReleaseCOMObject

I did the same for a worksheet test: This time I get the results:

private void RCWSheets()
{
Excel.Sheets wks = Application.Workbooks[1].Worksheets;
Excel.Worksheet sht1 = wks[1];
Excel.Worksheet sht2 = wks[1];
Excel.Worksheet sht3 = wks[1];
Debug.WriteLine("Sheet3:= " + Marshal.ReleaseComObject(sht3));
Debug.WriteLine("Sheet2:= " + Marshal.ReleaseComObject(sht2));
Debug.WriteLine("Sheet1:= " + Marshal.ReleaseComObject(sht1));
}

And the output wasn't exactly as I expected. 

Sheet3:=3
Sheet2:=2
Sheet1:=1

I can't work out why sheet3:=3. I was expecting this to be 2.

Next I tried a range test with the following code:

private void RCWRanges()
{
Excel.Worksheet sht = Application.Workbooks[1].Worksheets[1];
Excel.Range r1 = sht.Range["A1"];
Excel.Range r2 = sht.Range["A1"];
Excel.Range r3 = sht.Range["A1"];
Debug.WriteLine("Range3:= " + Marshal.ReleaseComObject(r3));
Debug.WriteLine("Range2:= " + Marshal.ReleaseComObject(r2));
Debug.WriteLine("Range1:= " + Marshal.ReleaseComObject(r1));
}

Again, the output wasn't as I expected:

Range3:=0
Range2:=0
Range1:=0

So my questions are:

  1. Why did the sheet test return an extra count. It returned 3 where I was expecting two.
  2. Why did the range test return 0 for all the reference counts? This suggests to me that the range request doesn't cross the .NET/COM barrier.

Thanks

Solution

  • After a little bit of debugging...

    The proxy returned and used in the managed code for the worksheet object persists between function calls for the whole app domain. For example assume you have two methods called sequentially Method1 and Method2 and each method sets a reference to the worksheet object. The first method will create the proxy for the worksheet. The second call will use the same proxy for a different reference to a different worksheet object. So the RCW count is now two.

    However, for a range object, the same proxy does not seem to be the case. It looks like a seperate proxy is created for different range references. You can see this if you use the test:

    Excel.Range r1 = sht1.Range["A1"];
Excel.Range r2 = sht1.Range["A1"];
Debug.WriteLine("r1=r2: " + ReferenceEquals(r1,r2));

    This returns False. This explains why in the original question the counts were all displayed as zero.

    As for object cleanup, I believe that the best way to do this a combination of things. Having your own app domain for the interop calls means that all references will be dealt with properly, which VSTO does. Alternatively use a shim. There's also the possibility under certain circumstances where leaving this to the gabage collector entirely may not what you require - for example you may have a lot of large objects you want to free now as opposed to waiting. In that case I think a manual release is fine. There's also manually invoking the garbage as well. But I personally think there are different situations of when to call what.