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 wasnt 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. Whats with that?

Your assistance is greatly appreciated.

Don

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" wrote in message
...
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