ReleaseComObjects scenarios

Add-in Express™ Support Service
That's what is more important than anything else

ReleaseComObjects scenarios
 
Albert Siagian


Guest


Hello,

I am trying to use ReleaseComObjects, but the results are very confusing, sometimes it works and sometimes it doesn't. I guess probably because I don't know when it is creating new objects (increment the counter) and when it is referencing. Appreciate more detail information about this.

2 scenarios:

Scenario 1: Procedure1 calls Procedure2, and Procedure3 calls Procedure3. Where should I use ReleaseComObjects ?
Sub Procedure1()
Dim ci = AddinModule.CurrentInstance
Dim ExcelApp = ci.ExcelApp
Dim wb As Excel.Workbook = Nothing
Dim ws As Excel.Worksheet = Nothing
ExcelApp.Workbooks.Open("file1.xlsx")
wb = ExcelApp.ActiveWorkbook
ws = TryCast(wb.ActiveSheet, Excel.Worksheet)
ws.Cells.Range("A1").Value = 123

Procedure2()
End Sub

Sub Procedure2()
Dim ci = AddinModule.CurrentInstance
Dim ExcelApp = ci.ExcelApp
Dim wb As Excel.Workbook = Nothing
Dim ws As Excel.Worksheet = Nothing
ExcelApp.Workbooks.Open("file2.xlsx")
wb = ExcelApp.ActiveWorkbook
ws = TryCast(wb.ActiveSheet, Excel.Worksheet)
ws.Cells.Range("A2").Value = 456

Procedure3()
End Sub

Sub Procedure3()
Dim ci = AddinModule.CurrentInstance
Dim ExcelApp = ci.ExcelApp
Dim wb As Excel.Workbook = Nothing
Dim ws As Excel.Worksheet = Nothing
ExcelApp.Workbooks.Open("file3.xlsx")
wb = ExcelApp.ActiveWorkbook
ws = TryCast(wb.ActiveSheet, Excel.Worksheet)
ws.Cells.Range("A3").Value = 789
End Sub

Scenario 2: Procedure11 calls Procedure22 followed by Procedure33. Where should I use ReleaseComObjects ?
Sub Procedure11()
Dim ci = AddinModule.CurrentInstance
Dim ExcelApp = ci.ExcelApp
Dim wb As Excel.Workbook = Nothing
Dim ws As Excel.Worksheet = Nothing
ExcelApp.Workbooks.Open("file1.xlsx")
wb = ExcelApp.ActiveWorkbook
ws = TryCast(wb.ActiveSheet, Excel.Worksheet)
ws.Cells.Range("A1").Value = 123

Procedure22()
Procedure33()
End Sub

Sub Procedure22()
Dim ci = AddinModule.CurrentInstance
Dim ExcelApp = ci.ExcelApp
Dim wb As Excel.Workbook = Nothing
Dim ws As Excel.Worksheet = Nothing
ExcelApp.Workbooks.Open("file2.xlsx")
wb = ExcelApp.ActiveWorkbook
ws = TryCast(wb.ActiveSheet, Excel.Worksheet)
ws.Cells.Range("A2").Value = 456
End Sub

Sub Procedure33()
Dim ci = AddinModule.CurrentInstance
Dim ExcelApp = ci.ExcelApp
Dim wb As Excel.Workbook = Nothing
Dim ws As Excel.Worksheet = Nothing
ExcelApp.Workbooks.Open("file3.xlsx")
wb = ExcelApp.ActiveWorkbook
ws = TryCast(wb.ActiveSheet, Excel.Worksheet)
ws.Cells.Range("A3").Value = 789
End Sub

Thanks
Albert
Posted 20 Jul, 2016 23:19:37 Top
Andrei Smolin


Add-in Express team


Posts: 18806
 Joined: 2006-05-11
Hello Albert,

Actually, these are the very same scenario: each procedure creates COM object on its own. You need to release a COM object after use. since every procedure creates wb = ExcelApp.ActiveWorkbook, every procedure should release it after use. "After use" in your case is "after retrieving wb.ActiveSheet".

In https://www.add-in-express.com/docs/net-office-tips.php#releasing, we give this rule:

Don't chain COM calls like in OutlookApp.Explorers.Count, ExcelApp.Workbooks[1] (C#) or PowerPointApp.Presentations.Item(1) (VB.NET).


This rule is broken in these constructs "ExcelApp.Workbooks.Open()" and "ws.Cells.Range().Value".

"ws = TryCast(wb.ActiveSheet, Excel.Worksheet)" leaves a COM object non-released if the ActiveSheet property returns a Chart.

Also, Workbooks.Open() returns a COM object.

Below is a variant of rewriting such a procedure: 

Sub Procedure33()
Dim ci = AddinModule.CurrentInstance
Dim ExcelApp = ci.ExcelApp
Dim wbs As Excel.Workbooks = ExcelApp.Workbooks
Dim wb As Excel.Workbook = wbs.Open("file3.xlsx")
Marshal.ReleaseComObject(wbs): wbs = Nothing
dim sheet as Object = wb.ActiveSheet
Marshal.ReleaseComObject(wb): wb = Nothing
if (TypeOf sheet Is Excel.Worksheet) Then
    Dim ws As Excel.Worksheet = CType(wb.ActiveSheet, Excel.Worksheet)
    Dim cells as Excel.Range = ws.Cells
    dim r as Excel.Range = cells.Range("A3")
    Marshal.ReleaseComObject(cells): cells= Nothing
    r.Value = 789
    Marshal.ReleaseComObject(r): r= Nothing
end if
Marshal.ReleaseComObject(sheet ): sheet = Nothing
End Sub



Andrei Smolin
Add-in Express Team Leader
Posted 21 Jul, 2016 01:18:03 Top
Albert Siagian


Guest


Andrei Smolin writes:
"ExcelApp.Workbooks.Open()" and "ws.Cells.Range().Value"


Hello Andrei,

Thanks. I use these quite a lot. Will re-think about it.

On the other hand, will this incorrect use of creating objects and release com objects, causing issue with Visual Studio registering the add-in ? For some random reason and non-replicated steps, Visual Studio crashes and restarting automatically many times. At the bottom left, it says "registering xxx.dll". I always thought this is due to unreleased com objects, but never figure it out why/how it happens.
Posted 21 Jul, 2016 01:36:20 Top
Andrei Smolin


Add-in Express team


Posts: 18806
 Joined: 2006-05-11
Albert Siagian writes:
I always thought this is due to unreleased com objects


This isn't so. When the constructor is run, the Excel object model isn't used. At least, it shouldn't be used.

Move all custom code from the constructor of the add-in module to the AddinInitialize or OnRibbonBeforeCreate event handler. The same applies to initializers of all complex-type variable declared on the class level in the add-in module.


Andrei Smolin
Add-in Express Team Leader
Posted 21 Jul, 2016 01:41:01 Top
Albert Siagian


Guest


Hello Andrei,

By "custom code" in the constructor, do you mean like below (at top of AddinModule.vb) ?

Private test1 as String = "ABC"
Public test2 as Integer = "123"

Regards
Albert
Posted 21 Jul, 2016 01:49:07 Top
Andrei Smolin


Add-in Express team


Posts: 18806
 Joined: 2006-05-11
These are initializers of simple-type variables. Custom code is any code *you* add to the constructor of the add-in module.


Andrei Smolin
Add-in Express Team Leader
Posted 21 Jul, 2016 01:54:42 Top
Albert Siagian


Guest


Ok, will check it more detail. My constructor in add-in module is using default one.

Thanks
Albert
Posted 21 Jul, 2016 02:11:38 Top