on 07-07-2015 10:22 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
Peter, which version of Analysis Office are you using?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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:
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
User | Count |
---|---|
82 | |
10 | |
10 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.