Jamcleat

This code follows the clock face code.  It also makes use of the pif() function, my local version of Pi() or Application.WorksheetFunction.Pi().
Continue reading “Hands On Code”

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.

In order to understand the drawing model In Excel, I first went to the book that I have, John Walkenbach’s Microsoft Excel 2000 Power Programming With VBA.  This is an excellent, well-written source of good information about using Excel whether you want to program or not.  It is well organized, helps you avoid problems and pretty much leads you through many aspects of creating good spreadsheets and VBA.  John Walkenbach has some other books on Excel including a more up-to-date version of this one.

Excel 2000 PP w/VBA was light on shapes. There are some examples on the CD. The only real references that I found helpful for what I needed on shapes were the programming introductory section regarding Cell Comments and how the Excel Object Model hierarchy may or may not have the property that you think that you want to change.  Do not skip the section on Comments if you really want to understand VBA for Excel (and probably Word, Powerpoint and Access).

Another important source of information is the help system in Excel Visual Basic for Applications.  This is easy to use in the VBA Integrated Development Environment (IDE), providing help with parameters, properties, applies to, and methods. For most help, just put the cursor on the word you want help with and press the F1 key.  The examples are usually trivial and it is sometime hard to find good examples that come close to what you want to do.

The third source of information is Create Macro.  Curious about code? Select Tools==>Macro==>Record New Macro and do something with the things you are interested in.  In my case, I created some shapes, moved them around, rotated them, gave them colors, etc.  Take notes as you do this though. Select Tools==>Macro==>Stop Recording.  Then Tools==>Macro==>Macros. Select the macro you just recorded (Macro 1?) and Click Edit.  Review and take note of the things that you want to do and the properties you need to be interested in.

Making a clock face was straight forward.  Ticks, text boxes, text.  Size based on window size.  About half an hour and that was done.

Now just add the hands.

About an hour into drawing the minute hand or rather making it move around, I separated the minute hand into its own spreadsheet.  I added code to log the values of the angles (radians and degrees are requred), and the x’s and y’s used in calculation.  Once I could see the changes in the values, the things required for the next baby step became apparent.

More in the next post.

I had been thinking about a little programming project for Microsoft Excel.  In order to complete the project, I needed to have a better understanding of the drawing engine.  I proposed for myself a little recreation to get a better understanding of what that would entail.

My idea was to make an analog clock, the kind with hands that go around.  Simple shape rotation stepped by a counter.  Not quite so.

The shapes in Microsoft Excel are located by their Top and Left properties.  No problem.  Just locate and rotate the hands as required.  Not so fast.  The shape rotates around its center.  No problem a bit harder to locate the Top and Left, but wait…there’s more.

It seems that the bounding box, the imaginary box that would enclose a shape does not track the rotating object.  Yes that’s right, the bounding box may or may not enclose the rotating shape.  And this.  The place where it changes is not an obvious thing that you could program to.  How to rotate the hand around the center of the clock as if it were a pivot.

An excellent suggestion I got was “why not put the center of the hand-shape in the center of the clock and hide the other part.  Good idea but it just moves the problem to the other side.  Since I have not learned yet how to color just half of a shape, I would have to have a second shape that matched the background that would act as a cover-up.  But I would have to rotate it just has I had the original.
In the end, i found a way to do it what I needed so it will be the subject of the next few blogs.