This tutorial explains a pair of important user defined functions, the “Navigator_u()” and the “Navigator_v()”. These functions save the user nine columns of formulas by calculating the effects of: 3-dimensional shift, rotations around the three axes of coordinates and 3D-2D perspective mapping. These user defined functions are also easy to use compared to writing all the perspective mapping equations from scratch.

In the previous 3D perspective tutorials we took in consideration two angles of rotation: the azimuth and the altitude. In artillery, radar, astronomy or wherever we have a ground based platform with an aiming function, we only need two angles (azimuth and altitude) to define the aiming direction.

The situation gets a bit more complicated in the case of a vehicle (ship, aircraft, or even a car) where we need to handle a total of three angles: the yaw angle (formerly known as azimuth) defines rotation around the vertical axis, the pitch angle or attitude (formerly known as altitude) defines rotation around the lateral axis and the roll angle (new) defines rotation around longitudinal axis.

##### Thanks to John Kerr for his suggestions on automating this model in VBA. Also thanks to Fabrizio Noto for his suggestion to using a single array function instead of two simple functions in order to improve speed (see his comment below) – in this case you need to write the function =Navigator() in a certain cell, select the cell to the right (or left) and then hit F2 followed by Shift+Control+Enter (all three keys at the same time). Once we have the 2D array in one row we can then copy it down (drag) in a regular fashion and we don’t have to use F2, Shift+Control+Enter again.

## Instant 3D-2D Perspective Mapping: the Navigator Functions

– by George Lungu @<excelunusual.com>

### Introduction:

This tutorial explains a pair of important user defined functions, the “Navigator_u” and the “Navigator_v”.

These functions save the user nine columns of formulas by calculating the effects of: 3-dimenssional shift, rotations around the three

axes of coordinates and 3D-2D perspective transforms.

In the previous 3D perspective tutorials we took in consideration two angles of rotation, the azimuth and the altitude. In artillery, radar,

astronomy or wherever we have a ground based platform with an aiming function we need only two angles (azimuth

and altitude) to define the aiming direction.

The situation gets a bit more complicated in the case of a vehicle (ship, aircraft, or even a car) where we need to take care of a total of three angles:

– the yaw angle (formerly known as

azimuth) defines rotation around the

vertical – z axis

-the pitch angle or attitude

(formerly known as altitude) defines

rotation around the lateral – x axis

-the roll angle (new) defines

rotation around longitudinal – y axis

<excelunusual.com>2

The user defined function below is the first of a pair of functions which calculate spatial shift, three different types of rotations and the 3D-2D perspective conversion

Native point Eye-screen Screen-origin

User Defined Function Spatial shift The three

Function name coordinate (object values rotation angles distance distance declaration definition)

Function

Function Navigator_u (x0, y0, z0, dx, dy, dz, Yaw, Pitch, Roll, eye_scr, scr_orig) As Double

return type

Dim x, y, z, x1, y1, z1, x2, y2, z2, x3, y3, z3 As Double

Variable type

x = x0 + dx

New coordinates

y = y0 + dy

Intermediate variables used

after translation

inside the function to calculate

z = z0 + dz

intermediate results

x1 = x * Sin(Yaw / 57.29578) + y * Cos(Yaw / 57.29578)

New coordinates

y1 = x * Cos(Yaw / 57.29578) – y * Sin(Yaw / 57.29578)

after yaw rotation

Division by 57.29578

z1 = z

converts degrees in

x2 = x1 radians

New coordinates

y2 = y1 * Cos(Pitch / 57.29578) – z1 * Sin(Pitch / 57.29578)

after pitch rotation

z2 = y1 * Sin(Pitch / 57.29578) + z1 * Cos(Pitch / 57.29578)

x3 = z2 * Sin(Roll / 57.29578) + x2 * Cos(Roll / 57.29578)

New coordinates

y3 = y2

after roll rotation

z3 = z2 * Cos(Roll / 57.29578) – x2 * Sin(Roll / 57.29578)

xES

(“u”) calculation as

u

Navigator_u = x3 * eye_scr / (eye_scr + scr_orig + y3)

function output ES SO y

End Function End of function declaration 3

The user defined function below is the last of a pair of functions which calculate

spatial shift, three different types of rotations and the 3D-2D perspective conversion

Native point Eye-screen Screen-origin

User Defined Function Spatial shift The three

Function name coordinate (object values rotation angles distance distance

declaration definition) Function

return type

Function Navigator_v (x0, y0, z0, dx, dy, dz, Yaw, Pitch, Roll, eye_scr, scr_orig, enable) As Double

Dim x, y, z, x1, y1, z1, x2, y2, z2, x3, y3, z3 As Double

Enable – show or hide

x = x0 + dx

point from the chart

Variable type

New coordinates

y = y0 + dy

Intermediate variables used

after translation

z = z0 + dz

inside the function to calculate

x1 = x * Sin(Yaw / 57.29578) + y * Cos(Yaw / 57.29578) intermediate results

