Voice from the field

Tags: .Net VSTO COM
Donwloads: Demo Project

CCR: I love you, but I leave you!

July 14 2016

 

From many developers I hear a complain that Office application is not cooperate as expected. Yes, it is, because interaction with Office based on the COM objects. Unfortunately, developers have to strictly follow the rules to avoid ends up with following....

Yes, that is mandatory rules and help to save your time and avoid crashes in future products.

Let me describe the problem first. When you create VSTO addin it works in Excel process and if it crashed or throw an exception the exception will be gracefully handled by Office environment. Usually, often... In worst case when the exception happened whole Excel/Word/PowerPoint process will crash and nothing will stay in memory except your bleak memories about lost data.

Another case you have when the external app (e.g. console) instantiates Excel/Word/PowerPoint process by Callable Com Wrappers.  In worst case you simple have one more office app process in the process list which is hidden and stuck in the memory. In worst case the target machine will get stuck, hung or down because of running out of memory or handlers.

To be honest it is some case when COM Addins and VSTO addins has a reference problem which is lead Office application say in the process list and consume memory.

Just a 5 cent of theory of CCR. On right side we have your awesome console application which is call Excel to open workbook. On left side we have COM client - which is Office Application exposed own object for you to open o work book. Let say Workbooks object with method Open(...). Then we have CCW create to marshal calls from .Net object (your app) and Excel objects (Workbooks)

COM interfaces

Finally, through the PIA attached to your project you successfully call Application.Workbooks.Open(...) and the object Workbook which you get as result and object Application and object Workbooks create own CCW and then left out of scope your function. Finally, the recognized as garbage and collected by "garbage collector".  What do you think happened with CCW?  Right! They still alive and Because your COM reference was not properly released. Basically your com client is still though it is in use, but you already leave it.  

Simply as our diving course captain usually says when see the coast patrol on horizon: "I love you but, I leave you"

Basically you leave the object which is make whole application thought that is in use and say in the memory.

 

Let' discuss the simple rules which is help you to avoid the memory problem for external application.


      1. Each Office object you going to use has a COM reference. COM reference need to be properly released. Always wrap Office objects in the Dispose implemented class and clean up on Dispose stage.


      2. Dispose root reference of the Application only when you finish app execution.


      3. Use ReleaseComObject instead of FinalReleaseComObject. Remember the "Final" is final.


      4. Each enumeration like Sheets in Workbook, Workbook in application generate additional COM references which is need to be properly released. You may cast it to ICustomAdapter and call GetUnderlyingObject or use get-by-index strategy instead of go-in-foreach.


      5. Double dot rules. Expressions like "Application.Cells[1, 1].Value2"  or "Application.AutoRecover.Enabled" always generate additional COM object (to Range and AutoRecover in this case) which is need properly disposed.


      6. Use EventSink when you need to subscribe for events like "SheetCalculate"

 

You can find attached demo project which is demonstrate opening 2 excel work book, fetching data and properly releasing reference. It also demonstrates subscription on ChangeSheet event. Enjoy J