>.
This tutorial brings up an older topic from my blog, excelunusual.com. It is the second part of a short series, showing in detail, how to create a virtual joystick in MS Excel/VBA to serve as a game controller. The Excel file will be available for download on my blog, excelunusual.com. Enjoy!
0:00
Hello my friends and welcome back
0:02
we will be continuing our
0:04
investigation into building a virtual
0:06
joystick which we can then use
0:09
in a lot of games to control various
0:11
parameters of the game before continuing
0:14
with this joystick i want to review a
0:16
few things we did the last two tutorials
0:19
first one is the activex pin button
0:22
which in a regular form is powered by a
0:24
very simple macro the name of the macro
0:27
which is in this case param1 is followed
0:29
by the underscore and change followed by
0:32
of course this
0:33
round parenthesis and inside we can do
0:36
anything with this parameter we can put
0:38
it in some formulas but in this case i
0:40
made a simple example where we print the
0:43
parameter in cell b8 and if we look here
0:46
this is a property of the button we use
0:48
so this is a button here
0:50
okay
0:53
and the property of the button is
0:56
right here
0:57
it has a name which in this case is
1:00
param1 you choose the name
1:02
and the macro will have the same name
1:04
must have the same name followed by the
1:06
underscore and change and there is also
1:10
a delay which can be anywhere between
1:13
anything you want but i would not
1:14
recommend going below 20 milliseconds
1:16
this case we chose 50 milliseconds also
1:19
there’s two parameters a minimum and the
1:21
maximum
1:22
param can go in increments of one
1:25
between these two so zero one two three
1:27
four five blah blah blah eight nine the
1:30
second version is uh the spin button
1:32
with rollover feature i created this
1:34
name is that previous button except when
1:37
it goes to maximum if you click up again
1:39
it doesn’t get stuck to that value it
1:42
will jump to the minimum so if parameter
1:44
is greater than nine then parameter will
1:46
go to zero or if you keep clicking the
1:49
the lower arrow when the parameter goes
1:51
to minimum to zero it will jump back to
1:53
maximum to nine it’s a rollover i can
1:56
show you right here you see
1:59
zero one two three four five six seven
2:03
eight nine and then click again zero one
2:06
two it repeats if you go down four three
2:09
two
2:10
one zero and then jumps to nine this is
2:13
a rollover the third version has a
2:15
lookup table inside and let me see it
2:18
has a rollover feature and also has a
2:20
lookup table we define an array call
2:23
called param3 array you can call it
2:25
anything you want and then you define
2:27
the array as array open parenthesis and
2:30
put certain values here it can be
2:32
anything it can be numerical it can be
2:35
text it can be boolean it can be many
2:38
different things in this case we chose a
2:39
combination between numerical and text
2:43
as the parameter varies between 0 and 9
2:46
you go through these values so when
2:48
parameter 0 you choose this one in cell
2:51
b9 so you use parameter as an argument
2:54
in this array so let’s see how it works
2:57
now parameter is 0. it chooses 1.234
3:01
parameter is 1 chooses this value two
3:04
point four five six parameter is two it
3:06
chooses the text this is a test
3:09
then woo
3:11
four written four
3:13
then when it’s five it chooses parent
3:15
three which is five then six then 7.77
3:19
and so on and so forth and this being
3:21
said let’s go to our joystick what we
3:24
did for this joystick i’m going to show
3:26
you right here first we need to define a
3:28
function okay this is a standard
3:30
function in the library
3:32
user32 which any windows machine has so
3:36
this function is is called get cursor
3:40
position get cursor pause this is a
3:42
dedicated name you cannot change it and
3:44
that’s how you define it declare
3:45
function leave user32 here there’s two
3:50
strings pieces of text this you can put
3:52
any text and here this is a name of a
3:55
variable is a special variable
3:57
you you need to give a proper name which
3:59
means something that makes sense since
4:02
this function retrieves the coordinates
4:05
of the cursor x y i named this cursor
4:08
and the score x y and this is a
4:10
structure because the function will
4:12
deposit every time is called the
4:14
function will deposit the x and y
4:16
coordinates of the cursor in this
4:18
structure then you have to declare the
4:20
structure okay so this name here and
4:23
this name are the same okay so you
4:25
declare a structure structure has two
4:27
components an x component for the x
4:29
position of the cursor and y so this
4:32
structure has two components then we
4:34
declare
4:35
a variable c c c comes from i made this
4:38
up is my name that i gave this name it’s
4:41
a reference point for the center of the
4:43
joystick so we declared this cc variable
4:46
as
4:47
a structure
4:49
of the type cursor x y so it has two
4:52
components x x and y y so if you look
4:55
here for instance you see cc dot xx
4:59
which means you retrieve the x
5:00
coordinate of the cc here cc.yy also we
5:04
declare a boolean variable to be able to
5:07
obtain this function of start pause or
5:10
on off for the macro so we declare on
5:13
off again this is my name i chose this
5:16
name as boolean so the macro is just a
5:19
normal macro start with the sub ends
5:21
with nsub the name this is the name i
5:24
made up detect absolute coordinates and
5:27
the first we introduce this toggle
5:29
toggle the logic variable on off from on
5:32
from true to false or vice versa and
5:35
then we write the infinite loop a do
5:38
loop which starts with do ends with loop
5:41
but the special loop which means a
5:43
conditional do loop which has do while a
5:46
certain condition is true which means on
5:48
off is true so inside what do we do we
5:50
retrieve the cursor position in variable
5:53
cc and cc is what is a cursor x y
5:58
structure the function this function
6:00
from user32 library deposits two numbers
6:04
every time the loop runs during each
6:06
iteration this function special function
6:09
deposits two number an x coordinate and
6:12
a y coordinate in the structure called
6:14
cc
6:15
and then we use cell b5 and b c5 to
6:19
print the x coordinate of the mouse and
6:22
the y coordinate of the mouse with minus
6:24
sign y minus because um the origin of
6:28
the mouse coordinate is from this corner
6:30
of the screen this
6:32
northwest corner of the screen and the x
6:35
increases moving from the left to the
6:38
right and the y vertical coordinate
6:40
increasing going downward so because of
6:42
this i for the vertical coordinate i
6:45
chose a minus is more intuitive here
6:48
there is is a counter that increasing by
6:51
increases by one increments one every
6:54
time the loop is is uh goes through an
6:56
iteration and also i
6:59
print in a certain range a rolling
7:01
history of the position at the previous
7:03
position of the mouse let me show you
7:05
how this goes
7:21
so i started here from here i can stop
7:23
it from here i want to modify this i
7:26
want to make it better i want to make a
7:27
real
7:29
joystick so for this i’m gonna show you
7:32
the
7:33
an old file that has a joystick right
7:35
here this is how it’s supposed to work
7:38
i click in the middle here
7:40
and
7:42
the joystick sticks to my cursor
7:46
and if i click again it stops it but in
7:50
this uh in these cells here i get the
7:52
relative coordinates with respect to
7:54
this point so check this out this very
7:56
varies
7:59
every time i go far away it goes to a
8:01
large number
8:04
here they are limited by a hundred 100
8:06
so i’m going to close this this is what
8:08
we want to do and i’m gonna
8:11
first move our copy move it to the end
8:13
create a copy call it joystick number
8:16
two
8:18
and for this i also need to go to visual
8:21
basic editor and insert the module the
8:24
module for the first part of the
8:26
joystick and
8:27
see joystick underscore one is in module
8:31
one you need to put this in a module you
8:33
cannot put it in an excel object here
8:36
sheets because because of this
8:38
declaration special function insert
8:41
module we can call this
8:44
you can change the name we can go to
8:46
properties view
8:48
properties window and instead of module
8:51
2 we can rename it joy
8:55
stick
9:00
so we take the
9:01
code from module one so ctrl c go in
9:05
joystick paste it what do we need to do
9:08
here we this right now the way it is
9:10
right now it retrieves the absolute
9:12
coordinates in order to retrieve the
9:15
relative coordinates of the cursor with
9:17
respect to an initial clickball we need
9:18
to copy this okay copy and put it
9:21
outside the loop
9:23
ctrl v
9:24
and transfer these coordinates not to b5
9:27
c5 let’s transfer them to a variable
9:31
called
9:33
c
9:34
o
9:36
so the central of the cursor the
9:38
reference point
9:42
x
9:43
okay
9:45
and uh
9:51
c-o-y okay let’s remove this minus so
9:54
right now before the loop started we
9:56
click somewhere on a button or on a
9:58
chart and it will remember the x and
10:01
y-coordinate of the
10:03
of the click point and again we need to
10:05
also pick up this ctrl c use the
10:08
function before because right now we
10:10
don’t have the coordinates we need to
10:11
use the function get cursor position cc
10:14
and then co so the initial click point
10:17
coordinates are here
10:19
then during each loop cycle we do the
10:22
same thing but this time we need the
10:24
relative coordinates to the initial
10:26
click point
10:27
so from the current coordinates we get
10:30
during each loop cycle
10:32
we need to subtract the initial click
10:35
point coordinates and
10:38
forget about this we don’t need any
10:39
trailing history
10:43
this is good let’s see how it works we
10:45
go here and
10:47
[Music]
10:49
assign macro oh here
10:52
joystick detect absolute coordinate
10:55
actually we should
10:58
name it detect relative coordinates so
11:01
let’s not do anything let’s go here to
11:03
joystick and say uh
11:06
just joystick
11:07
detect relative coordinates
11:12
okay joystick
11:23
so let’s see
11:24
oops
11:25
debug okay so it doesn’t like something
11:29
c o y
11:30
okay here is the problem
11:32
cc
11:34
okay
11:37
something is moving so what we need to
11:39
do is
11:41
delete this okay
11:44
delete this chart remove the title make
11:47
it from um from format axis from minus
11:53
100
11:55
to plus 100
12:01
and also make this one from
12:04
minus 100
12:07
to plus 100
12:11
okay let’s make the chart smaller and
12:19
i want to have let me see let’s look
12:21
here inside
12:23
so we plot in b5 c5 let’s see b5 c5
12:27
right here this is zero zero we keep
12:29
this zero zero so check this out
12:33
the data for this
12:35
let’s
12:38
macro to the chart
12:40
call the detect relative coordinates
12:43
and check this out
12:49
the problem is the y
12:52
the y-coordinate has to be negative in
12:55
order to
12:56
to have the right movement
12:58
so i’m going here and say
13:02
minus
13:03
and here plus
13:06
plus okay
13:09
so check this out that’s right here you
13:12
see it’s following my cursor
13:14
is exceeding my cursor action so we need
13:16
to make the chart much smaller
13:21
so now it’s pretty good
13:23
the only problem i would like to limit
13:25
it when i go out it should be limited to
13:28
100 okay
13:29
my axes are between minus 100 plus 100 i
13:33
would like to limit it from to 100
13:36
between minus 100 and plus 100
13:39
so in order to do that i need to do the
13:42
following
13:44
the formula can get very complicated
13:46
because if there’s a positive value the
13:47
negative value so what i want to do is
13:50
essentially
13:52
instead of this
13:54
i want to
13:55
extract the absolute value from this
13:58
number so regardless of the sign
14:01
should be the same positive number
14:03
i think i b s
14:06
abs
14:07
okay
14:08
and also
14:10
extract an absolute
14:13
value from this
14:17
and in order to keep the sign
14:20
we need to
14:22
copy this
14:27
times
14:29
let’s put sign sgn
14:34
okay
14:36
so let’s see if it works sgn
14:40
so copy this
14:49
vba functions let’s see
14:52
it works okay
14:54
good
14:55
there’s a problem it’s not limited
14:58
so in order to limit
15:00
this
15:01
i want to say
15:03
max
15:04
okay no minimum minimum so
15:07
mean
15:09
of this
15:11
and okay there is no minimum you need to
15:13
use this this formulation
15:16
application
15:20
dot mean
15:22
okay
15:24
application that mean
15:33
this works
15:35
so
15:36
which means we use a worksheet a
15:39
spreadsheet function
15:41
that doesn’t exist in vba but if we put
15:43
application dot we can use that function
15:45
okay
15:47
sometimes you can use worksheet function
15:50
function worksheet
15:52
but i like application very simple and
15:54
here the same control v
15:58
so we want to limit this value the apps
16:00
of this uh
16:02
this x value to a hundred so we put
16:05
minimum of this and a hundred and here
16:08
let’s copy this ctrl c
16:13
actually now let’s let’s do this
16:27
so this should be limited
16:31
to 100 you see here
16:34
it doesn’t go past 100
16:38
goes to negative value is limited to
16:40
minus 100 okay sideways movement
16:44
minus 100 plus 100
16:47
so this is our
16:50
um
16:51
joystick
16:53
and
16:57
click on chart we started
16:59
click again we stop it always click in
17:02
the middle
17:04
otherwise you’re gonna have problem if
17:05
you started by clicking here
17:08
you know you you have some problems so
17:09
you need to click in the middle
17:12
so the mouse the
17:13
joystick head
17:15
stays under the cursor
17:17
another thing we can do
17:19
so essentially what i’m doing here i’m
17:22
giving the chart a facelift
17:24
i change some colors some dimensions
17:27
and eventually i’m gonna even delete
17:29
this button the on off which you don’t
17:31
need
17:32
now the on off
17:34
feature
17:35
the start pause
17:36
is attached to the chart itself to the
17:39
joystick
17:43
and next application we will learn how
17:45
to use this in some special cases
17:49
we will make a little game
17:50
to see to
17:52
prove you the utility of this macro
17:55
and this being said i will uh thanks for
17:58
watching i will see you in the next
18:00
tutorial
