|
Michael Kaden
Posts: 33
Joined: 2023-10-25
|
Hello Andrei,
I have a general question to Object Release
In my COM AddIn
'Select the current cell
Dim selection As Excel.Range = CType(excelApplication.Selection, Excel.Range)
'Write to other cells
selection.Offset(2, 1).Value2 = 1000
selection.Offset(2, 2).Value2 = 100
selection.Offset(2, 3).Value2 = 2000
Now I understand for previous correspondence we had, I should put
System.Runtime.InteropServices.Marshal.ReleaseComObject(selection)
at the end of the procedure. My questions are:
- Will this also release the selection.offset Objects
- So far i understood only Range Objects should be released no other Objects?
Your advise is highly appreciated
Thank you and kind regards Michael |
|
|
Andrei Smolin
Add-in Express team
Posts: 19240
Joined: 2006-05-11
|
Hello Michael,
Dim selection As Excel.Range = CType(excelApplication.Selection, Excel.Range)
1. This invokes the Application.Selection property from the Excel object model. The result is: 1) the COM object; you don't access it directly in your code because your code only deals with .NET objects; 2) the wrapper object holding a reference to the COM object; although this is a .NET object, you don't access it directly because it is part of the COM implementation in .NET; 3) the .NET object that you directly use in your code.
2. After the Application.Selection property is called and all the objects above are created, the code line above casts the .Net object to the Excel.Range type and stores it to the selection variable; the variable stores a reference to the wrapper object.
3. Calling Marshal.ReleaseComObject(selection) will make the wrapper object for the specified variable to release the underlying COM object.
selection.Offset(2, 1).Value2 = 1000
selection.Offset(2, 1) creates the same triad: 1) the COM object; 2) the wrapper object holding a reference to the COM object; 3) the .NET object (of the Excel.Range type) that you directly use in your code. Because of the syntax used, the .NET object is used to invoke Range.Value2; then this .NET object is left for the next run of the Garbage Collector. This COM object should be 1) stored to a separate variable, say, range1, 2) used, e.g. range1.Value = 1000, 3) released by calling Marshal.ReleaseComObject(range1).
Regards from Poland (GMT+1),
Andrei Smolin
Add-in Express Team Leader |
|
|
Michael Kaden
Posts: 33
Joined: 2023-10-25
|
Dear Andrei,
Thank you very much for your explanation. I have also read the Add-in Express article about releasing Excel COM objects.
https://www.add-in-express.com/creating-addins-blog/release-excel-com-objects/
I would appreciate some clarification before choosing a consistent approach for my new CyDesign add-in.
The Microsoft documentation
https://learn.microsoft.com/en-us/dotnet/api/system.runtime.interopservices.marshal.releasecomobject?view=netframework-4.8.1
for Marshal.ReleaseComObject advises using this method only when necessary and describes the risks when other code still uses the same runtime-callable wrapper.
I am therefore trying to understand how this guidance fits with your recommendation to release each temporary Excel range explicitly.
My previous application, aleraSoft, combines an XLL and a COM add-in. The XLL functions schedule work that the COM add-in performs through OnSendMessage. During solver iterations, the application can make many thousands of range accesses within a few seconds, for example:
selection.Offset(3, 4).Value2 = 122
Previously, I released only the original selection at the end.
I now understand that this does not explicitly release the intermediate offset ranges. However, I have not identified a memory or Excel shutdown problem attributable to leaving those temporary ranges to garbage collection.
For CyDesign, could you please clarify:
1. Is explicit release of every temporary range a requirement for reliable operation with Add-in Express, or a recommendation to ensure prompt cleanup?
2. What specific problems should I expect if I let .NET garbage collection release temporary ranges once they are no longer referenced?
3. Which references should my code release, and which should it leave alone because they are supplied or managed by Add-in Express or shared with other code?
My concern is that adding explicit releases throughout the application increases complexity and could introduce premature-release errors.
I would like to understand the practical benefit and adopt the simplest reliable approach for an add-in running inside Excel.
Thank you very much for your guidance.
Kind regards,
Michael |
|
|
Andrei Smolin
Add-in Express team
Posts: 19240
Joined: 2006-05-11
|
Hello Michael,
First off, Microsoft refers to a really wide set of scenarios, far beyond COM add-ins.
You should understand that they cannot recommend a programming culture to such a wide set of programming scenarios. On the other hand, we lived all our lives with the rule of thumb: release everything you create. Naturally, we adapted it to the .NET world. So, the recommendation to release every COM object comes from our experience. To minimize the risk of leaving a COM object unreleased we also invented a rule of dividing responsibilities between the caller and callee suggesting that the callee should never release a COM object that the caller passes to the callee; instead, we suggest that the caller should be responsible for releasing such COM objects.
When the Garbage Collector finds a non-referenced .NET variable (such as the one created by selection.Offset(2, 1)) it releases it as well as the COM object the variable refers to. But the release occurs in an unpredictable moment and the potential problem is: the COM object may break your code re-using that very cell/range; or, it may break when Excel will be closing. In fact, there may be a plenty of other non-determined scenarios. Say, I wouldn't be surprised, if you'll get a COM-related exception when creating/updating/deleting a chart referring to the same range/worksheet/workbook. Too many fault scenarios! No specificity! Complete unpredictability! Sometimes it looks like such an exception is somehow related to moon phases. This can be solved by discipline.
If you follow the caller/callee responsibility division paradigm, you'll never release NET things that Add-in Express passes to your code; this is exactly what we expect from you.
Sure, releasing COM objects in this way makes your code more complex; such code resembles what C/C++ programmers do. On the other hand, you'll release all your COM objects, and you'll release them in predictable moments.
Unfortunately, I don't know what the simplest approach could be.
Lately, I use this one. I have an extension method (I call it ReleaseComObject) that accepts an object, verifies that the object not null (Nothing in VB), that the object is a COM object and releases it. This allows writing selection.ReleaseComObject() and then aRange.ReleaseComObject(). Quite a minor thing, though.
public static int ReleaseComObject<T>(this T o)
where T: class
{
if (o != null && Marshal.IsComObject(o))
{
int result = Marshal.ReleaseComObject(o);
return result;
}
else
return 0;
}
Regards from Poland (GMT+1),
Andrei Smolin
Add-in Express Team Leader |
|
Forums
Topics
Search
Help
Login
Register