3D-2D Perspective Mapping in Excel – part #2

The first half of this presentation dealt with mapping a 3D scene onto a 2D surface which can be a computer screen, a projection screen or the retina of the eye. By doing so we preserve much of the feel of depth of the scene. Of course when we look at an picture we get only part of the feel of depth because our both eyes see the same image. Stereoscopic view, refers to a technique for further enhancing the illusion of depth in an image by presenting two offset images separately to each eye of the viewer. We do not deal with this here – at least not yet. The whole proof of the first half of the presentation was done on a very particular setup when the origin of the object system of coordinates was perfectly centered with the eye-to-center-of-screen, also axis x was parallel to u and z was parallel to v. Though it seems like a particular conversion case, the degree of generalization is still maintained since we can use a secondary object system of coordinates which is translated and rotated from the original object system of coordinate by arbitrary values. In this presentation we review the concepts of translation and rotation and then apply them in conjunction with the perspective conversion to achieve a more general three dimensional model in Excel.

[sociallocker][/sociallocker]

This article has 9 Comments

  1. Hi,

    Many thanks for your info on 3D modelling in excel. Previously i’ve only ever used freefrom shapes and the graph method is deinitely seemingly a better way of constructing a wireframe. I’ve now managed to model a topographical survey of my current site.

    are you able to give me any clues as to how to go about creating a solid model. i know you’ve indicated that excel will be very slow, but i’d still like to give it a try, if i can know where to start. previously, with the with using the freeforms, i’ve always struggled with an alogrithm to work out which order the shapes should be placed in, so that rear planes area are underneath and the front planes are on top.

    Regards

    Richard

  2. Pingback: Flight Simulator Tutorial #3 – scene rotations and translation « Excel Unusual
  3. Peter, thanks for your input. VBA is slow as a dog if you try to do real calculations. Take my planetary system (the one in 3D or the one with 8 bodies). It will probably be 100 times slower in pure VBA (than it’s now) even without charting anything without any spin button. What you mention here is theory. I’ve seen three quarter of mile long code in VBA or other languages and it is dificult to read or write, it is slow (in VBA) and it is not intuitive. It’s one dimensional and it starts with 2 pages of variable declarations. Find your way through that, good luck… I have zero interest in that. Zero. If you do, I suggest you go to one of the 1001 sites which do that and only that. And they do it professionaly. You got the wrong idea about this site. What I do here is breadboarding (wires in the air and straps everywhere), you can probe anything using a oscilloscope in 2 seconds. No circuit board, no magnifier lenses, no Autocad, free of charge. You can see everything, touch, feel and smell everything. And learn a lot. I am coming from industry and I know the value of things. Programmers and programming are readily available. Everyone is full of advice, theory links and articles, yet most of people cannot do much anymore without a team and a budget. Why is that? And those teams are full of experts, senior designers, senior programmers Ph.D’s and such. I worked in Phoenix a couple of years ago for large semiconductor company and in just 12 days I managed to take one of their master models a fairly complex electromechanical system they had in Matlab and convert it in Excel (spreadsheet) on my own time and it was about 100 times faster than the original (with all the buttons and chart updating). If you want to do something useful, to really grow or challenge yourself, choose something from nature and model it just by thinking and doing (not by reading, asking or “teaming up”). Use any language you wish or no language (just paper and pen). Cheers, George

  4. George,

    In hindsight, the previously mentioned link on Excel performance conflicts with two of the things that you do most often in your spreadsheet demos:

    screen-updating and spin-control events.

    However, I hope it can help other readers who are using VBA to drive spreadsheet analysis. I feel that your use of these to control the variation of data fed to the algorithms and the immediate impact on the graphic result is a key to the usefulness of your site. I agree that this interactive approach to math, science, and art is valuable component of learning. My hope is that readers of your blog would be intrigued enough by your algorithms that they would take the next step and progress from the cell-driven formula to developing macros to expand the scope of using the formula.

    There are many arguments about first programming languages; the attached link provides a number of interesting comments on VBA as first language. I personnally don’t feel that the language itself is that important. I think your approach on introducing the math and science in an environment already familiar to many is great. The next step for many is to turn on the macro recorder and then to modify the VBA code to be able to deal with an expanding set of data.

    http://www.dailydoseofexcel.com/archives/2007/11/09/vba-for-beginners/

    Having programmed in several different environments, my main effort has been focused on learning enough of the Excel object-model to augment the cell-driven formulas I had already developed. VBA is very flexible in that algorithms can be quickly encoded and tested without too much concern about programming structure and it can also be treated as a complete programming environment to perform serious engineering analysis.

    This seems to be the mode I am currently in — quick and dirty evolving to more in depth analysis.

    Pete

  5. Thanks Peter! I will carefully study it. The main purpose of my method is introduction to programming for the average person with almost no use of a programming languag. It just happens to be fast and I like it this way. I got to a level where I can do many things, give me a year and you’ll see much more. People study computer science to create games and science modeling. What a waste, they should be studying art, physics and science by practice and playing rather than programming languages. I wouldn’t get into VBA too deep myself, if I ever put the effort I would study C or something similar. Most of my models are speed limited by charting though. Cheers, George

  6. I did a little basic VBA to animate it (put this sub in sheet4):

    Public Sub Mixed_motion()
    For i = 10 To 0 Step -0.1
    [b14] = i * 36
    [b12] = i * 36
    [b5] = i – 3
    DoEvents
    Next i

    For j = 0 To 10 Step 0.1
    [b14] = j * 36
    [b12] = j * 36
    [b5] = j – 3
    DoEvents
    Next j
    End Sub

  7. Thanks, John for all the comments. I need to find out what these games do and then it’s easy to translate them, no problem. I recommend to do it in Excel 95% and 5% in VBA (much faster and easier to program). We can do it even in solid shapes but here the speed of Excel is not that great if we exceed few hundred shapes. I am working hard to post some more 3D stuff today – a user defined function pair.

  8. Another excellent article! This makes me wonder whether its possible to do the 3D wireframe game “Battlezone” in Excel? I used to play a game called “cbzone” in the 1990s and I have the code in C, which occasionally I look at and wonder how to translate it to VBA!

Leave a Reply

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