Can You Reset a shape's Left & Top position

[Jason Zischke]

Hi All

I was wondering if you can reset a shapes top & left position after it is
rotated to reflect its new position?

Thanks in Advance

Jason

 

[Rick Rothstein]

What do you mean by "reset"? Exactly what are you attempting to do?

 

[Jason Zischke]

Hi Rick

What I mean is for example I place a rectangle on a worksheet then rotate it
90 degrees, its left & top position still talks to the position it was before
it rotates. Is there a way to change the property.

Thanks

Jason

 

[OssieMac]

Hi Jason,

Is the following what you are after.

With ActiveSheet.Shapes("Rectangle 1")
.IncrementLeft 10
.IncrementTop 10
.IncrementRotation 30
End With

Note that negative values are used to move up or left and rotate
counter-clockwise positive values for the other directions.

 

[Jason Zischke]

Hi OssieMac

Thanks for your quick responce. Unfortunately that isn't what I'm looking
for. I want to be able to talk to the new left and top position of a shape
after it has been rotated rather then the left and top position before it was
rotated?

Thanks

Jason

 

[Joel]

Excel rotates a shape around the center of the shape. It is simple to put
the upper left corner of the shape back to its original position by saving
the old top and left properties before you rotate and then putting the upper
left corner back to its original position

With ActiveSheet.Shapes("Rectangle 1")
oldleft = .Left
oldtop = .Top
.Rotation = 25
.Left = oldleft
.Top = oldtop

End With

 

[OssieMac]

Hi again Jason,

After some testing I now see what you mean. Joel's comment that the shape
rotates on its axis is correct and while the shape appears to have altered
its left and top positon, reading the values shows them as unchanged and
therefore resetting them to the old values does nothing. The following code
uses maths to relocate a rectangle shape. Firstly makes it a square then
rotates and then returns it to a rectangle and resets the left position.

I haven't gone into it to the nth degree and so it only works with rotation
from zero to 90 degrees. however, it might point you in the right direction
to achieve what you want.

To test the code, create a rectangle shape from the drawing toolbar on the
active sheet. (Ensure that it is Rectangle 1).

Sub RotateRectangleShape()
'NOTE: Not all variables required in test
'Left for future reference only

'Code only works for rotation from zero to 90 degrees
'Will require Case statement and test for current
'rotation position and adjust maths to cope
'with rotation from/to other positions.

Dim dblLeft As Double
Dim dblTop As Double
Dim dblWidth As Double
Dim dblHt As Double
Dim dblRot As Double
Dim shp As Shape

Set shp = ActiveSheet.Shapes("Rectangle 1")

With shp
dblLeft = .Left
dblTop = .Top
dblWidth = .Width
dblHt = .Height
dblRot = .Rotation

'Make rectangle into square
.Height = dblWidth

'Rotate
.IncrementRotation 90

'Return to rectangle
.Height = dblHt

'Reset left coordinate
.Left = dblLeft - (dblWidth - dblHt) / 2

End With

End Sub



Just for interest I used the following code to test what occurs when a
rectangle shape is rotated. As for the previous code create a Rectangle shape
on the active sheet.

Sub ShapeRotationTest()

Dim dblLeft As Double
Dim dblTop As Double
Dim dblWidth As Double
Dim dblHt As Double
Dim dblRot As Double
Dim shp As Shape

Set shp = ActiveSheet.Shapes("Rectangle 1")

With shp
dblLeft = .Left
dblTop = .Top
dblWidth = .Width
dblHt = .Height
dblRot = .Rotation

MsgBox "Left = " & dblLeft & vbCrLf & _
"Top = " & dblTop & vbCrLf & _
"Width = " & dblWidth & vbCrLf & _
"Height = " & dblHt & vbCrLf & _
"Rotation = " & dblRot

.IncrementRotation 90

'.Left = dblLeft 'does nothing
'.Top = dblTop 'does nothing

MsgBox "Left = " & .Left & vbCrLf & _
"Top = " & .Top & vbCrLf & _
"Width = " & .Width & vbCrLf & _
"Height = " & .Height & vbCrLf & _
"Rotation = " & .Rotation

End With

End Sub

 

[Joel]

OssieMac:

I think yoiur solution only works because you are rotating by 90 degrees.

Here is the general solution

