PRB: Excel is Slow When Entering or Exiting Modal States

Microsoft Excel takes an unusually long amount of time when it enters or exits modal states, such as when it is editing cells or bringing up a modal dialog box.

Due to the data structure that Excel uses to maintain the list of all objects and the way that it communicates with the Visual Basic Editor (VBE) to freeze or unfreeze events for these objects, once the VBE is loaded, then the running time of an Excel modal state change is directly proportional to the product of the number of worksheets and the number of outstanding interfaces.

To work around the problem, minimize the number of outstanding interfaces or worksheets needed.

This behavior is the result of a design limitation in Excel.

When Excel makes a modal state change it notifies the Visual Basic for Applications (VBA) Component manager. The VBA Component manager then iterates all of its objects (such as worksheet project items) and informs the host to freeze or unfreeze the events of those objects.

At each object freeze request, Excel sets a freeze bit for the object but also has to do this for all of the object's children. Because Excel stores the list as a linked list with only next/prev/parent pointers, it requires iterating the entire list of outstanding objects to discern the parent/child relationships. Therefore, the number of iterations is at least the product of the number of worksheets and the number of outstanding objects.

A typical scenario is an Excel automation client that might hold on to hundreds or thousands of Range object references for its implementation. If there is also a large number of worksheets in a workbook, you might expect to see a performance problem in editing cells, in bringing up dialog boxes, or in general switching between modal and non-modal states. A performance problem is obvious if there are 500 worksheets and 10,000 outstanding objects on a 300 MHz computer.

Try to avoid holding on to numerous Excel objects whenever applicable and, in the preceding example, consider storing the addresses of the cells instead or making use of multi-cell ranges.