SAP Knowledge Base Article - Public

2669723 - VBA Macro performance is bad when Analysis for Microsoft Office add-in is active

Symptom

When you run VBA Macros in a Analysis for Microsoft Office workbook or in a Excel file (even without any Analysis for Microsoft Office contents or APIs) , the performance is bad. When you deactivate Analysis for Microsoft Office add-in, the performance issue disappears.

Environment

  • SAP BusinessObjects Analysis for Microsoft Office 2.5
  • SAP Analysis for Microsoft Office 2.6
  • SAP Analysis for Microsoft Office 2.7
  • SBOP ANALYSIS MS OFFICE 2.8

Reproducing the Issue

Run VBA Macro in a workbook where Analysis for Microsoft Office add-in is active.

Cause

Sometimes, Analysis for Microsoft Office add-in consumes time when the macro code runs, because it needs to register Excel events,  for example -  sheet change. This means whenever sheet content is changed, the Analysis for Microsoft Office add-in is called to verify whether the changed content is related to Analysis for Microsoft Office add-in. The execution time is nearly zero but there is also some (small) time needed for Excel to pass this information from the VBA context to the Analysis for Microsoft Office add-in. The total time of the registration makes the performance bad.

Resolution

Edit your Macro as follows:

  At the beginning of the Macro:

    Application.EnableEvents = False
    Application.ScreenUpdating = False

  At the end of the Macro:

    Application.EnableEvents = True
    Application.ScreenUpdating = True

Please note the following:

  • This recommended change is valid in almost all cases, except, if you use 'Excel events' within your VBA code. In this case, it is required to have EnableEvents switched on. Please confirm this with your VBA developer.
  • In all cases, the switching off of ScreenUpdating should be possible.

Keywords

slow, AO, Analysis for Excel, Analysis for Office, A4O, AddIn, AddOn, long time , KBA , BI-RA-AO-XLA , Excel Addin , Problem

Product

SAP Analysis for Microsoft Office 2.6 ; SAP Analysis for Microsoft Office 2.7 ; SAP Analysis for Microsoft Office 2.8 ; SAP BusinessObjects Analysis for Microsoft Office 2.5