Let x = original left position
Let y = original top position
Let h = height of figure
let w = width of figure
let R = radius of figure sqrt[(1/2h**2)+(1/2w**2)
let p = original angle of figure arctan(-1 * h/w)
The minus 1 is required because the
left position is negative to the center and the y is positive from the
center.
h and w are absolute numbers

The center of the figure is then
Let cx = x + 1/2w
Let cy = y + 1/2h, note negative y direction is positive on the screen
because positive pixels (x,y) is in the negative y direction.

The formula for the origianal top left corner of the figure with respect to
the center is

Left = cx + Rcos(p)
Top = cy + Rsin(p)

Now if you add a rotation d to the above formulas

Left = cx + Rcos(p + d)
Top = cy + Rsin(p + d)

The distance the left and top will move is the diference between the orignal
and new postions

delta left = Rcos(p + d) - Rcos(p)
delta top = Rsin(p + d) - Rsin(p)


The new Left and Top are the following
x2 = x + Rcos(p + d) - Rcos(p)
y2 = y + Rsin(p + d) - Rsin(p)

Note the above formulas don't reduce since
sin(p + d) = sin p cos d + cos p sin d
cos(p + d) = cos p cos d - sin p sin d

Note : When you rotate 90 degrees Sin A = cos B
sin(p + 90) = sin p cos 90 + cos p sin 90
sin(p + 90) = cos p

cos(p + 90) = cos p cos 90 - sin p sin 90
cos(p + 90) = sin p

 

[OssieMac]

Hi again Jason and Joel,

To Jason: As you can see the maths part of this can be complex or it might
be quite easy depending on exactly what is required.

Therefore perhaps you would like to share a little more information
otherwise we are really flying in the dark.

What sort of shapes are you working with?
How much do you want to rotate them? Is it in multiples of 90 degrees or are
you using intermediate rotations?
Have you considered resetting dimensions in lieu of rotation?

To Joel: I can see you put some real effort into the maths. I tried to
implement it in test VBA code but failed. Not sure if it is me or if there is
an error in the maths logic. Perhaps you would like to create some test code
similar to the example that I posted.

 

[Joel]

The proof I posted is simple geometery that any high schooler should know.
Can you post the test code you wrote. The error is porbably a sign error on
my part.

To understnd what I did addume the center of the figure is in the center of
a coordinate axis (x,y). I used zero degreees to be on the positive x-axis
and positive rotation in the counter-clockwise direction. The original upper
left corner of the figure would then be (-1 * width/2, height/2).

the screen coordinates are in pixels since left, top, width, and height are
al in pixels. The (0,0) screen locztion is in the upper left corner of the
screen. Positive x direction is left to right. Positive y direction is from
top to bottom which is opposite from standard conventions. I used standard
conventions so I didn't get confused.

The differences in convention is only important to keep the direction
straight. There is a trick I learned in geometry where we refered to the
quandrants of the coordinate axis by "CAST". This stood for Cosine, All,
Sine, Tan. The quandrants are as follows

1st - x positive, y positive
2nd - x negative, y positive
3rd - x negative, y negative
4th - x positive, y negative

CAST was the order for 4, 1, 2, and 3 quadrants indicating which trig
functions were positive.

C: 4th quandrant cosine is positive
A: 1st quadrant all trig functions are positive
S: 2nd quadrant sine is positive
T: 3rd quadrant Tan is positive

Therefore in the other quadrants the functions were negative. The trig
functions in VBA will follow these conventions as long as you use standad
conventions where 0 degrees is on the positive x axis and the positive
rotation is in the counter-clockwise directions. You then hae to remember to
multiply by -1 to compensate for the Y Pixels being in the oppositive
direction from the Y coordinate axis.

 

[Gerardo]

@ Joel, can you tell me please what do you mean with the variable "d" on "(p + d)"?

Thanks in advance!!

OssieMac:

I think yoiur solution only works because you are rotating by 90 degrees.

Here is the general solution

Let x = original left position
Let y = original top position
Let h = height of figure
let w = width of figure
let R = radius of figure sqrt[(1/2h**2)+(1/2w**2)
let p = original angle of figure arctan(-1 * h/w)
The minus 1 is required because the
left position is negative to the center and the y is positive from the
center.
h and w are absolute numbers

The center of the figure is then
Let cx = x + 1/2w
Let cy = y + 1/2h, note negative y direction is positive on the screen
because positive pixels (x,y) is in the negative y direction.

The formula for the origianal top left corner of the figure with respect to
the center is

Left = cx + Rcos(p)
Top = cy + Rsin(p)

Now if you add a rotation d to the above formulas

Left = cx + Rcos(p + d)
Top = cy + Rsin(p + d)

The distance the left and top will move is the diference between the orignal
and new postions

delta left = Rcos(p + d) - Rcos(p)
delta top = Rsin(p + d) - Rsin(p)


The new Left and Top are the following
x2 = x + Rcos(p + d) - Rcos(p)
y2 = y + Rsin(p + d) - Rsin(p)

Note the above formulas don't reduce since
sin(p + d) = sin p cos d + cos p sin d
cos(p + d) = cos p cos d - sin p sin d

Note : When you rotate 90 degrees Sin A = cos B
sin(p + 90) = sin p cos 90 + cos p sin 90
sin(p + 90) = cos p

cos(p + 90) = cos p cos 90 - sin p sin 90
cos(p + 90) = sin p