Skip to Content
avatar image
Former Member

Macro performance with Excel addin


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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Jul 08, 2015 at 12:34 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.

  • Jul 07, 2015 at 09:25 PM

    Peter, which version of Analysis Office are you using?

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Tammy Powlas

      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