Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Shapes and slow VBA code in Excel 2007

For reasons known only to Microsoft, macros that add and/or format shapes (rectangles, text boxes, etc.)
on worksheets can easily take 10 or 20 times longer in Excel 2007 than in earlier versions.

Fortunately the slow code can be rewritten to achieve acceptable performance in Excel 2007.

What's slow code?

Much of the code that has been written to manipulate shapes works by selecting the shape and then
operating on the "selection". Like this:

Selection.Characters.Text = "ABC"

There was nothing wrong with this kind of code before Excel 2007. After all, the macro recorder produced
code like this and how to write code that addressed shapes directly was not always obvious. And, in any
case, there was little benefit in doing so.

Enter Excel 2007. It's not clear why but this kind of code runs like a turtle in quicksand under Excel 2007.
And the more shapes you're dealing with the slower it goes.

The remedy is to address shapes "directly". Like this:

Set sh = ActiveSheet.Shapes.AddTextBox(...)
sh.DrawingObject.Text = "ABC"

This code runs about 50% slower in Excel 2007 than Excel 2003, which is equal to or better than macro performance generally.

To see this performance penalty yourself and to get a more complete example of formatting shapes directly download The enclosed file, SlowShapesXL2007.xls created by Jim Rech,
is in Excel 97-2003 format so it can be run in Excel 2007 and in earlier versions.

Penalty Ratio from the test code in the workbook