I had a great clock face. Now it was a simple matter to add the hands and make them go around. Not.
I had drawn a minute hand from a diamond shape object and stretched it out so that it had a nice appearance. The code for rotation is similar to the code I used to position the ticks around the clock face. The code located the Left and Top in the sheet window using Sin and Cos of the angle in radians (0 to 2 pi). With each re-postioning, I rotated the MinuteHand object to a new orientation in degrees.
By the way, the help implies that there is a Pi() function that will return the value of pi. In Excel. Not in VBA. So I created my own pif() function to take its place. Similarly, Min() and Max() are available as spreadsheet functions but not as native functions. Someday I will get around to changing them to “Application.WorksheetFunction.Pi()”, “Application.WorksheetFunction.Min()” and “Application.WorksheetFunction.Max()”.
The result of all of this was that the Minute hand went around the face and changed angle for about 12 ticks and then jumped to what seemed to be an arbitrary location and continued from there for about another 1/4 turn when it jumped again. This odd behavior continued around until it got back to where it started.
After messing with it and trying to figure out what it was doing by stepping through it, I created another program to learn about how the shape was behaving. I put some code in to fill in the spreadsheet with the values for the tick number, Top, Left, Angle, Degrees and such. What I learned was that the bounding box for the shape:
1. Does not always contain the shape or conversely does not always closely adhere to the bounds of the the object.
2. The bounding box jumps from one orientation (portrait) to another (landscape) suddenly at a predictable but poorly documented rotation of the enclosed object.
3. The shape rotates around the center of the bounding box.
I drew this out on the white board and looked at it a long time. With the various points and triangles came the solution.
The pivot-point of the hand was the mid-point of the end of the drawn figure. The pointer-end was at the opposite side. The shape-center was the midpoint of the diagonal between (Left, Top) and (Left+Width, Top+Height). From the shape-center, find where the hand-pivot-point is now, after rotation. Find the delta between that place and the clock-face Center (I used the center of the window). Add those delta-x and delta-y to the current bounding box Left and Top. Then the hand shape will be properly positioned and oriented. Rotate, determine delta, shift. Repeat.
The code in the following post needs to be placed after the code in the previous post. And until I figure out how to make WordPress leave the code format alone, it will be lacking indentation. Sorry.