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.

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

– by George Lungu

1

### Introduction:

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 when 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 will 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.

<excelunusual.com>

### Translation

– The following coordinate transformation of point (x0, y0, z0) defines a translation by (Dx, Dy, Dz):

(x0+Dx,y0+Dy)

(x , y , z ) => (x + Dx, y + Dy,z + Dz)

0

(x0,y0)

Illustration of a 2D translation operation (a 3D translation is harder to draw so I saved some effort)

### Translation + 3D Perspective Conversion – spreadsheet implementation

– This stage is implemented in a new worksheet named, “Translation+3D”

<excelunusual.com> 3

### Azimuth rotation

– Azimuth rotation is rotation of an object around the “Z” axis. The z coordinate is unchanged so

the rotation calculations are concerned only with the (x,y) coordinates of the position vector.

Let’s review the definitions of two basic trigonometric functions on a right triangle:

Sin(alpha) =b/c

Cos(alpha) =a/c

Let’s take a vector with its (x,y) components (again, v is a position vector in the (x,y) plane and let’s rotate it by angle :

#### Before the rotation

vx and vy were parallel with the x and y axes respectively and could be expressed function of the axes unit vectors i_, j_:

vx_=i_*vx

vy_=i_*vy

Where i_ and j_ are the x and y unit vectors respectively

#### After the rotation

vx and vy are no longer parallel with the x and y axes respectively and can be expressed function of the axes unit vectors i_, j_:

vx = i _*cos(alpha)*vx + j _*sin(alpha )*vy

vx = -i _*sin(alpha)*vx + j _*cos(alpha )*vy

We can express v as the vector sum of vx_ and vy_

v_= v x_+ v y_=

=i _*vx* cos(alpha ) + j_*vx*sin(alpha) – i*vy*sin(alpha) + j*vy*cos(alpha)

Grouping around i and j results in:

v_ =i _*[vx*cos(alpha)-vy*sin(alpha)]+ j_*[vx*sin(alpha)+vy* cos(alpha)]

<excelunusual.com> 6

Calling vx’ and vy’ the new coordinates of the rotated point around origin we obtain the

following formulas:

vx’ = vx*cos(alpha) -vy*sin(alpha )

vx’ = vx*sin(alpha) + vy*cos(alpha)

(where alpha is the rotation angle and vx and vy are the original coordinates before the rotation)

Don’t memorize but be able to derive this at any time!

Sometimes people like to put this in matrix form:

Let’s see how the three transformations studied so far (translation,

azimuth rotation and 3D-2D perspective) can be incorporated in the

same worksheet =>>>

<excelunusual.com >7

#### Translation + Azimuth Rotation + 3D Perspective – spreadsheet implementation

– This stage is implemented in a new worksheet named, “Translation+AzimuthRotation+3D”

<excelunusual.com> 8

#### Altitude rotation

– Altitude rotation is the rotation of an object around the “X” axis. The x coordinate is unchanged

so the rotation calculations are concerned only with the (y,z) coordinates of the position vector.

By the same logic followed during azimuth derivation we have:

vy” = vy’*cos(beta) – vz’*sin(beta)

vz” = vy’*sin(beta) + vz’*cos(beta)

(where beta is the rotation angle and v’y and v’z are the original coordinates before the rotation)

Translation + Azimuth Rotation + Altitude Rotation + 3D conversion – implementation

– This stage is implemented in a new worksheet named “Transl+Azimuth+Altitude+3D”

– All the translation and rotation parameters are adjusted by spinner buttons

– Verifying the implementation is left as an exercise to the reader

by George Lungu <excelunusual.com> 9

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

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

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

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

George,

I know you do a lot using formulas in spreadsheet cells to get the maximum speed from many of these simulations; I tend to use more VBA code. I found this link:

http://www.codematic.net/excel-development/excel-vba/excel-vba-performance.htm

to speed up the performance of my VBA code. I believe it can also speed up cell calculations.

Pete

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

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.

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!