Add-in Express causing performance issue with VBA code

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

Add-in Express causing performance issue with VBA code
 
Posts 1 - 10 of 13
First | Prev. | 1 2 | Next | Last 
Amit B


Guest


We have a production user who has reported that after installing our addin, time taken by some of his macros are doubled from 1 min to 2 mins.
After analyzing the code, it seems that none of our code was executing when user ran the macro.

In order to reproduce the problem, I have created a blank addin and a very small xlsm file with a very basic macro. I have attached a download link of these files below. There are 2 components in the zip attachment -
1. test.xlsm has a button which is updating a value of cell A1 500k times
2. A test addin which has no custom code. It is blank addin generated from a template and there is no subscription of any excel events.

You can see that when add-in is enabled, the vba button handler takes 20 sec to execute, and when addin is off then same handler code takes half the time i.e. 10 sec approx.

I think that there is some code issue with addin-express library where it is processing some excel events which are not subscribed by addin.

Please consider this issue on priority


https://apparity.sharefile.com/d-s35b8152f8434050a
Posted 24 Feb, 2017 13:38:42 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Amit,

Thank you sending us the files.

If you don't need to handle Excel events, you can drop an Excel Events component (ADXExcelAppEvents) onto the add-in module and set ADXExcelAppEvents.Enabled=false. On my side, this speeds up the macro significantly.


Andrei Smolin
Add-in Express Team Leader
Posted 27 Feb, 2017 07:41:46 Top
Amit B


Guest


Hello Andrei,

In the actual addin, we are handling excel events. So we can't set the excel app event to false. What other options do we have?

FYI the sample xlsm file which I have attached is just for demonstration purpose. I have number of files from different users where similar problem exist.
Posted 27 Feb, 2017 09:35:14 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Amit,

Another way to speed up the macro is to set Application.EnableEvents = false before you run the loop and set Application.EnableEvents = true after.


Andrei Smolin
Add-in Express Team Leader
Posted 27 Feb, 2017 09:52:18 Top
Amit B


Guest


We already tried this Andrei and know that this will work.

We have 2 issues here -
1. These are actual production files from customers which we don't own. So we can't change that
2. We don't know how many files have these issues. It is impossible to change all the files.

As I said earlier, this loop is just an example. Many excel users perform cell writing as a very basic operation. Generally users copies data from one file and paste it into another cell-by-cell.
Looking at the complexity of excel macros, we can't instruct users to change their files.
Posted 27 Feb, 2017 10:00:30 Top
Marcus Datascout


Guest


I'm sure you already know copying-and-pasting a lot of data cell-by-cell via VBA.

Can the macros be modified to populate a Variant array first and then transfer the array to the target range once?
This would minimise calls to Excel's object hierarchy (Range object).

Without having seen the code, I have seen plenty of end user created macros which copy-and-paste like this:

Range("A1").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste

Where the source and target cells are actually selected, rather than:

Range("A1").Copy Range("B1")

or

Range("B1").Value = Range("A1").Value

Is this a contributing factor?

Another approach (that may or may not be viable given your circumstances).
Load the required data from the customer spreadsheets into a central database.
This makes it simpler to pull just the data you need in a single execution in your target spreadsheet.
Posted 27 Feb, 2017 11:12:37 Top
Amit B


Guest


Hi Marcus Datascout,

As I mentioned earlier we don't own these spreadsheets. We cannot change the macro code.

Solution has to be on addin side.
Posted 27 Feb, 2017 11:19:02 Top
Marcus from London


Guest


Sorry, I misunderstood that it was your users' macros copy-pasting data from your customers spreadsheets.

In that case the second suggestion may still be viable.

It's possible to extract the data from the source spreadsheets without opening them and loading into a central database or spreadsheet. This you would have control over.

Understandably this will be easier if the customer spreadsheets have a consistent layout/structure.
Posted 27 Feb, 2017 12:09:05 Top
Amit B


Guest


Hi Marcus from London,

This is our plugin model -

We deploy the addin on customers machine, and on excel events like before save, after save, before close etc. we perform some operations. The problem which was reported by customers is that by just deploying the addin causes slowness in the macro execution, and their day-to-day work is suffering. What I prove in the POC (attached in first post) was that even if no excel event is subscribed then also adx is causing slowness.

We cannot ask customer to change their spreadsheets. This is no an option at all.

The other approach which you are suggesting doesn't fall into product model. We have a well established enterprise product where we only process those spreadsheet in which we are interested. We cannot extract data from spreadsheets the way you described.

The answer which we want is this -
In the sample application which I have attached, there is no event which I subscribed from adx. Then why adx is causing slowness.
Are you taking time in processing something which addin is not interested in? like some sheet(cell) change event etc.?
Is there performance problem in your code?

Thanks,
Posted 27 Feb, 2017 12:30:05 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Amit,

There's no radical solution of this problem on the Add-in Express side. Since we have two programs that handle Excel events, it is quite natural that two of the programs works slower together than each of them separately.

I suggest that your add-in provides 2 Ribbon buttons setting Application.EnableEvents to false and true.

I've created this Excel add-in: 

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
    Excel.Application eventClass = this.Application;
    eventClass.AfterCalculate += EventClass_AfterCalculate;
    eventClass.SheetCalculate += EventClass_SheetCalculate;
    eventClass.SheetChange += EventClass_SheetChange;
}

private void ThisAddIn_Shutdown(object sender, System.EventArgs e) {
}

DummyClass dummy = null;

void EventClass_AfterCalculate() {
    if (dummy != null)
        dummy.DummyMethod();
}

void EventClass_SheetCalculate(object sheet) {
    if (dummy != null) dummy.DummyMethod();
    MyGlobals.ReleaseComObject(sheet);
}

void EventClass_SheetChange(object sheet, object range) {
    if (dummy != null) dummy.DummyMethod();
    MyGlobals.ReleaseComObject(sheet); MyGlobals.ReleaseComObject(range);
}


The DummyMethod does nothing; moreover, it isn't called since the dummy variable is set to null - this imitates the ADXExcelAppEvents component missing on the module. MyGlobals.ReleaseComObject() just releases the passed variable wrapping the release in a try/catch block.

I register the add-in and reproduce the very same issue. Moreover, I get the same issue if I comment out the code within each of the three event handlers above!


Andrei Smolin
Add-in Express Team Leader
Posted 28 Feb, 2017 05:12:45 Top

Posts 1 - 10 of 13
First | Prev. | 1 2 | Next | Last