Hello everyone, this is a tutorial in which a virtual Excel joystick controlled by mouse movements on a scatter chart has been demonstrated .
While in operation, a macro updates the coordinates of the pointer in two spreadsheet cells. The two coordinates are then incorporated in formulas which limit the range of motion of the joystick.
Just like a real device the stick head movement is confined within a square (with a side length of 200 pixels) and so is the numerical output (limited to [-100,100] on both X and Y).
Immediate applications of this joystick are: a race car game (acceleration, brake and steering), a flight simulator (emulates an airplane cockpit yoke or control stick – Roll+Pitch), a shooting game (azimuth and altitude cannon control), or a combination of the above applications.
Good luck!
Usage: If the macro is stopped, click the chart to start it. It is a good idea to position the pointer in the origin of the chart before clicking, this way the cursor will roughly be positioned on top of the joystick head on the chart. The joystick position is controlled by the mouse movement. To stop the macro click the chart again.
A Virtual Joystick in Excel
by George Lungu
Introduction:
Focusing on a programming language takes one’s eyes off the ball. This website is based on Excel because Excel is very easy to use
without much prior knowledge. The main purpose of the blog is not to teach you Excel, VBA, Math, Physics, Geometry or Trigonometry but to
challenge you to think. The best teacher is locked up inside your head.
You must just learn to trust him and he will help you solve any problem.
Two years back I built a Tetris game in Excel using buttons on the screen as controls. I also tried to use shortcut keys but their
functionality seemed to be somewhat unreliable and problematic in the sense that the action of the keys was occasionally delayed.
A little later I made a roller coaster and my next objective was to build a flight simulator game in Excel. For something like that I really
needed to simultaneously and finely control two angles, the roll angle and the pitch angle.
The opportunity eventually showed up when I found a VBA code snippet on the internet which retrieved the X, Y position of the mouse
on the screen. And that’s what I was looking for …
<excelunusual.com> 2
A plan:
– This tutorial has three parts, the first part contains a short code which prints the absolute
mouse coordinates of the cursor on the screen, the second part demonstrates a macro which
retrieves relative cursor coordinates from an initial click point, and the third worksheet will
contain a complete virtual joystick.
– You don’t need to understand 100% of the code, just save the file somewhere else and when
you need this function use it. Again, the main purpose of this blog is not to teach you any
programming language but to motivate you to focus on building interesting applications on
your own.
Retrieving absolute coordinates – check out “Windows API” on Wikipedia
The following are declarations:
Declaration of a special API function (Application Programming Interface) which retrieves the cursor position
Public Declare Function GetCursorPos _Lib “user32” (Some_String As POINTAPI) As Long
Type POINTAPIX As Long
Y As Long
End Type
Declaration of a special structure (Point API) used as the output type of the previous API function.
It is essentially the pair of coordinates (as long integers) of the cursor on the screen started to be
measured from the upper left corner of the screen.
<excelunusual.com> 3
The first macro – absolute mouse coordinates retrieval:
– In the VBA editor insert a module (named Module1). Write both, the previous declarations and the following macro in Module1.
– This macro will print the cursor coordinates as an infinite time loop (about 50-500 times a second) in cell B5 and C5 respectively:
Sub GetCoordinate() Declaration of the macro name and return (nothing)
Dim Pt As POINTAPI Declaration of the string “Pt” as a Point API type structure
Do Infinite “Do” loop declaration (start)
DoEvents Always add this statement if you ever need to stop the loop manually or update a chart while the loop is running
GetCursorPos Pt The “Pt” structure is given a value by the API function “GetCursorPos” during each “Do” loop cycle
[B5] = Pt.X The cursor position is printed in the range “B5:C5”
[C5] = Pt.Y The cursor position is printed in the range “B5:C5”
Loop End of loop declaration
End Sub End of macro declaration
<excelunusual.com> 4
Let’s create a start button for this macro:
– In the drawing toolbar: choose “Rectangle” -> draw a rectangle
– Change the color of the square and add the text (I used “Get Coordinates”)
– Right click the button -> Assign Macro -> choose “Get Coordinate” from the list -> OK
Testing the macro:
– Click the button and observe how the numbers in cells (B5,C5) change to reflect the position of the cursor as we
move the mouse
– Observe how fast and precisely the updating is done
– Hold down the “Escape” button to stop the macro
<excelunusual.com> 5
The second macro – relative mouse coordinates retrieval:
– Inserting a new worksheet we’ll write the next macro in Module1 under the first macro
– This macro will print the cursor coordinates as an infinite time loop (about 50-500 times a
second) in cell B5 and C5 respectively, relative to the point on the screen where the start button was first clicked
– After the macro is created we will create a start button in a second different sheet (named “GetRelativeCoordinates”) where we can test the macro
Sub GetRelativeCoordinate() Declaration of the macro
Dim Pt0 As POINTAPI Declaration of two point API structures, one as initial click coordinates and the second as the current (dynamic) cursor coordinates
Dim Pt1 As POINTAPI
GetCursorPos Pt0 Assigns “Pt0” the initial click coordinates
Do Infinite “Do” loop declaration (start)
DoEvents Always add this statement if you ever need to interrupt the loop manually or update a chart while the loop is running
GetCursorPos Pt1 Every loop cycle assigns “Pt1” the cursor coordinates
[B5] = Pt1.X – Pt0.X Every loop cycle calculate the relative coordinates and
[C5] = Pt0.Y – Pt1.Y display them in the range “B5:C5” (figure out why I wrote those formulas the way I wrote them)
Loop End of “Do” loop declaration
End Sub End of macro declaration
<excelunusual.com> 6
The third macro – the virtual joystick:
– Inserting a new worksheet we write the next macro in Module1 under the previous macro. On
top we declare a Boolean variable which has the role of a “switch”, keeping track if the macro is
running or is stopped.
This will allow the macro to be started or stopped using the same button.
Dim RunPause As Boolean
– This macro will print the cursor coordinates as an infinite time loop (about 50-500 times a
second) in cell B5 and C5 respectively, relative to the point on the screen where the start button
was first clicked and it will also move the image of a joystick on a chart.
Sub JoyStick() Declaration of the macro
Dim Pt0 As POINTAPI Declaration two Point API type structures, one as initial click coordinates
Dim Pt1 As POINTAPI and the second as the current (dynamic) cursor coordinates
RunPause = Not RunPause Boolean “flip”, if the macro is stopped this will start it and vice versa
GetCursorPos Pt0 Assigns variable “Pt0” the initial click coordinates
Do While RunPause = True Conditional “Do” loop declaration (start)
DoEvents Always add this statement if you ever need tointerrupt the loop manually or update a chart while the loop is running
GetCursorPos Pt1 Every loop cycle assigns “Pt1” the cursor coordinates
[B5] = Pt1.X – Pt0.X Every loop cycle calculate the relative coordinates and display them in the range “B5:C5” (figure out why I wrote
[C5] = -Pt1.Y + Pt0.Y those formulas the way I wrote them)
Loop End of “Do” loop declaration
End Sub End of macro declaration
<excelunusual.com> 7
The virtual joystick the spreadsheet:
– While the macro “JoyStick()” is running, it will dynamically update the relative coordinates of the cursor on the screen the cells B5 and C5
– Cells B7 and C7 contain the relative coordinates taken from cells B5, C5 but limited to the interval [-100, 100]
– B7: “ =IF(B5<0,MAX(-100,B5),MIN(B5,100))”
– C7: “ =IF(C5<0,MAX(-100,C5),MIN(C5,100))”
– Set cells B8 and C8 to constant zero
– Create a 2D scatter chart with [B7:B8] as the X axis values and [C7:C8] as the Y axis values
– Delete any chart legend or title and change the color of the plot area to your preference
– Size the chart as a square and also adjust the range of both axes to [-100, 100]
– Right click the chart -> “Assign Macro” -> select “JoyStick()”
<excelunusual.com> 8
Usage:
1. If the macro is stopped, click the chart to start it. It is a good idea to position
the pointer in the origin of the chart before clicking, this way the cursor will
roughly be positioned on the joystick head on the chart. The joystick position is
controlled by the mouse movement.
2. To stop the macro click the chart again.
Conclusions:
– A virtual smooth joystick macro controlled by mouse movements was demonstrated.
– While in operation, the macro updates the coordinates of the pointer in two spreadsheet cells.
– The two coordinates are then incorporated in formulas which limit the range of motion of the joystick.
Just like a real device the stick head movement is confined within a square (with a side length of 200 pixels)
and so is the numerical output (limited to [-100,100] on both X and Y).
– Immediate applications of this joystick are: a race car game (acceleration, brake and steering), a flight simulator
(emulates the cockpit yoke or control stick – Roll+Pitch), a shooting game (azimuth and altitude cannon control), or
a combination of the above applications.
by George Lungu <excelunusual.com>
Than you!
You’re welcome.
I need a simple 3 DOF / 2D simulation for a project my company is working on. I suspected that Excel had the capability of doing real time graphics, but my mechanical skills far exceed my computer skills. The joystick and flight simulator on this site indicate that it can be done. We’re mostly interested in determining the stability of the system when controlled manually (joystick). Would anyone on this site be interested in working on this? Please contact bob@desertaerospace.com
I will be interested how the speed benchmarking work out. Size is not a particular issue; I have used MMULT to transform coordinates from ship axes to the helicopter flight deck for a spectral analyis (6 dof, real and imaginary parts, 50 or more frequency steps) giving a 12×12 matrix multiplied into a 12×50 matrix and followed this by solving simultaneous linear equations using the MINVERSE worksheet function. I was not ‘up against the clock’ though.
I will be working on that :-).
George.
This looks really useful. I have searched the VBA help pages and only found mouse movement events in the context of controls and, even at that, the functionality seemed to require the control was on a form rather than a sheet.
Can you point me to some documentation that will help me understand the syntax of the function:
Public Declare Function GetCursorPos Lib “user32” (Some_String As POINTAPI) As Long ?
The sheet did not respond very well to the Escape key but I eventually managed to shake the mouse monitoring off.
Regards, Peter
Peter, I will start with the second question first: click the chart again when you need to stop the macro. I designed it on purpose like this (using DoEvents and a Boolean “flip” variable) so that you can start and stop it at any time by clicking on the same object – the chart. Also when you start the macro click in the origin of the chart. This way the handle of the joystick will follow your cursor.
You might look down at me but I am not sure where to find the documentation. I found the macro on the internet and simplified it a little. I will use it a lot and I don’t feel any curiosity to find out what was in the mind of the person who designed it. I am not a VBA guru and I am not planning to be one. From the arsenal I pick a knife, a revolver and a shotgun and train myself how to use them very well. I don’t care about a thousand more weapons that are there until I find one that I am convinced it would give me a clear advantage while hunting (such as your MMULT and perhaps TRANSPOSE – I tested those this morning and now I need to benchmark them for speed). Thanks for the useful suggestions!!! George
Hello i’m not speak english very so i’ll write this letter in spanish sorry .
I found very interesting your site , but it will better if you show more explanation o any example , because many of us don’t know better excel , please.
Hola me llamo juan francisco soy estudiante de ingenieria industrial , encuentro muy interante su pagina web , quisiera saber donde puedo encontrar aplicaciones en excel igual que aqui pero destinadas a la ingenieria industrial , si Ud. añadiera mas de ellas en esta pagina seria muy provechoso , se despiede , Juan Francisco
Juan Francisco, my interest is to have a fast growing educational site. I need to strike a balance between novelty, quality and quantity. I cannot afford to go back and double the amount of explanations for each post in the past. That would not help because most of visitors would be below that level of understanding anyway. If you say, in this post I don’t understand this or that, I can go and fix that. Or if you suggest a topic I might be able to write a new post for you (like I just did few days ago for someone else). I get a fair number of downloads from people and they rarely spend any time to post a comment which would help my site. Let me know what you don’t understand and I can add to that post or write a new one.
Industrial engineering is a new interdisciplinary field which did not exist when I was a student. I believe that due to the growing trend of outsourcing from the US they invented a type of engineering which can deal, from a high level, with integrating different types of classical forms of engineering (some performed outside the US). I seriously doubt you can find a site like mine but focused on IE or focused on any other form of engineering. Excel is used massively from chemical engineering to aircraft design, but people do their work and would not take those sheets and post them, let alone make tutorials. Moreover how many people would look for that anyway?
If you really intend to learn you have to do mostly everything by yourself. You have to enjoy, be persistent, obsessed and passionate. You must treat it like a hobby you are addicted to. A good book, a job, a good team, a good teacher will just give you a shallow learning, the illusion of learning. After that shallow learning people get fet up of the technical side and are ready to promote to management. I did learn this pretty late myself. But if you really want to be an true engineer you need to enjoy technical work, be passionate about it, be inventive and creative, and treat it like a good game. Learn it by doing it not by reading about it. That’s why you should treat this site as a source of tricks and inspiration and try to create the models by yourself as much as possible. Make better things than you see here. If you spend the time you can do it.
And by the way there are plenty of explanations in these tutorials, you just have to follow more than one tutorial and also to google little details I might have missed.
If you post comments and bring other people on the site who comment too, I can make a couple of IE models for you. Just let me know what you need and I will try to learn it and create something. Right now however, I am very busy trying to take this site off the ground. Cheers, Jorge