PRB: Excel is Slow When Entering or Exiting Modal States (277688)

MORE INFORMATION

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.