Excel Shapes.Range(Array(“someName”)) – whats the defined range


I have the following code snippet in Excel VBA:

With Worksheets("MLS").Shapes.Range(Array("Rounded Rectangle 1")).Fill
    .ForeColor.RGB = RGB(166, 166, 166)
    .Transparency = 0.3
End With

I don't know where "Rounded Rectangle 1" is defined and what kind of shape or range it refers to. It is not defined within the VBA Code, I have already tried searching the whole project for the name.

Where can I find the definition of "Rounded Rectangle 1" and what might have been the programmers intent in referring to a range/shape using this shapes/range/array construct?

Best Solution

Worksheets("MLS").Shapes.Range(Array("Rounded Rectangle 1")) refers to a shape named "Rounded Rectangle 1" on the worksheet "MLS" so you won't find the definition you're looking for in your code, it's an object that exists on the worksheet.

Worksheets("SheetName").Shapes.Range([arg]) is used to reference a subset of the shapes present on the named worksheet (i.e. objects in that sheet's Shapes collection). The argument of the Shapes.Range method can either be:

  • An Integer, referring to the index of the shape in the Shapes collection
  • A String, referring to the name of a shape in the Shapes collection.
  • An Array containing strings and/or integers, referring to the names/indices of shapes in the Shapes collection.

In your specific case the use of Array("Rounded Rectangle 1") is unnecessary and the line in question could have been written as

Worksheets("MLS").Shapes.Range("Rounded Rectangle 1") 

with the same effect, or even just

Worksheets("MLS").Shapes("Rounded Rectangle 1")

However if there were multiple Rounded Rectangles on your sheet then you would need to use the full fat reference your mystery programmer used, something along the lines of

Worksheets("MLS").Shapes.Range(Array("Rounded Rectangle 1", "Rounded Rectangle 2", "Rounded Rectangle 3"))

Which would return a collection containing the shape objects Rounded Rectangle 1, 2 & 3.

Related Question