Is there some way to improve this process?
Maybe. I recorded a macro while adding a text box to a sheet. This is
basically what it boiled down to:
Activesheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 25.5, 2,
53.25, 17.25).Select
When I called basically the same macro in a loop 250 time it took 22
seconds.
The .Select is something Excel adds. Recorded code then goes on and does
other things with the 'Selection'. Much to my surprise when I removed it
the macro took .1 of a second. Yeah, 1/10 second.
So the selecting appears to be the problem. If your macro does any
selecting see if you can get rid of it. It's not always easy because
recorded code does the 'other things' with the Selection and finding the
right object and method to replace it with is not always obvious.
Sub TimeTextboxAdd()
Dim Counter As Long
Dim StTime As Double
ActiveSheet.TextBoxes.Delete
StTime = Timer
Application.ScreenUpdating = False
For Counter = 1 To 500 Step 2
AddTextBox Cells(Counter, 1).Top + 1
Next
MsgBox Timer - StTime
End Sub
Sub AddTextBox(Top As Double)
' 22 seconds
ActiveSheet.Shapes.AddTextBox(msoTextOrientationHorizontal, 25.5, Top,
53.25, 17.25).Select
' .01 seconds
'ActiveSheet.Shapes.AddTextBox msoTextOrientationHorizontal, 25.5, Top,
53.25, 17.25
End Sub
--
Jim
|I have a macro that draws objects on the worksheet based on data contained
in
| the cells. In Excel 2003, with a worksheet of 500 lines of data, this
macro
| ran in about 15 seconds. In 2007, it takes upwards of 15 minutes. As the
| macro works down the sheet, the process of drawing my triangles and
diamonds
| gets slower and slower. It seems like Excel loses processing speed as
more
| and more objects are drawn on the page.
|
| Is there some way to improve this process?
