Microsoft Office Forums VBA to change shape rotation
 Register FAQ Search Today's Posts Mark Forums Read

#1
09-05-2014, 09:29 AM
 gebobs Windows 7 64bit Office 2010 64bit Expert Join Date: Mar 2014 Location: Atlanta Posts: 837
VBA to change shape rotation

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.
Attached Files
 RWD.xlsm (16.2 KB, 13 views)
#2
09-05-2014, 12:41 PM
 excelledsoftware Windows 7 64bit Office 2003 IT Specialist Join Date: Jan 2012 Location: Utah Posts: 455

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

Last edited by excelledsoftware; 09-05-2014 at 12:48 PM. Reason: forgot content
#3
09-05-2014, 01:58 PM
 gebobs Windows 7 64bit Office 2010 64bit Expert Join Date: Mar 2014 Location: Atlanta Posts: 837

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!!
#4
09-05-2014, 02:18 PM
 excelledsoftware Windows 7 64bit Office 2003 IT Specialist Join Date: Jan 2012 Location: Utah Posts: 455

Glad it worked. I just noticed that I usually put
Code:
`ws.Cells(2, x).Value - 90`
But it appears to work without it.
#5
09-06-2014, 07:47 AM
 gebobs Windows 7 64bit Office 2010 64bit Expert Join Date: Mar 2014 Location: Atlanta Posts: 837

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.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post preetidb PowerPoint 4 12-30-2013 03:22 PM ajkiran PowerPoint 1 11-10-2012 07:41 AM excelledsoftware PowerPoint 3 03-01-2012 06:59 PM shinyjellicent12321 PowerPoint 1 02-19-2012 11:43 AM PBone PowerPoint 4 01-13-2011 11:59 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:18 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top