y1 = x * Cos(Yaw / 57.29578) – y * Sin(Yaw / 57.29578) New coordinates

after yaw rotation

z1 = z

x2 = x1

New coordinates

y2 = y1 * Cos(Pitch / 57.29578) – z1 * Sin(Pitch / 57.29578)

after pitch rotation

z2 = y1 * Sin(Pitch / 57.29578) + z1 * Cos(Pitch / 57.29578)

x3 = z2 * Sin(Roll / 57.29578) + x2 * Cos(Roll / 57.29578)

New coordinates

y3 = y2

after roll rotation

z3 = z2 * Cos(Roll / 57.29578) – x2 * Sin(Roll / 57.29578)

If enable = 1 Then

xES

(“u”) calculation as

u

Navigator_v = z3 * eye_scr / (eye_scr + scr_orig + y3)

ES SO y

function output

Else

Enable – show or hide

Navigator_v = 9999

point from the chart

End If

End Function

End of function declaration 4

Here is a final, cleaned up version of the two functions – comments are in green

Function Navigator_u(x0, y0, z0, dx, dy, dz, Yaw, Pitch, Roll, eye_scr, scr_orig) As Double

Dim x, y, z, x1, y1, z1, x2, y2, z2, x3, y3, z3 As Double

Yaw = Yaw / 57.29578 ‘convert degrees to radians and assign result back to same argument

Pitch = Pitch / 57.29578 ‘convert degrees to radians and assign result back to same argument

Roll = Roll / 57.29578 ‘convert degrees to radians and assign result back to same argument

x1 = (x0 + dx) * Sin(Yaw) + (y0 + dy) * Cos(Yaw)

y1 = (x0 + dx) * Cos(Yaw) – (y0 + dy) * Sin(Yaw) „ z1 = (z0 + dz)

y2 = y1 * Cos(Pitch) – (z0 + dz) * Sin(Pitch) „ x2 = x1

z2 = y1 * Sin(Pitch) + (z0 + dz) * Cos(Pitch)

x3 = z2 * Sin(Roll) + x1 * Cos(Roll) ‘ y3 = y2

z3 = z2 * Cos(Roll) – x1 * Sin(Roll)

Navigator_u = x3 * eye_scr / (eye_scr + scr_orig + y2) ‘ since y3 = y2

End Function

———————————————————————————————————————————-

Function Navigator_v(x0, y0, z0, dx, dy, dz, Yaw, Pitch, Roll, eye_scr, scr_orig, enable) As Double

Dim x, y, z, x1, y1, z1, x2, y2, z2, x3, y3, z3 As Double

Yaw = Yaw / 57.29578 ‘convert degrees to radians

Pitch = Pitch / 57.29578 ‘convert degrees to radians

Roll = Roll / 57.29578 ‘convert degrees to radians

x1 = (x0 + dx) * Sin(Yaw) + (y0 + dy) * Cos(Yaw)

y1 = (x0 + dx) * Cos(Yaw) – (y0 + dy) * Sin(Yaw) „ z1 = (z0 + dz)

y2 = y1 * Cos(Pitch) – (z0 + dz) * Sin(Pitch) „ x2 = x1

z2 = y1 * Sin(Pitch) + (z0 + dz) * Cos(Pitch)

x3 = z2 * Sin(Roll) + x1 * Cos(Roll) ‘ y3 = y2

z3 = z2 * Cos(Roll) – x1 * Sin(Roll)

If enable = 1 Then

Navigator_v = z3 * eye_scr / (eye_scr + scr_orig + y2) ‘ since y3 = y2

Else

Navigator_v = 9999 „ we use argument “enable” different than 1 to make the point invisible

End If

5

End Function

Implementation:

The sample workbook contains three

worksheets, the first two are identical, the

only difference is that the latter is trimmed

to have fast button response. The third

worksheet is a demo and you must hit the

“Run-Pause” button to make it work.

Thanks to John Kerr for his ideas of demo

automation in VBA.

6

dear George,

why don’t you use a single UDF that returns both value (u and v).

This will increase the speed of your calculation!

Function Navigator(x0, y0, z0, dx, dy, dz, Yaw, Pitch, Roll, eye_scr, scr_orig, enable)

Dim out(1 To 1, 1 To 2) As Double

‘code shared <<<<<<<<<<<<<<<<<<<<<

out(1, 1) = x3 * eye_scr / (eye_scr + scr_orig + y2)

If enable = 1 Then

out(1, 2) = z3 * eye_scr / (eye_scr + scr_orig + y2)

Else

out(1, 2) = 9999

End If

Navigator = out

End Function

Thanks, Fabrizio. I wanted to do that exactly for the reason you mentioned (speed) since both functions almost to the end are identical but I didn’t know how to do it.

As I drove home last night, I thought you could combine this with your Virtual Joystick sheet to get the shape to spin/move in the direction of the cursor position?

Bingo! You read my mind, everything will converge into a flight simulator pretty soon. Thanks for the comment. George