Microsoft Office Forums - View Single Post

excelledsoftware · 09-05-2014, 12:41 PM

You need to make some objects to achieve this. The Set word will do this after you dim it. The code below will do what you are looking for.

Code:

Sub Button1_Click()
  Dim ws As Worksheet, AngleShape(1 To 3) As Shape, x As Byte

    'Set up the references
    Set ws = ThisWorkbook.ActiveSheet
    Set AngleShape(1) = ws.Shapes("Ship")
    Set AngleShape(2) = ws.Shapes("Wind")
    Set AngleShape(3) = ws.Shapes("Apparent")
    
    'Perform the calculation
    Calculate
    'Perform the rotation for the above shapes
    For x = 1 To 3
      AngleShape(x).Rotation = ws.Cells(2, x)
    Next x

End Sub

I have a question for anybody though. My lines after 'Set the references show me setting each shape to the array variable. I tried things like

Code:

Set AngleShape = (ws.Shapes("Ship"), ws.Shapes("Wind"), ws.Shapes("Apparent"))

and

Code:

Set AngleShape = Array(ws.Shapes("Ship"), ws.Shapes("Wind"), ws.Shapes("Apparent"))

and

Code:

Set AngleShape() = Array(ws.Shapes("Ship"), ws.Shapes("Wind"), ws.Shapes("Apparent"))

But it will not let me do this. Even if I dim AngleShape as variant. I know that 2 more lines of code isnt a big deal but I would like to know if it is possible.

Thanks