creating Shapes

  • Thread starter Thread starter Christof DB
  • Start date Start date
C

Christof DB

I have a macro which first removes all shapes of a
workbnook and then creates a set of shapes (10 to 300)
The removal is very fast: I create a shaperange with the
ones to delete and then delete them all at once.

Creating the new ones however takes quite a while. I
suspect it would be faster if I first build all the
shapes in memory, as a datastructure, and then add them
all at once to the worksheet.

Problem1: how to create a shape which is not part yet of
a worksheet
Problem2: if that is not possible, then what suggestion
is there to speed it up?

Thanks in advance
Christof
 
Christof,

As far as I know, there is no way to create a shape without creating it on
the sheet. What code are you using to create the shapes? I ran the following
code to create 300 rectangles, and it ran in less than 1 second.

Dim N As Long
Dim WS As Worksheet
Set WS = ActiveSheet
Application.ScreenUpdating = False
For N = 1 To 300
With Cells(N, 1)
WS.Shapes.AddShape msoShapeRectangle, .Left, .Top, .Width, .Height
End With
Next N
Application.ScreenUpdating = True


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Chip,

you are right. I use similar code and have executed the
shape creation code seperately, and generating 500
rectangles takes typically 0,185s according to the timer

I guess it must be the overhead which is killing the
performance.

while creating the shapes I made a collection of objects
referring to the shapes for later reference in my
datastructure. I've seen that doing that is quiet slow :
1 of my macro's generates a complex datastructure of
about 1500 objects of 7 types of classes, and it takes
nearly a second!

I'll go and dig there first and come back if I have
another query.

Thanks a lot,

Christof
-----Original Message-----
Christof,

As far as I know, there is no way to create a shape without creating it on
the sheet. What code are you using to create the shapes? I ran the following
code to create 300 rectangles, and it ran in less than 1 second.

Dim N As Long
Dim WS As Worksheet
Set WS = ActiveSheet
Application.ScreenUpdating = False
For N = 1 To 300
With Cells(N, 1)
WS.Shapes.AddShape
Click to expand...
msoShapeRectangle, .Left, .Top, .Width, .Height
 
Back
Top