Posts by overdrive

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:

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