Slow (very slow) 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:

    ActiveSheet.Shapes.AddTextBox(...).Select
    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 SlowShapesXL2007.zip. 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





Code run from a shape can crash Excel 2007

Note:This problem is fixed in Office 2007 SP2

If a macro that closes an Excel workbook is assigned to a shape, clicking it in Excel 2007 will cause a crash.
Note: If you have more workbooks open you not have this problem.
Note: It seems that If Office 2003 is also installed on the machine you not have this problem.

To see this, copy the code below into a standard module. Then add a shape, a rectangle for example, to worksheet, right click it, and assign this macro to it.

Sub CloseWithoutSaving()
    Dim Response

    Response = MsgBox("Are you sure you want to close the workbook without saving," _
                      & vbNewLine & _
                      "Click No and use the Save button to save your changes" _
                      , vbYesNo, "My test Macro")

    If Response = vbYes Then
        ActiveWorkbook.Close SaveChanges:=False
    Else
        'Do nothing
    End If

End Sub

Note: This code runs correctly in Excel 97-2003.

The moral is that you should consider using a Forms or ActiveX button rather than a shape if there is any possibility Excel 2007 will be used. Those controls do not have this problem.


But there is a Workaround :

Copy this macro also in your module and right click on the shape and assign this macro to it.

Sub Assign_This_Macro_To_The_Shape()
    Application.OnTime Now + TimeSerial(0, 0, 1), _
                       ThisWorkbook.Name & "!CloseWithoutSaving"
End Sub

When you click on the Shape it will wait for one second and then run the macro with the close code.
Thanks to Dave Peterson for this tip.