VBA to change shape rotation

gebobs · #1 09-05-2014, 09:29 AM

I have a simple test and calculation.

Two variables:
* A2: Ship direction =rand()*360)
* B2: Wind direction =rand()*360)

One calculation:
* C2: Apparent wind direction (relative to the ship) =B2-A2+IF(A2>B2,360,0)

One button activating a macro that recalculates the ship and wind directions.

Pretty simple, but I'd like to fancy it up. I've put in three arrow shapes, one each for the ship, wind, and apparent directions. I'd like the macro to change the rotation of these based on the recalculated results.

I'm so rusty in VBA. Any help would be appreciated.

excelledsoftware · #2 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

gebobs · #3 09-05-2014, 01:58 PM

Thanks. We forgot to account for the original 90 deg rotation but I took care of that easily. Nice clean code is easy to debug.

AngleShape(x).Rotation = ws.Cells(2, x) - 90

Mille grazie!!

excelledsoftware · #4 09-05-2014, 02:18 PM

Glad it worked. I just noticed that I usually put

Code:

ws.Cells(2, x).Value - 90

But it appears to work without it.

gebobs · #5 09-06-2014, 07:47 AM

This was for a bet I had with my brother. He bet me $25 bucks I couldn't come up with a simpler solution than his or find out where his solution failed. On that second part, I gave up. Look at what his equation was:

=IF(A2<IF(A2+180>360,A2+180-360,A2+180),IF(AND(B2>A2,K2<IF(A2+180>360,A2+180-360,A2+180)),O2,-O2),IF(OR(B2>A2,B2<IF(A2+180>360,A2+180-360,A2+180)),O2,-O2))

Even if it's right, it's wrong. A kludge is a kludge. A and B are the same, but what the heck is O? LOL...stupid is what stupid does.

The above calculates the apparent wind direction on the ship if the ship is motionless. The next step of complexity is to then add speeds to both the wind and the ship. That requires some vector calculus. But I'm not doing anything until he posts the terms.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Change accent theme color used as shape fill (not RGBs)	preetidb	PowerPoint	4	12-30-2013 03:22 PM
color change for same shape	ajkiran	PowerPoint	1	11-10-2012 07:41 AM
enter free rotation	excelledsoftware	PowerPoint	3	03-01-2012 06:59 PM
Rotation Animation?	shinyjellicent12321	PowerPoint	1	02-19-2012 11:43 AM
Motion Paths with Rotation	PBone	PowerPoint	4	01-13-2011 11:59 AM