none
How to best manage ribbon UI performance issues in the Excel 2013 and Excel 2016 single document interface (SDI)? RRS feed

  • General discussion

  • My company develops an Excel add-in that adds 3 new tabs to Excel, and many controls within these tabs. Prior to the introduction of the single document interface (SDI) in Excel 2013 - i.e. in Excel 2007 and Excel 2010 - this had no noticeable performance impact on Excel.

    However, since the introduction of the SDI, it's clear that the rendering of the ribbon is significantly slower, and gets exponentially slower than pre-SDI versions of Excel as more workbooks are opened, I'm assuming because it's rendering each workbook window separately (as opposed to Excel 2007/10 which was always a single window housing all open workbooks).

    The extent of this performance issue can be seen using a simple example macro which opens 25 empty workbooks and then closes them, as follows:

    Option Explicit
    
    Private Declare Function GetTickCount Lib "kernel32" () As Long
    
    Sub Test_Open_Close()
    
        Dim lngStart As Long
        Dim lngDuration As Long
    
        lngStart = GetTickCount
        Test_Open
        Test_Close
        lngDuration = GetTickCount - lngStart
    
        Call MsgBox("Duration: " & lngDuration & "ms", vbInformation)
    
    End Sub
    
    Private Sub Test_Open()
    
        Dim nWorkbookNumber As Long
    
        For nWorkbookNumber = 1 To 25
            Application.Workbooks.Add
        Next nWorkbookNumber
    
    End Sub
    
    Private Sub Test_Close()
    
        Dim nWorkbookNumber As Long
    
        For nWorkbookNumber = 1 To 25
            Call Application.ActiveWorkbook.Close
        Next nWorkbookNumber
    
    End Sub

    If this code is run in a new instance of Excel 2007 or Excel 2010 (i.e. pre-SDI), it takes around 1.5 seconds to run. However, in Excel 2013 or Excel 2016 (i.e. SDI), it takes more than 8 seconds to run. And this is without any ribbon customizations having been made.

    When our company's add-in is then loaded, with our 3 custom tabs, the macro takes ony 0.1 seconds longer to run in Excel 2007 or Excel 2010 (i.e. pre-SDI) - i.e. it takes around 1.6 seconds, whereas it takes over 33 seconds to run in Excel 2013 or Excel 2016 (i.e. SDI).

    This performance impact of the SDI is debilitating for our users, and extremely frustrating for us.

    Has anyone else come across this problem and found a solution or workaround?

    Saturday, May 19, 2018 4:28 AM

All replies

  • The extent of this performance issue can be seen using a simple example macro which opens 25 empty workbooks and then closes them

    When our company's add-in is then loaded, with our 3 custom tabs, the macro takes ony 0.1 seconds longer to run in Excel 2007 or Excel 2010 (i.e. pre-SDI) - i.e. it takes around 1.6 seconds, whereas it takes over 33 seconds to run in Excel 2013 or Excel 2016 (i.e. SDI).

    It is as it is.

    Furthermore, I guess the slow speed is related to other code that is executed, therefore add this usual lines at the beginning of the main macro:

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False


    Without this lines, your code need on my Office 2016 machine around 11 seconds. With this lines around 4 seconds (Office 2010 needs around 1 second).

    But what has this to do with the ribbon?

    There are a lot more things executed in the background that we do not see. And there is no chance to change the behavior.

    I know that the VBA callback routines can be a bottleneck and slow down the speed significantly, therefore your callback routines must be as fast as possible.

    Maybe you are interested how I manage them, here is my "from developer to developers" addin
    https://www.dropbox.com/s/eow60utqq4nf1gr/AK.zip?dl=1

    In general: If your ribbon tab is too slow, switch to the Home tab before you execute extensive operations.

    Andreas.

    Saturday, May 19, 2018 8:16 AM
  • The following functionality can usually be turned off while your code is executed:



    • Application.ScreenUpdating

      Turn off screen updating. If Application.ScreenUpdating is set to False, Excel does not redraw the screen. While your code runs, the screen updates quickly, and it is usually not necessary for the user to see each update. Updating the screen once, after the code executes, improves performance.

    • Application.DisplayStatusBar

      Turn off the status bar. If Application.DisplayStatusBar is set to False, Excel does not display the status bar. The status bar setting is separate from the screen updating setting so that you can still display the status of the current operation even while the screen is not updating. However, if you do not need to display the status of every operation, turning off the status bar while your code runs also improves performance.

    • Application.Calculation

      Switch to manual calculation. If Application.Calculation is set to xlCalculationManual, Excel only calculates the workbook when the user explicitly initiates the calculation. In automatic calculation mode, Excel determines when to calculate. For example, every time a cell value that is related to a formula changes, Excel recalculates the formula. If you switch the calculation mode to manual, you can wait until all the cells associated with the formula are updated before recalculating the workbook. By only recalculating the workbook when necessary while your code runs, you can improve performance.

    • Application.EnableEvents

      Turn off events. If Application.EnableEvents is set to False, Excel does not raise events. If there are add-ins listening for Excel events, those add-ins consume resources on the computer as they record the events. If it is not necessary for the add-in to record the events that occur while your code runs, turning off events improves performance.

    • ActiveSheet.DisplayPageBreaks

      Turn off page breaks. If ActiveSheet.DisplayPageBreaks is set to False, Excel does not display page breaks. It is not necessary to recalculate page breaks while your code runs, and calculating the page breaks after the code executes improves performance.




    Read more about that in the Excel performance: Tips for optimizing performance obstructions article.



    Also I'd recommend releasing underlying COM objects. All Excel add-ins should systematically release their references to Excel objects when they are no longer needed. Failing to systematically release reference to Excel objects can prevent Microsoft Office Excel from shutting down properly.



    Use System.Runtime.InteropServices.Marshal.ReleaseComObject to release an Excel object when you have finished using it. Then set a variable to Nothing in Visual Basic (null in C#) to release the reference to the object.


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Monday, May 28, 2018 12:39 AM