Performance difference when calling code from event vs ribbon button click

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

Performance difference when calling code from event vs ribbon button click
 
Pierre Alain Carrier




Posts: 36
Joined: 2011-12-22
Hello,

We have a piece of code that we run either on the Application Sheet_Changed event or when the user presses a button on our custom ribbon tab. It is the exact same call in both instance, but the time to finish is very different:

Ribbon button: 55 seconds
Changed event: 3 seconds

As you can see, this is a huge difference. Does anyone know what could be causing this and how we can fix it?
Posted 16 Aug, 2020 14:30:29 Top
Andrei Smolin


Add-in Express team


Posts: 17361
Joined: 2006-05-11
Hello Pierre,

Please provide that piece of code.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 17 Aug, 2020 00:50:16 Top
Pierre Alain Carrier




Posts: 36
Joined: 2011-12-22
The actual code is quite complex, but the basic call for the ribbon is below. We have the exact same thing in the Sheet_Changed event.

Note that for the event, we have our own (old) event sink and we are not using the Add-in Express Application event at this point. However, that's the code that's fast, so I don't think it's an issue.


private void btnUpdateWorksheet_OnClick(object sender, IRibbonControl control, bool pressed)
        {
            try
            {
                DoSomeWork();
            }
            catch (Exception ex)
            {
                // Error handling
            }
        }


Running a profiler show that the exact same calls are made from that point on, but the COM interop calls into Excel take much longer.

I will try to create a new project and see if I can replicate.
Posted 17 Aug, 2020 08:29:30 Top
Pierre Alain Carrier




Posts: 36
Joined: 2011-12-22
I tried to create another project, got the ribbon working, but I cannot get the Excel Application events to fire.

I added the adxExcelAppEvents component in the designer and set up the SheetChange event, but it never gets fired. Did I forget to do somehting? The ribbon shows up properly and the button click works fine.
Posted 17 Aug, 2020 09:57:45 Top
Andrei Smolin


Add-in Express team


Posts: 17361
Joined: 2006-05-11
Hello Pierre,

You can send that project to the support email address; find it in {Add-in Express installation folder}\readme.txt; please make sure your email contains a link to this topic.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 18 Aug, 2020 02:37:58 Top
Andrei Smolin


Add-in Express team


Posts: 17361
Joined: 2006-05-11
Hello Pierre,

The DoSomeWork() method modifies the sheet, this raises the SheetChange event which calls DoSomeWork() again. This creates an endless loop which crashes Excel due to stack overflow. You can use a flag to prevent calling DoSomeWork() when in the SheetChange event.

===
Showing a message box and then hiding it deactivates and then activates the underlying Office window. This produces a flow of events that "spoil the picture". To avoid side effects, you can create debug messages using System.Diagnostics.Debug.WriteLine() or System.Diagnostics.Trace.WriteLine(). You collect the messages at run time using the DebugView utility; download it at http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx
===

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 18 Aug, 2020 10:45:13 Top
Pierre Alain Carrier




Posts: 36
Joined: 2011-12-22
Yes. In our actual code we disable the raising of event.

My current problem is that SheetChange does not get called at all.
Posted 18 Aug, 2020 10:53:29 Top
Andrei Smolin


Add-in Express team


Posts: 17361
Joined: 2006-05-11
Do you have an extra EXCEL.EXE in processes? Try to restart the machine.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 18 Aug, 2020 10:54:33 Top
Pierre Alain Carrier




Posts: 36
Joined: 2011-12-22
No other Excel process. I rebooted to test. Same thing. No Excel application events fire.
Posted 18 Aug, 2020 17:54:37 Top
Andrei Smolin


Add-in Express team


Posts: 17361
Joined: 2006-05-11
Hello Pierre,

Turn off all COM add-ins. Unregister your add-in and register it again. Make sure the path to adxloader[64].dll is correct: open File | Options | Add-ins, select your add-in under "Active application add-ins", see the Location field below.

Also make sure you do not use Office from Microsoft Store, see https://www.add-in-express.com/creating-addins-blog/2019/02/27/office-from-store-issues/.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 19 Aug, 2020 04:40:37 Top