Application.Undo makes Excel hang

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

Application.Undo makes Excel hang
 
OliverM




Posts: 144
Joined: 2015-08-13
In my add-in I have references to workbooks, sheets and cells. The classes holding the references all implement the IDisposable interface. In the dispose event I release all references by calling
Marshal.ReleaseComObject(WhatEverReference)

This always worked without any problem.
Lately I introduced a new feature which utilizes CurrentInstance.ExcelApp.Undo(). All over sudden Excel hangs on program exit if Undo() is called at runtime but finalizes like usual if Undo() is not called.
I have no idea which additional reference I need to release in order to bring everything back to normal.
Kind regards
Oliver
Posted 28 Apr, 2017 09:58:26 Top
Andrei Smolin


Add-in Express team


Posts: 14094
Joined: 2006-05-11
Hello Oliver,

You can comment out blocks of your code to locate the issue.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 28 Apr, 2017 10:20:05 Top
OliverM




Posts: 144
Joined: 2015-08-13
This is not easy as there are lots of cascading dependencies. Hence simply commenting out some code will make the app break.

I tried to boil it down by:
- Opening a single workbook with a single sheet (2 references)
- Adding a reference to cell A1 (3 references)

Test case #1
Move cell A1 to somewhere on the sheet and call ExcelApp.Undo() then close Excel => result is hanging Excel
Test case #2
Do not move cell A1 and just close the book => result is clean exit
Kind regards
Oliver
Posted 28 Apr, 2017 10:30:47 Top
Andrei Smolin


Add-in Express team


Posts: 14094
Joined: 2006-05-11
I'll test this scenario on Monday.

Have a great weekend.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 28 Apr, 2017 10:40:22 Top
Andrei Smolin


Add-in Express team


Posts: 14094
Joined: 2006-05-11
Hello Oliver,

I can't reproduce the issue using a sample add-in showing two Ribbon buttons. The buttons are handled as follows:

        Excel.Workbook wbk = null;
        Excel.Worksheet wks = null;
        Excel.Range rng = null;
        private void adxRibbonButton1_OnClick(object sender, IRibbonControl control, bool pressed) {
            wbk = ExcelApp.ActiveWorkbook;
            wks = wbk.ActiveSheet as Excel.Worksheet;
            rng = wks.Range["a1"];
        }
        private void adxRibbonButton2_OnClick(object sender, IRibbonControl control, bool pressed) {
            Marshal.ReleaseComObject(wbk); wbk = null;
            Marshal.ReleaseComObject(wks); wks = null;
            Marshal.ReleaseComObject(rng); rng = null;
        }


I can't make Excel hang. I open a workbook, modify A1, click the first button, move the cell somewhere, click Undo, click the second button, close Excel, confirm saving changes. Excel is 2016 16.0.7803.1018 32 bit

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 01 May, 2017 07:27:12 Top
OliverM




Posts: 144
Joined: 2015-08-13
Hi Andrei,

Thank you very much for testing the scenario. As it turns out the hanging Excel process was caused by a consumer thread making an undo call directly into the Excel object model. Switching to an action delegate and marshaling the undo call back into the Excel UI main thread solved it.
As it might by helpful for other guys here is my code.


// Add to AddinModule
public Dispatcher Dispatcher { get { return Dispatcher.CurrentDispatcher; } }

// Helper routine in executing class
private void DoUndo()
{
  Action action = delegate
  {
     AddinModule.CurrentInstance.ExcelApp.EnableEvents = false;
     AddinModule.CurrentInstance.ExcelApp.Undo();
     AddinModule.CurrentInstance.ExcelApp.EnableEvents = true;
  };

  AddinModule.CurrentInstance.Dispatcher.Invoke(DispatcherPriority.Normal, action);
        
}
Kind regards
Oliver
Posted 02 May, 2017 02:42:20 Top
Andrei Smolin


Add-in Express team


Posts: 14094
Joined: 2006-05-11
Thank you!

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 02 May, 2017 03:43:22 Top