Introduction to Anaglyph Stereoscopy in Excel – part #5: adding a joystick to control the yaw and pitch rates of the 3D cube

In this section, a joystick is added to the model which controlls the  yaw rate and the pitch rate (not the angles!) of the stereoscopic cube.

This joystick was used before in a dedicated tutorial and in the flight simulator tutorial.

Its importance in future models can not be underestimated, hence I decided to cover this virtual device again. Use your 3D glasses while running the model.

The PDF erroneously claims this is part #4 of the tutorial, whereas this is actually part #5 (it’s a typo in the PDF).

[sociallocker][/sociallocker]

Excel anaglyph stereoscopy #5 – a joystick drive

by George Lungu

– In the previous section, a stereoscopic rotating cube was created. In this section,
two degrees of rotation, yaw and pitch are being linked to a virtual joystick.

A brief description of the virtual joystick:

– This macro will be assigned to a small chart. When the chart is clicked the macro returns two numbers
which are the relative coordinates of the mouse from the initial click point. Clicking the chart again will
stop the macro. The chart will display a stick centered in the center of the chart and whose end will
follow the mouse pointer on the screen. The movement will not exceed 100 pixels in any direction.

– The joystick and its operation are very important for future models. Though previously explained
before, a brief description of this device and the macro behind it will be given here.

– 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).

– This part of the VBA code like all declarations needs to be placed on the top of the page!!!

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
X As Long previous API function. It is essentially the pair of coordinates (as long integers) of
Y As Long the cursor on the 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 track if the macro is running or is stopped.

Dim RunPause As Boolean This will allow the macro to be started or paused by clicking the
same object (the joystick chart).

The JoyStick macro – continuation:

– Copy last worksheet and rename the new worksheet “3D_Stereoscopy_Joystick”

– The new JoyStick macro, shown below, is similar to the old JoyStick macro used before on this blog
either in the “Joystick” tutorial series or in the “Flight Simulator” tutorial series.

– The macro will first reset the yaw and pitch then it will record the cursor coordinates at the click
point that started the macro. During a continuous conditional loop the macro will constantly change the
yaw and pitch parameters by a value proportional to the relative mouse coordinates. As a consequence
the joystick will dictate the rotation speed not the (angular rates) for the cube positional angles.

Declaration two Point API type structures, Macro Declaration
one as initial click coordinates and the Sub JoyStick()
second as the current cursor coordinates Dim Pt0 As POINTAPI
Dim Pt1 As POINTAPI
Yaw and pitch value are reset at the start of the macro
[B2] = 0
This flips the “RunPause” Boolean variable and allows the macro
[B4] = 0
to be started and paused clicking the same button (chart)
RunPause = Not RunPause
Assigns “Pt0” the initial click coordinates
GetCursorPos Pt0

Conditional “Do” loop declaration (start) Do While RunPause = True

Always add this statement if you ever need to stop the DoEvents
loop manually or update a chart while the loop is running GetCursorPos Pt1

Every loop cycle assigns “Pt1” the current cursor coordinates [N2] = Pt1.X – Pt0.X
DoEvents
Every loop cycle calculate the relative cursor
[O2] = -Pt1.Y + Pt0.Y
coordinates and display them in the range “N2:O2”
DoEvents
Every loop cycle the yaw is changed by an amount proportional
[B2] = [B2] + [N3] * [N4]
to the x joystick deviation (this is a numerical integration)
DoEvents
Every loop cycle the pitch is changed by an amount
[B4] = [B4] + [N3] * [O4]
proportional to the y joystick deviation (numerical integration)
DoEvents
End of “Do” loop declaration
Loop
End of macro declaration
<www.excelunusual.com> End Sub
2

Worksheet update:

– Copy last worksheet and rename the new worksheet
“3D_Stereoscopy_Joystick”.

– Create a joystick calculation area:

– Range M1:M5 will contain the labels to the left.

– Range N2:O2 will be updated by the “Joystick” macro.

– Cell N3 will contain the rotation speed scaling factor. You can adjust it later to a value you like
depending on how fast you want the cube to rotate for a certain joystick deviation.

– Range N5:O5 will be the data for the origin of the joystick. Type a zero in both cells.

– Range N4:O4 will be the data for the joystick head, which is the relative coordinate data limited to
the range [-100, 100] and scaled by the rotation speed scaling factor:
=> N4: “=$N3*IF(N2<0,MAX(-100,N2),MIN(N2,100))”
=> Copy N4 to O4 and you get O4: “=$N3*IF(O2<0,MAX(-100,O2),MIN(O2,100))”

Create and format the joystick chart:

– Create a 2D scatter chart with [N4:N5] for X axis values and [O4:O5] for Y axis. St i c k

– Delete the chart legend, the titles and change the color of the plot area to your
preference. Minimize the font of both axes to the value of 1. Type number 20 in
both cells N2 and O2.

– Size the chart as a square and also adjust the range of both axes to [-100, 100]

– Click the center point once then wait 2 second and click it again. The cursor
turns into a cross. Choose a red marker for the center of size 8 and repeat the
operation for the other end of the joystick (the handle) choosing a blue circle of size
20. You can of course choose the colors and sizes you like but a small red pivot point
for the joystick is useful in referencing (zeroing) the joystick neutral at the start.

– Right click the chart -> “Assign Macro” -> select “JoyStick()”

– The model is ready to use by clicking the center of the joystick chart. Observe that the rotation speed is
proportional to the joystick deflection. To stop macro and the rotation just click the joystick chart again.

<www.excelunusual.com> The end.
3

4 Comments


  1. Don, I don’t want that. I initially made it like that but I prefer to give the cube the feel of a vehicle control, better say an anti-airctaft cannon. About the internals of the macro, I want to keep it very simple and do the rest in the worksheet. Don’t forget, this is an beginner tutorial.
    Thanks for the input, good stuff! I never used matrices in VBA. Readers might be quite interested in your version. George

  2. Oh, I also forgot to mention that you have to convert the “azimuth” and “altitude” rotation matrices to values as well, so start with unit matrix values {{1,0,0},{0,1,0},{0,0,1}} in both.
    Cheers,
    Don

  3. Hi George,
    I tried a modification to your spreadsheet by converting the “azimuth*altitude*roll” range I7:K9 to values and adding a reset button at well that runs the code:

    Private Sub resetButton_Click()
    [I7] = 1
    [J8] = 1
    [K9] = 1
    [I8:I9] = 0
    [J7] = 0
    [J9] = 0
    [K7:K8] = 0
    End Sub

    and changing your code for the Joystick to input increments to the rotation angles relative to the screen. That way the joystick will behave like you’re manipulating the cube in your hands rather than like you’re flying thecube and viewing it from a 3rd person perspective. Both styles of rotation are valid and it depends on your purposes as to which is the most appropriate to use.

    Note that this implementation also drops the “roll” rotation input altogether.

    The modified joystick code is:

    Dim Pt0 As POINTAPI
    Dim Pt1 As POINTAPI

    RunPause = Not RunPause
    GetCursorPos Pt0
    Do While RunPause = True
    DoEvents
    GetCursorPos Pt1
    [B50] = Pt1.X – Pt0.X
    DoEvents
    [C50] = -Pt1.Y + Pt0.Y
    DoEvents
    [B1] = [B52] / 1500
    [I7:K9] = WorksheetFunction.MMult([E2:G4], [I7:K9])
    DoEvents
    [B3] = [C52] / 1500
    [I7:K9] = WorksheetFunction.MMult([I2:K4], [I7:K9])
    Loop

Leave a Reply

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