共用方式為

Considerations for common performance issues with Office Add-ins and Macros in Office 2013

  • 發行項

The intent of this blog post is to address frequent questions that we see comparing performance difference between Office 2010 and 2013, especially by developers who use Office as a development platform via macros using VBA, add-ins, COM add-ins and interop using C++ / a .Net languages such as C#, VB.Net.

Before we compare the performance between versions, it is very important to note that the hardware plays a critical role. So, it is important to try and match the hardware (CPU, RAM, disk type etc...) to make sure the performance issue that is affecting is not due to these changes.

Firstly, let’s take a look at the changes that have been done to Microsoft Office 2013. Secondly, let’s look at frequent issues an Office developer would experience and subsequently with a possible tip / workaround to reduce the performance impact.

  • There are a lot of features that have been added to Microsoft Office 2013 as a whole, which might contribute to the general slowness in some development scenarios. For e.g. invoking a method from Object Model. While every effort has been done to make sure that the performance does not deteriorate drastically, but as one can understand new feature needs more code to be executed which implies slower operation / execution.
  • Microsoft Office 2013 introduced a design change in rendering of UI. Office applications have moved from traditional GDI+ rendering, to DirectX based rendering.
  • Excel 2013 has moved from Multiple Document Interface (MDI) to Single Document Interface (SDI).

   Programming for the Single Document Interface in Excel

The following are few tips / workaround that might help you.

  • Make sure you have updated Office to the latest and greatest as the issue may have been addressed in latest patches.
  • The below article talks about best practices that could be considered for performance issues with Excel VBA.

   Excel VBA Performance Coding Best Practices

  • The below article talks about performance issues arising out of incompatible third party video drivers.

   Performance and display issues in Office 2013 client applications

  • Check “Disable hardware graphics acceleration” check box under File-> Options -> Advanced -> Display section. (See screenshot below).

  

  • Uncheck “Animate controls and elements inside windows” check box under System -> Advanced System Settings -> Advanced -> Performance Options -> Settings. (See screenshot below).

  

  • Using “ScreenUpdating” property appropriately would help to speed up your macro code execution.

   Application.ScreenUpdating Property

  • Using “DoEvents” function appropriately would help in yielding execution so that the operating system can process pending messages/actions.

   DoEvents Function

  • In a scenario where your macro executes many individual copy and paste operation and it takes longer than expected to complete the copy and paste operations, consider setting “AcbControl” registry as explained in the below KB article. This is applicable to Excel 2010 and Excel 2013. Remember to use the appropriate registry key location (14.0 for Excel 2010 and 15.0 for Excel 2013).

   Description of the Office 2010 hotfix package (Mso-x-none.msp): August 13, 2013

  • There is slowness in certain Object Model calls, like Protect and Unprotect of a document.

   Excel macros that protect and unprotect worksheets may run slowly

While comparing performance between Office versions, it is advisable to use similar configuration machines. Adding logs to the application might help in identifying which call(s) take long time for execution.

Hope this information is helpful. I will try to keep this post up-to-date.

Update : Note that these tips/points are also applicable to Office 2016.

Comments

  • Anonymous
    October 08, 2015
    Cool, this is a very useful information.