This section explains the spreadsheet implementation of the perspective rotation and translation formulas within the Present array.
It also shows the implementation of the 3D-2D conversion formulas within the Past array, then it goes on to presenting the VBA macros used (the Reset and JoyStick macros).
[sociallocker]
[/sociallocker]
A Basic Flight Simulator in Excel #5 – the worksheet implementation of the perspective handling formulas and the presentation of the VBA the macros
by George Lungu
– This section first describes the implementation within the Present array
of the 3D perspective rotation and translation formulas.
– The tutorial then briefly reviews 3D-2D perspective conversion formulas
and goes on to explaining their worksheet implementation.
– Finally the section starts explaining the macros used by the model: Reset
and JoyStick. These Reset macro is very similar to the Reset macro used in
other projects and JoyStick macro is 90% the same with the old JoyStick
macro explained in a post in January 2011, the only difference being the
introduction of two more DoEvents statements (to allow for more prompt
charting) and the addition of a paste line (from Present array into the
Past array) in order to emulate the dynamic nature of the model.
<excelunusual.com>
Add one more entry to the input parameter area:
– Copy the “Tutorial_4” worksheet and rename the new worksheet “Tutorial_5”
– Insert a new cell label: R84: “Delta y”, and name the range of S84, deltaY
– Insert the Delta y formula: S84: “=Throttle”
– We insert this separately as a variable since we might later want to easily increase the complexity of this
formula without touching the Present array matrix (which is more complicated to change).
Fill out the Present (Current) array with perspective formulas (2 rotations + 1 translation):
We derived the formulas for the landscape perspective transformation before:
Use the above formulas, the named ranges and the Past array data to fill out the Present array:
=> V271: “=cosR*V401+sinRsinP*V402-sinRcosP*V403”
=> V272: “=cosP*V402+sinP*V403-Throttle”
=> V273: “=sinR*V401-cosRsinP*V402+cosRcosP*V403”
=> Copy range V271:V273 down into range V274:V393
=> Copy range V271:V393 to the right into to the range W271:BT393 and you have just completed the Present array
<excelunusual.com> 2
z (Target)
Brief review of the 3D-2D perspective conversion:
-We derived the formulas for the landscape perspective transformation in a previous post. A very brief knowledge refresh is given here.
– The “Target” point is situated in a 3D space past the glass screen (monitor surface) and it has the coordinates (x, y, z). The eye of the observer is situated in point E.
– In a descriptive way, finding (u, v) screen image coordinates after the 3D-2D
perspective transformation is like shooting with an imaginary handgun from the eye E Hole
to the “target” point and measuring the u-v coordinate of the bullet hole in the screen.
– Using right triangle similarity we can derive the following formulas (the derivation Screen was done before and it’s not given here: Plane
(Object) P
– The following two triangles are similar triangles: EO’A’ and EBP’, A’
therefore we can write the following proportionality equality:
the origin of the object
The object has to be in front of the observer therefore EB (ES+SO+y>0) must be positive at all times to
prevent artifacts from being displayed on the screen. While displaying multipoint shapes, the perspective
conversion formulas have to be written as to eliminate any shape that has even one vertex which does not
satisfy the previous condition, otherwise annoying artifacts have been noticed in the image.
<excelunusual.com> 3
Add two more entries to the input parameter area:
– Insert a new cell label: R86: “Eye_Screen”, and name the range of S86, Eye_Screen
– Insert a new cell label: R87: “Screen_Origin”, and name the range of S87, “Screen_Origin”
– Assign the constant 10 to the Eye_Screen constant and 0 to the Screen_Origin constant
(you can play with those constants later and choose a pair based on your visual preference).
Fill out the u-v array with 3D-2D perspective conversion formulas:
– It is very useful to make this array the same size as the Present and Past arrays.
– The u-v array will contain stacked information as follows:
=> the to top row will contain u formulas
=> the second top row will contain v formulas
=> the third top row will contain a condition later to be used to determine which
triangles have a vertex behind the observers so we can
“hide” or mask them in order to avoid visual artifacts
=>the rows below will have the same vertically
stacked structure with periodicity of three
-Use the formulas to the right, the named ranges and
the Past array data to fill out the u-v array
>V531: “=V401*Eye_Screen/(Eye_Screen+Screen_Origin+V402)”
>V532: “=V403*Eye_Screen/(Eye_Screen+Screen_Origin+V402)”
>V533: “=IF(Eye_Screen+Screen_Origin+V402>0,1,0)”
>Copy range V531:V533 down into range V534:V653
>Copy range V534:V653 to the right into to the
range W531:BT653 and the u-v array is complete
4
<excelunusual.com>
Retrieving the index number (showing simulation progress):
– We would like to monitor in real time how our simulation progresses, which means visualizing the number of
times the macro went through the 3D-2D perspective calculation loop. In our case, since we chose the time step
to be 1 second, the index would be equal to the number of seconds passed since the start of the model.
– While we could just add a line in the Joystick macro which prints the iteration number in a cell we prefer not to
do that since it takes simulation time, instead take advantage of the one-operation cut and paste which will be
implemented anyway to make the scene rotate and translate.
– We already have two matrices, the Past and Present between which data is exchanged (the functions in
Present array take argument from the Past array and the results of the Present array are stored in the Past
array during the next time step).
– The paste operation is done over 3 x 51 x 41 points of data out of which 3 x 41 x 41 are landscape. We could
use the rest of space for the index number simulation, additional landscape and control panel instruments.
How is this implemented in the worksheet? -> In both the Present and the Past array introduce a section
for index. The present index will be a formula (indexcurrent = indexpast + 1) and the past index will be a constant
integer number reset to 0 at the start of the takeoff.
-The same macro running the joystick function will paste the results of the present array calculations in the past
array and do this each cycle of the Do loop. In consequence the index will increase as the simulation
progresses and this is because the arguments in the present array function are taken from the past.
– Since we have plenty of room unused we can add a title for identification both areas where the index is
calculated, and we can change the background color for easy identification:
=> Insert a label in each of Present and Past
matrices: BK271: “Index”, BK401: “Index”
=> BK272: “=BK402+1”
=> BK402: “=0”
<excelunusual.com> 5
The Reset macro :
– The reset macro pastes the landscape result data from the Present in
the Past array so that the data can be used by the Present array to [V401:BJ523] = [V81:BJ203].Value
calculate the perspective image during the first time step. [BK402] = 0
– The index value in the Past array is reset to zero. End Sub
The JoyStick macro :
– The code for this macro has to be placed in a module otherwise it won’t work (the feature retrieving the
mouse coordinate will not work in any sheet section of the VBA project)
– In this section of the tutorial only the API declaration and PointAPI structure will be presented below. The
presentation of the JoyStick macro, which is 90% similar to the old joystick macro will be continued in the
next section.
– This part of the VBA code like all declarations has to be placed on the top of the page!!!
Sub Reset()
Declaration of a special API (Application
Public Declare Function GetCursorPos Lib “user32” _
Programming Interface) function which
(Some_String As POINTAPI) As Long
retrieves the cursor position
Type POINTAPI
declaration of a special structure (Point API) used as the output type of the previous API
X As Long
function. It is essentially the pair of coordinates (as long integers) of the cursor on the
Y As Long
screen started to be measured from the upper left corner of the screen.
End Type
declaration of a Boolean variable which has the role of a “switch”, keeping
Dim RunPause As Boolean track if the macro is running or is stopped.
This will allow the macro to be started or paused by clicking the same object (the joystick chart).
to be continued…
<excelunusual.com> 6
