Problem with Blog Sample

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

Problem with Blog Sample
Excel add-in development in Visual Studio: Application and base objects 
Leon Lai Kan




Posts: 200
Joined: 2018-12-20
Hi,

I am playing with this sample:
Excel add-in development in Visual Studio: Application and base objects
https://www.add-in-express.com/creating-addins-blog/2013/09/23/excel-addin-development-application-base-objects/


When I click the Open Existing Workbook button, the program is supposed to open the workbook "C:\Files\MyExcelFile.xlsx" which exists in my directory. Instead, the program crashes. What happens?

Here are the codes for the click event and the sub:


Private Sub btnOpenExistingWorkbook_OnClick(sender As Object, control As IRibbonControl, pressed As Boolean) Handles btnOpenExistingWorkbook.OnClick

        OpenExistingWorkbook("C:FilesMyExcelFile.xlsx")
        'the backslashes don't show up on the screen!

    End Sub



 Private Sub OpenExistingWorkbook(filePath As String)
        Dim wb As Excel.Workbook = Nothing
        Dim wbs As Excel.Workbooks = ExcelApp.Workbooks

        wb = wbs.Open(filePath)
        '---OR----
        'wb = wbs.Add(filePath)

        Marshal.ReleaseComObject(wbs)
        'Marshal.ReleaseComObject(wb)
    End Sub



The error points to this line:
Marshal.ReleaseComObject(ws)
which is found in this sub:
Sub adxExcelEvents_WorkbookBeforeSave

And the error message is:
Object reference not set to an instance of an object.



Thanks
Leon
Posted 15 Jan, 2019 05:35:55 Top
Andrei Smolin


Add-in Express team


Posts: 16568
Joined: 2006-05-11
Hello Leon,

I assume an add-in containing only the two methods above doesn't crash Excel. Correct? If so, the issue is caused by the remaining code in your add-in.

Do you by any chance connect to events of the default workbook (Book1) that Excel opens at startup? If you start Excel, it opens Book1; if, after that, you open an existing workbook without modifying Book1, Excel closes Book1 and opens the specified workbook instead.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 15 Jan, 2019 08:33:00 Top
Leon Lai Kan




Posts: 200
Joined: 2018-12-20
Hi, Andrei

I re-downloaded your sample file, built it, registered it, and ran debugging.
I also unregistered all other COM add-ins in Excel.

Again, I pressed the Open Existing Workbook button. Again the program is blocked at this line:
Marshal.ReleaseComObject(ws)

I tried several times, but got the same results.

Note that I did not modify anything in your sample code, and ran it as it is.

Could you check if the Open Existing Workbook button works at your end?

Best Regards,
Leon

ADDED:
If I disable the 2 lines below (in this Sub: adxExcelEvents_WorkbookBeforeSave) , the program works fine!

'Marshal.ReleaseComObject(ws)
'Marshal.ReleaseComObject(wb)

These 2 lines are in this sub: adxExcelEvents_WorkbookBeforeSave

What has the Sub: adxExcelEvents_WorkbookBeforeSave got to do with my button? Strange!

-------

I also notice that when the Workbook C:\Files\MyExcelFile.xlsx opens, a header is added to its sheet, although I did not press the "Add Company header" button. ??
Posted 16 Jan, 2019 04:08:13 Top
Andrei Smolin


Add-in Express team


Posts: 16568
Joined: 2006-05-11
Hello Leon,

While debugging the issue, I've found these mistakes in the code of the adxExcelEvents_WorkbookBeforeSave method:

- Marshal.ReleaseComObject(wb) - this breaks one of the rules we give in section Releasing COM Objects (see e.g. https://www.add-in-express.com/docs/net-office-tips.php#releasing): Never release COM objects obtained through the parameters of events provided by Add-in Express. Specifically, due to the way the .NET Framewrok deals with COM objects, e.HostObject and wb As Excel.Workbook refer to the same COM object. This is why releasing wb breaks the rule.

- TryCast(wb.Worksheets, Excel.Worksheets) - this returns Nothing (null in C#); in other words, the Excel.Workbook.Worksheets property returns an object which IS NOT of the Excel.Worksheets type. This code line should be replaced: instead of Dim ws As Excel.Worksheets = TryCast(wb.Worksheets, Excel.Worksheets), use Dim ws As Excel.Sheets = wb.Worksheets.

- the actual exception occurred on this line: Marshal.ReleaseComObject(ws). All calls to Marshal.ReleaseComObject() should be modified as follows: replace Marshal.ReleaseComObject(ws) with If ws IsNot Nothing Then Marshal.ReleaseComObject(ws). This prevents exceptions that occur due to incorrect casting etc. (In C# terms you should write: if (ws != null) Marshal.ReleaseComObject(ws)).

The last item also reminds me about our recommendation: we recommend using the following construct when releasing COM objects:
- VB.NET: If ws IsNot Nothing Then Marshal.ReleaseComObject(ws) : ws = Nothing
- C#: if (ws != null) Marshal.ReleaseComObject(ws); ws = null;

The idea behind the extra statement that nullifies the variable is to prevent an exception that occurs if you mistakenly re-use the variable. The exception is "COM object that has been separated from its underlying RCW cannot be used". It reflects the way COM objects are maintained in .NET. You can find background details in https://www.add-in-express.com/creating-addins-blog/2011/11/04/why-doesnt-excel-quit/.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 16 Jan, 2019 07:59:19 Top
Leon Lai Kan




Posts: 200
Joined: 2018-12-20
Wow! What a worry I have given you!

But why did adxExcelEvents_WorkbookBeforeSave get fired?

Thanks for your effort.

Best Regards,
Leon
Posted 16 Jan, 2019 08:06:52 Top
Andrei Smolin


Add-in Express team


Posts: 16568
Joined: 2006-05-11
No problem! That's my job and I love it!

Leon Lai Kan writes:
But why did adxExcelEvents_WorkbookBeforeSave get fired?


I don't have an idea. Excel raises this event basing on its internal logic; Add-in Express is sort of a wrapper in this case: it just informs the add-in about events.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 16 Jan, 2019 08:28:02 Top