Macro performance with Excel addin

[Former Member]

Hi,

I've recently started using the Excel addin (Analysis) to refresh my data and then using a few VBA routines to do some detailed data crunching and updating of a dashboard. When I run these macros in normal Excel mode then it takes around 2 seconds per routine but when I run the macros under the Analysis addin then the same routine takes almost a minute each.

Any reason why or settings I need to adjust to speed this up? The performance to refresh the data is fine, it's really only the Macro performance once they are running under the addin.

thanks in advance for any input provided.

Peter

[Former Member · Accepted solution]

You Need to turn off Events before your code runs:

With Application                                

.Calculation = xlCalculationManual      

.EnableEvents = False      

.ScreenUpdating = False  

End With

Afterwards enable them.

Best regards,

Victor

[Former Member · Accepted solution]

Yes many thanks Victor. spot on, I literally did that as your message came!

The main part that did the trick is "Application.EnableEvents = False". One of my colleagues said that BO analysis has some embedded events that introduce additional logic so switching that off did the trick.

thanks for responding.

[TammyPowlas]

Peter, which version of Analysis Office are you using?

[Former Member]

Version 1.4.10.3290

[TammyPowlas]

Would it be possible to upgrade to at least SP12 for 1.4?  I know that SP had some performance improvements; then report back on your performance.

If the performance is still not good then please report back and share some macro code on what you think is taking so long.

[Former Member]

I haven't managed to find a link to that patch. Would you be able to provide the link to it please?

The macro I've created is simple enough and it's only looking for specific data then reporting back to a dashboard. Like I said in the first message, in normal Excel mode it takes literally about 2 seconds to run and with the Addin it takes more than 45 seconds.

[TammyPowlas]

Peter - go to Software Downloads | SAP Support Portal

Select support packages and patches

I select A-Z index, A for Analysis A- Z Index | SAP Support Portal

Select SBOP Analysis Office

Keep drilling down

SP12 is there:

[Former Member]

Hello Tammy,

Thanks for your help but Victor's solution was the correct one. Apparently BO Analysis has extra embedded events that introduce additional logic in the Macro so switching that off did the trick.

I added Application.EnableEvents = False at the beginning of my code and Application.EnableEvents = true at the end of the coding. This did the trick and sped up the macro dramatically.

regards

Peter