Rotating a line shape about a point.

I'm trying to rotate a line shape (created with the Shapes.AddLine method) about a point.
I've found an example authored by Chip Pearson - a clock - that does this nicely. It uses the following code to move the second hand:

Private Sub SetSecondHand(Seconds As Single)
Dim Theta As Single ' clockwise angle from vertical
Dim NewX As Single
Dim NewY As Single
Dim LS as Shape
Theta = (Seconds / 60) * TwoPI
NewX = cCenterX + (cLenSecond * Sin(Theta))
NewY = cCenterY - (cLenSecond * Cos(Theta))

LS.Nodes.SetPosition 2, NewX, NewY

Open in new window

However, when I try to run this code in Excel 2010, it returns the following error message:

"The specified value is out of range"

(Variables NewX = 296 and NewY = 107)

I've attached the original source file. Can you assist me in getting this to work on the newer version of Excel? Clock.xls
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Arno KosterConnect With a Mentor Commented:
this indeed works with polylines.
In order for the text fields to work, i updated the initialising code a little.
dsimcoxAuthor Commented:
A little more investigation - the shape Chip created (in an older version of Excel) with the Shape.AddLine method has no nodes in the newer versions of Excel. LS.Nodes.Count yields 0. If I create a free-form shape, it has a positive node count. Apparently the lines created with Shape.AddLine are different and node-less.
Arno KosterCommented:
The problem is that from excel 2007 the nodes property is no longer used and instead the rotation property is promoted.

let me see if i can come up with a solution...
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

dsimcoxAuthor Commented:
I've worked with the rotation property - but it rotates about the center point of the line. I want to rotate the line about the end point.

I think the nodes property is still accessible. If you draw a free-form line and check for nodes in the immediate window, it returns the correct answer - but not for a autoshape.
Arno KosterCommented:
dsimcox, have you tried the updated version of the clock.xls file ?

it uses polylines instead of lines. With polylines (which do have node objects accessible) the clock lines indeed can be pointed 'in the right direction' using macro code.
The version that I uploaded works at my workplace, using excel 2007.

Can you verify that it also works at your location ?
dsimcoxAuthor Commented:
I'd like to try the newer version of clocks.xls but I don't know where to find it. Can you post a link please?
Arno KosterCommented:
it's in my post 36813503 of september 29th @ 10.28 AM
you can click on the underlined text clock.xls
dsimcoxAuthor Commented:
Great - I'm out of town until Monday so I'll have a look at it then. Thanks for your helpful reply!
dsimcoxAuthor Commented:
I was able to use this example to get my code working. Thanks for making the updates to Chip's code. It helped a lot!
Arno KosterCommented:
you're welcome !
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.