Constructing A Shaperange Collection For Dynamically Generated Charts

tomsoyer · Apr 8th 2007

Hi,

I have a question about how to construct ShapeRange collection objects for dynamic shapes. I have a set of dynamically generated charts that I would like to group some of them together via VBA. This means that I need to construct a ShapeRange collection object dynamically. Unfortunately, the example provided by Microsoft only deals with static shapes, i.e., the number of shapes to be grouped and their names are known ahead of time. Below is the example from Microsoft:

----------------------------Microsoft Example--------------------------
The following example constructs a shape range that contains the shapes named "Big Star" and "Little Star" on myDocument and applies a gradient fill to them.

Code

Set myDocument = Worksheets(1)
Set myRange = myDocument.Shapes.Range(Array("Big Star", "Little Star"))
myRange.Fill.PresetGradient _
    msoGradientHorizontal, 1, msoGradientBrass

------------------------------End Example-----------------------------

The example above works for static shapes with predefined names. For dynamic shapes where their names are generated dynamically, one would think that replacing "Big Star", "Little Star" with a variable would do the trick, but it does not. For example the following code would generate an error message (shape not found):

Code

Dim strShapeNames as string
Set myDocument = Worksheets(1)
strShapeNames="""Big Star"", ""Little Star"""
Set myRange = myDocument.Shapes.Range(Array(strShapeNames))

Does this mean it is not possible to group dynamically generated charts? Has anyone tried to construct ShapeRange objects for dynamically generated charts?

Thanks,

Tom

Anonymous · Apr 8th 2007

Re: Constructing A Shaperange Collection For Dynamically Generated Charts

Welcome to the forum. However please read the rules and use code tags for VBA. I have added them for you this time, but normally the post would be deleted.

**Andy Pope** · Apr 8th 2007

Re: Constructing A Shaperange Collection For Dynamically Generated Charts

Code

Dim vntShapeName(1)
Dim myDocument As Worksheet
Dim myRange As ShapeRange
Set myDocument = Worksheets(1)
vntShapeName(0) = "Big Star"
vntShapeName(1) = "Little Star"
Set myRange = myDocument.Shapes.Range(vntShapeName)

tomsoyer · Apr 8th 2007

Re: Constructing A Shaperange Collection For Dynamically Generated Charts

Thanks Andy! Your code works.

I have a follow up question. I noticed that the following code, although similar to yours, does not work:

Code

Dim vntShapeName as Variant
Dim arrShapeName(1) as String 
Dim myDocument As Worksheet 
Dim myRange As ShapeRange 
 
Set myDocument = Worksheets(1) 
arrShapeName(0) = "Big Star" 
arrShapeName(1) = "Little Star" 
vntShapeName=arrShapeName
Set myRange = myDocument.Shapes.Range(vntShapeName)

According to Microsoft documentation, a variant of array and an array of variant are accessed the same way. So I am wondering why in this case one works and the other does not? Any idea?

Thanks,

Tom

**Andy Pope** · Apr 9th 2007

Re: Constructing A Shaperange Collection For Dynamically Generated Charts

I have no idea why one works and the other does not.

overdrive · Apr 6th 2021

Quote from tomsoyer
Re: Constructing A Shaperange Collection For Dynamically Generated Charts

Thanks Andy! Your code works.

I have a follow up question. I noticed that the following code, although similar to yours, does not work:
Code
Dim vntShapeName as Variant
Dim arrShapeName(1) as String 
Dim myDocument As Worksheet 
Dim myRange As ShapeRange 
 
Set myDocument = Worksheets(1) 
arrShapeName(0) = "Big Star" 
arrShapeName(1) = "Little Star" 
vntShapeName=arrShapeName
Set myRange = myDocument.Shapes.Range(vntShapeName)
According to Microsoft documentation, a variant of array and an array of variant are accessed the same way. So I am wondering why in this case one works and the other does not? Any idea?

Thanks,

Tom
Display More

Sorry to relive such an old issue but wanted to explain why the code is invalid. In that code there are one implicit error that does not let debugging of shadow error.

According to Microsoft documentation, the necessary argument for Range from the Shapes collection requires an array (of indices or text). Therefore, it must be explicitly declared as such, so Dim vntShapeName as Variant must become Dim vntShapeName() as Variant. https://docs.microsoft.com/en-…ba/api/Excel.Shapes.Range

Now, if you execute debugging then VBE will info about of shadow error, you can not directly assign to matrix. This code is correct:

Code

Sub test()
 Dim vntShapeName() As Variant
 Dim arrShapeName(1) As String
 Dim myDocument As Worksheet
 Dim myRange As ShapeRange
 
 Set myDocument = Worksheets(1)
 
 arrShapeName(0) = "Big Star"
 arrShapeName(1) = "Little Star"
 
 
 vntShapeName = arrShapeName(0)
 vntShapeName(1) = arrShapeName(1)
 
 Set myRange = myDocument.Shapes.Range(vntShapeName)
End Sub

Display More

**Carim** · Apr 6th 2021

Hello,

Hopefully, you have noticed this thread is ....14 years old ....

Constructing A Shaperange Collection For Dynamically Generated Charts

Participate now!

Share