Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
' encapsulating all COM references in the test methods allows them to go out of scope
' when the method ends and makes them eligible for garbage colllection
test()
testFail()
' watch the 2 Excel instances disappear from TM after the msgbox is closed
' running the GC releases all the COM references allowing Excel to shutdown
ReleaseCollectableCOMObjects()
End Sub
Sub test()
Dim app As New Excel.Application
app.DisplayAlerts = False
Try
' generate a 100 Range references
' oh-no four dots breaking the 1 or is the 2 dot rule
For Each cell As Excel.Range In DirectCast(app.Workbooks.Add.Worksheets(1), Excel.Worksheet).Range("A1:A100")
cell.Value = 1
Next
Catch ex As Exception
MsgBox(ex.Message)
Finally
app.Workbooks.Close()
app.Quit()
End Try
End Sub
Sub testFail()
Dim app As New Excel.Application
app.DisplayAlerts = False
Try
' there is no named range fred, so Excel will throw an error
For Each cell As Excel.Range In DirectCast(app.Workbooks.Add.Worksheets(1), Excel.Worksheet).Range("fred")
cell.Value = 1
Next
Catch ex As Exception
MsgBox(ex.Message)
Finally
app.Workbooks.Close()
app.Quit()
End Try
End Sub
Private Sub ReleaseCollectableCOMObjects()
' ref: Marshal.AreComObjectsAvailableForCleanup Method
' [url]https://msdn.microsoft.com/en-us/library/system.runtime.interopservices.marshal.arecomobjectsavailableforcleanup(v=vs.110).aspx[/url]
Do
GC.Collect()
GC.WaitForPendingFinalizers()
Loop While System.Runtime.InteropServices.Marshal.AreComObjectsAvailableForCleanup
End Sub
End Class
