Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating Shapes
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating Shapes
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 "Christof DB" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating Shapes
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 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 "Christof DB" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel document with shapes on it but the shapes do not print | Excel Worksheet Functions | |||
Naming Auto Shapes and Creating new Shapes | Excel Discussion (Misc queries) | |||
alternative coordinates for creating shapes | Excel Discussion (Misc queries) | |||
When drawing shapes in excel the shapes keep disappearing | Excel Discussion (Misc queries) | |||
Creating shapes from given criteria | Excel Worksheet Functions |