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.