Creating, Sizing, Translating and Rotating 2D Shapes in Excel 2007

Most of the models on this blog are designed for Excel 2003 or earlier versions. Sometimes however, Excel 2007 or 2010 are the only versions available even though they might be far slower when running these models. This presentation is an introduction to Excel 2007 and it was suggested to me by one of my readers.

[sociallocker] [/sociallocker]

This article has 9 Comments

  1. Thanks Charles.
    It also seems you understand these operations quite well. I am unfortunately not really interested in formal teaching. I spent a weekend and tried to just to reinvent the perspective formulas. I am interested in applications and also in deriving the formulas “on the beach, on a deserted island” without any formal knowledge. Cheers, George

  2. Peter,
    I too would like to look at what you have done. I have looked into the shapes enough to understand your code. I got stuck in rotating collections of shapes that are oriented and rotated relative to each other…. the MS model did not seem to have a rotation matrix definition per object and then one per collection or group.
    Thanks, Charles

    George great Blog… just found it.

  3. Peter: I’d like to see this workbook to rotate shapes. This could help solve a problem I am currently facing. Regards, Todd

  4. Peter, for the time being I am sticking with my style since I’ve barely started. Later I might introduce more of other type of coding. Rotation is a rather a secondary issue for me now but I think your suggestion with MMULT is valuable and I will try it in the current post. Once I exhaust this path I might move to a different style. In my very subjective opinion, the first principles are both the forest and the trees for people who are learning. Once somone understands the concept he can move to anything else. I would definitely like to look at those files. Thanks, George

  5. George. I need to look at more of your examples before offering too much in the way of personal opinion. For the moment if I pick up on the shapes and coding issues.

    1. I would not suggest that use of the MS drawing package for rendering complex 3D geometry is a good idea. However, the 2½ D world of extruded shapes can produce remarkably good effects to illustrate ideas. If you wish to see examples then I can send a workbook to you, although the ones that depend upon Excel 2007 functionality may offer little of value for your website.
    2. With the coding, you can either stay with the ‘from first principles’ approach or move on to more abstract concepts. The former approach creates problems of ‘not being able to see the wood for the trees’ but, ultimately, in the second approach the basics will become hidden in CAD libraries etc. Hitting the right balance is a challenge.

    As for as the balance between VBA and in-sheet calculation is concerned, I rotated a chess board (64 squares linking 81 distinct vertices) with 4 playing pieces using 5 lines of VBA.
    With Shapes(“Board”).ThreeD
    .RotationX = alpha
    .RotationY = beta
    .RotationZ = gamma
    End With
    Alpha, beta and gamma were read from cells on the worksheet after being calculated from the ‘azimuth’ and ‘altitude’ rotations you mention elsewhere.

  6. Thanks Peter for the suggestions. I am aware of using shapes to create solid objects. There is a file floating around form a Gamasutra guy. I will use that later for very limited purposes but right now there is enough meat in the wireframe business, plus using VBA extensively to control the vertices defeats the purpose of using excel. I’ve seen objects (faces, cars) created in VBA using the shapes and for fairly small objects the speed even in 2003 was awfully low. In 2007 or 2010 the refresh rate must be less than 1 frame/minute. You can make a cube or a simple object but try putting the coaster in VBA (few thosand vertices) and the loop will have a period of minutes. C and open GL are not that hard so spending time to write VBA for real animation is a waste. Excel has a size threshold beyond which the speed goes down a lot. I am trying to encourage people to work in simple and natural ways, and solve a very wide ranges of problems. In my vision, the physics and occasionally even some math are important. People need to be able to solve any of these problems with a pen and a paper and not worry about syntax or languages. Once you get into more VBA and special functions all of that is lost. One should determine a minimum useful set of funtions and try to stick with that (offset is one of those). Out there in industry and even academia there are many good programmers who one can hire to properly code anything. Yet these people are just good coders. They use “recipes”, modules and libraries and forget to use their mind. In industry companies spend fortunes to buy black box software licenses and the engineers end up being tool jockeys. Creativity, logic and original thinking are generally penalized. I am trying to raise a flag here and give a different example.

  7. I am enjoying the creativity that has gone into this example as well as others on the site; Excel does not have to be dreary!

    There are all sorts of trivial things to be learnt from looking at other’s coding styles; I had not realised that you could use a spinner control without using the linked cell to communicate the value for example. Meanwhile, a couple of ideas for further development:

    1. The array formula MMULT gives a mathematically very clear approach to implementing the rotations and translations shown. If combined with named ranges then the formula might read
    {=MMULT(coordinates, alpha_rotation) + offset}
    The idea could extend to the rotation of 3d boxes rather than sticking with 2d rectangles.
    2. The same thing as is done here through the use of scatter charts can also be achieved using drawing objects in the Shapes collection. The advantage then is that fills can be used to create solid blocks of color moving across the worksheet. If you are feeling adventurous these may be extruded into 3d shapes and moved in 3d with fancy shading and lighting effects (Office 2007 and later).

  8. Try to get an earlier version of Excel too. The models will really come to life.

  9. Oh yes! Amazing post. I loved this one. I am already practicing and enjoying. Thanks a lot

Leave a Reply

Your email address will not be published. Required fields are marked *