Tags: array, arrays, building, errors, excel, following, microsoft, msdn, routine, routinesub, select, shapes, software, waythe

Using Arrays to select Shapes

On Microsoft » Microsoft Excel

3,508 words with 1 Comments; publish: Tue, 27 May 2008 00:52:00 GMT; (306109.38, « »)

I am having a problem building and then using an Array. Can anyone show me the errors of my way?

The following routine is called by another routine

Sub BuildArray(MyName)

On Error GoTo NotFound

If MyArray = "" Then

MyArray = MyName

Else

MyArray = MyArray & """, """ & MyName

End If

Exit Sub

NotFound:

Msg = "Selection is not on this Sheet."

Title = "Not Found"

Style = vbOKOnly

Response = MsgBox(Msg, Style, Title)

MyContinue = "No"

Exit Sub

End Sub

“MyArray” looks OK (i.e. “I3890 150”, “I3890 160”, “I3890 170”). Then the calling routine tries to use the array as follows:

Worksheets(MySheet).Select

Worksheets(MySheet).Shapes.Range(Array(MyArray)).S elect

Selection.Cut

End Sub

But the result is Run-time error “1004”. “The item with the specified name wasn’t found”.

If I substitute the actual value of “MyArray” (i.e. “I3890 150”, “I3890 160”, “I3890 170”) in the calling program, it runs just fine. What’s with that?

Your assistance is greatly appreciated.

Don

All Comments

Leave a comment...

  • 1 Comments
    • Don,

      You need to create a variant array variable and populate it using code like

      the following:

      Sub SelectShapes()

      Dim x(1 To 3) As Variant

      x(1) = "Oval 1"

      x(2) = "Oval 2"

      x(3) = "Oval 3"

      ActiveSheet.Shapes.Range(x).Select

      End Sub

      To create a dynamic array so that it can be used for any number of objects

      you could use code like the following

      Option Explicit

      Dim MyArray() As Variant

      Sub Test()

      BuildArray "Oval 1", True

      BuildArray "Oval 2"

      BuildArray "Oval 3"

      ActiveSheet.Shapes.Range(MyArray).Select

      End Sub

      Sub BuildArray(MyName As String, Optional Start As Boolean = False)

      Static i As Integer

      If Start Then

      i = 1

      ReDim MyArray(1 To 1)

      MyArray(1) = MyName

      Else

      i = i + 1

      ReDim Preserve MyArray(1 To i)

      MyArray(i) = MyName

      End If

      Exit Sub

      End Sub

      John Green

      "Don Rouse" <DonRouse.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:667F0724-1E7E-467B-9962-5854FD1433CE.excel.todaysummary.com.microsoft.com...

      > I am having a problem building and then using an Array. Can anyone show

      me the errors of my way?

      > The following routine is called by another routine

      > Sub BuildArray(MyName)

      > On Error GoTo NotFound

      > If MyArray = "" Then

      > MyArray = MyName

      > Else

      > MyArray = MyArray & """, """ & MyName

      > End If

      > Exit Sub

      > NotFound:

      > Msg = "Selection is not on this Sheet."

      > Title = "Not Found"

      > Style = vbOKOnly

      > Response = MsgBox(Msg, Style, Title)

      > MyContinue = "No"

      > Exit Sub

      > End Sub

      >

      > "MyArray" looks OK (i.e. "I3890 150", "I3890 160", "I3890 170"). Then the

      calling routine tries to use the array as follows:

      > .

      > Worksheets(MySheet).Select

      > Worksheets(MySheet).Shapes.Range(Array(MyArray)).S elect

      > Selection.Cut

      > End Sub

      > But the result is Run-time error "1004". "The item with the specified

      name wasn't found".

      > If I substitute the actual value of "MyArray" (i.e. "I3890 150", "I3890

      160", "I3890 170") in the calling program, it runs just fine. What's with

      that?

      > Your assistance is greatly appreciated.

      > Don

      #1; Tue, 27 May 2008 00:53:00 GMT