Using Arrays to select Shapes
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
|