none
Excel 2016 crashes when accessing active chart RRS feed

  • Question

  • VSTO project with simple code:

         private Excel.Workbook lastWorkbook;     

    private void ThisAddIn_Startup(object sender, System.EventArgs e){ var app = Globals.ThisAddIn.Application; app.WorkbookActivate += AppOnWorkbookActivate; } private void AppOnWorkbookActivate(Excel.Workbook wb){ try{ if (lastWorkbook != null){ var chart = lastWorkbook.ActiveChart; } } catch (Exception e){ Console.WriteLine(e); } lastWorkbook = wb; }

    Now do the following:

    1. Run the project in Excel 2016 64bit. You should have one workbook opened.

    2. Go to File-> New and open a second workbook.

    3. Switch to the first workbook and close it.

    4. This causes an exception which is not caught and crashes Excel.

    If you do this in Excel 2010 or Excel 2013 this does not happen. Any workaround? How can I determine if the workbook that was active before the current one (in workbook activate event) was closed so that I can avoid accessing ActiveChart in this case?



    Tuesday, March 1, 2016 10:08 AM

All replies

  • Hi Mitja,

    I think your issue was caused by that when you close first workbook, the lastWorkbook is not released right now, then the second workbook actives, this exception happened. This exception is thrown when the CLR detects that something has gone horribly wrong.

    If you do not need get ActiveChart if the firstworkbook close, you could try to set the lastWorkbook null when you close first workbook. Here is a simple code:

    private Excel.Workbook lastWorkbook;
            private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                var app = Globals.ThisAddIn.Application;
                app.WorkbookActivate += AppOnWorkbookActivate;
                app.WorkbookBeforeClose += App_WorkbookBeforeClose;
            }
            private void App_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel)
            {
                if (lastWorkbook == Wb)
                {
                    lastWorkbook = null;
                }
            }
            private void AppOnWorkbookActivate(Excel.Workbook wb)
            {
                try
                {
                    if (lastWorkbook != null)
                    {
                        var chart = lastWorkbook.ActiveChart;
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                }
                lastWorkbook = wb;
            }

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, March 2, 2016 1:41 AM
  • Workbook before close is not the right event for this since a user can press cancel on the close dialog. The workbook was not closed in that case, but the event already triggered and it would clear the lastWorkbook variable. In this scenario, our logic would not work correctly.

    On top of that, there seems to be a bug with the event triggering of this event in Excel 2016.

    Maybe the Workbook Shutdown event could do the trick.

    Since this is limited to Excel 2016 do you think it will be fixed in the future? Or should we just implement this workaround when our users are running Excel 2016.

    Wednesday, March 2, 2016 7:27 AM
  • Hi Mitja,

    >> Maybe the Workbook Shutdown event could do the trick.

    ThisAddIn_Shutdown would not work. ThisAddIn_Shutdown fires only when add in is shut down, in other words, it only fires when you close all the excel workbook.

    >> On top of that, there seems to be  a bug with the event triggering of this event in Excel 2016

    For this issue, I suggest you submit a feedback.

    For your issue, I think issue was caused by that when you close first workbook, the lastWorkbook is not released right now, then the second workbook actives, this exception happened. Since there is a limit in WorkbookBeforeClose event, I suggest you loop the Globals.ThisAddIn.Application.Workbooks to get the workbook instead of store workbook in a workbook object.

    Here is a simple code:

    private   string wName;
            private void ThisAddIn_Startup(object   sender, System.EventArgs e)
            {
                var app =   Globals.ThisAddIn.Application;
                app.WorkbookActivate +=   AppOnWorkbookActivate;                     
            }       
            private void   AppOnWorkbookActivate(Excel.Workbook wb)
            {
                Excel.Workbooks ws =   Globals.ThisAddIn.Application.Workbooks;
                if (wName != null)
                {
                    foreach (Excel.Workbook w in   ws)
                    {
                        if (w.Name == wName)
                        {
                            var chart =   w.ActiveChart;
                        }
                   }
                }
                wName = wb.Name;            
            }

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Monday, March 7, 2016 8:38 AM
  • Hello Edward.

    With the shutdown event I had the Workbook Shutdown event in mind, not the Addin Shutdown.

    Saving a name is problematic, since (at least in Excel 2016) you can have multiple workbooks with the same name opened. Also using "Save as" or a similar function can cause problems.

    I think I'll be able to find a solution so thanks for your help. Mostly I was just wondering why this happens only in the newest version of Excel.

    Monday, March 7, 2016 8:58 AM
  • Hi Mitja,

    Thanks for more information. If you have a better solution, please feel free to post.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, March 8, 2016 2:12 AM
  • It seems that I was looking at the wrong documentation. Shutdown event is not available in Microsoft.Office.Interop.Excel. So at least for now this is an unsolved issue for us. Did you manage to look at it, is it considered a bug? I guess there must be some differences when compared to other versions that causes it.
    Friday, March 18, 2016 10:34 AM
  • Hi Mitjas,

    I think the time to release workbook object are different in different versions when you close a workbook. As my above reply, I would suggest you check whether the closed workbook object still exist in Workbooks. If you think Workbook.Name do not meet your requirement, I suggest you check other properties like Path.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Saturday, March 19, 2016 5:15 AM