"The item with the specified name wasn't found" error when trying to group shapes on a worksheet

Question

I am writing a VBA program to selectively group some rounded rectangle shapes. There are going to be sets of these groups, so I want to store them in an array. (For e.g. I would like to have dataSeriesGroup(1) to have a group of say three rounded rectangles, dataSeriesGroup(2) to have a group of three other rounded rectangles, and so on). I am trying to assign them to the group using the .Name attribute, as follows:

Dim ctr, ctr2, seriesCount, dataCount as Integer
Dim dataSeriesGroup() as Shape
Dim dataPoint() as Shape
Dim dTop, dLeft, dWidth, dHeight as long
Dim dataPointName as Variant

<Bunch of code to calculate values of dTop, dLeft, dWidth, dHeight, seriesCount, dataCount>

Redim dataSeriesGroup(seriesCount)
Redim dataPoint(dataCount, dataSeriesCount)
Redim dataPointName(dataCount)

For ctr = 1 to seriesCount

 For ctr2 = 1 to dataCount
   Set dataPoint(ctr2, ctr) = ActiveSheet.Shapes.AddShape(msoShapeRoundedRectangle, dLeft, dTop, dWidth, dHeight)
   dataPointName(ctr2) = dataPoint(ctr2, ctr).Name
 Next ctr2

Set dataSeriesGroup(ctr) = Activesheet.Shapes(Array(dataPointName)).Group

Next ctr

Everything is working fine, but when I am trying to set the dataSeriesGroup(ctr) I am getting an error "Run-time error '-2147352571 (80020005)': The item with the specified name wasn't found."

Can someone please provide some guidance as to what I am doing wrong?

Excelosaurus · Accepted Answer · 2018-11-23T06:16:32.870

The issue lies in the way variable datapointName is declared. You want to build an array that will behave the same as those returned by the Array function, which returns a zero-based array of Variants:

Dim datapointName() As Variant '<== Notice the parentheses.

Careful with the ReDims, as you typically don't want dangling Empty values at the far end of your arrays, so:

ReDim dataPointName(0 To dataCount - 1) '<== That's dataCount elements!

Also see the comments in the sample code, below, regarding variable declaration.

Finally, use the Range property of the Shapes collection to get a subset, and remove the call to Array(), since dataPointName is already that:

Set dataSeriesGroup(seriesIndex) = ActiveSheet.Shapes.Range(dataPointName).Group

Putting it all together, here's some functional demo code based upon yours:

Sub DoTheShapesThing()
    'Note: in VBA, to each variable its type; otherwise: Variant.
    'I've renamed some variables for clarity.
    Dim seriesIndex As Integer
    Dim dataIndex As Integer
    Dim seriesCount As Integer
    Dim dataCount As Integer
    Dim dataSeriesGroup() As Shape
    Dim dataPoint() As Shape
    'Haven't altered your position and size variables, but the type should typically be Double.
    Dim dTop As Long
    Dim dLeft As Long
    Dim dWidth As Long
    Dim dHeight As Long
    Dim dataPointName() As Variant '<== Here, the parentheses make all the difference! You want an array of Variants, just like the Array function returns.

    'I've added this declaration for the code to compile. REMOVE IT! You've probably declared this variable elsewhere.
    Dim dataseriesCount As Long

    'Test values...
    seriesCount = 2
    dataCount = 2
    dataseriesCount = seriesCount '<== Note that dataseriesCount must be >= seriesCount so the code below doesn't go "Subscript out of range".
    dLeft = 100: dTop = 100: dWidth = 100: dHeight = 100

    ReDim dataSeriesGroup(0 To seriesCount - 1)
    ReDim dataPoint(0 To dataCount - 1, 0 To dataseriesCount - 1)
    ReDim dataPointName(0 To dataCount - 1)

    For seriesIndex = 0 To seriesCount - 1
        For dataIndex = 0 To dataCount - 1
            'Took some liberties with shape disposition here...
            Set dataPoint(dataIndex, seriesIndex) = ActiveSheet.Shapes.AddShape( _
                msoShapeRoundedRectangle, _
                dLeft + 10 * (seriesIndex + dataIndex), _
                dTop + 10 * (seriesIndex + dataIndex), _
                dWidth, _
                dHeight)

            dataPointName(dataIndex) = dataPoint(dataIndex, seriesIndex).Name
        Next dataIndex

        Set dataSeriesGroup(seriesIndex) = ActiveSheet.Shapes.Range(dataPointName).Group
    Next seriesIndex
End Sub

Thank you so much!! My code is working now - the issue was with me not defining the dataPointName as an array. Really appreciate the help! — Samik Banerjee, Nov 23 '18 at 08:38

"The item with the specified name wasn't found" error when trying to group shapes on a worksheet

1 Answers1