 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
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.
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
