>.
This tutorial brings up an older topic from my blog, excelunusual.com. It is the first 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!
Hello my friends and welcome back today
0:02
we will be discussing about creating a
0:05
joystick like this one here this is uh
0:08
this file is taken from my website from
0:10
excel unusual.com this is a chart having
0:13
a macro associated with it the macro is
0:16
called joystick so if we click in the
0:18
middle we start the macro and the macro
0:20
will monitor many times a second the
0:23
coordinates of the cursor it will update
0:25
two cells in the worksheet which will
0:28
update the chart and the chart will try
0:30
to maintain this
0:31
big big navy dot under the cursor giving
0:35
you the illusion that actually the
0:38
cursor captured this head of the
0:40
joystick so this being said let’s
0:43
proceed i already have a blank
0:46
excel
0:47
workbook open this is the new excel 365.
0:51
so first we go to i went here to
0:55
google and i typed detect cursor
0:58
position vba and you can go and
1:02
click on any of these
1:05
so we need to copy this first
1:06
declaration the declaration of special
1:09
function
1:10
the function is ctrl c
1:13
is called get cursor pause get cursor
1:16
position okay
1:17
and um we come here in developer visual
1:21
basic we need to open this into a module
1:24
so insert module paste this
1:27
i already spent a little bit of time
1:30
and i made a
1:32
joystick many years ago
1:34
so this get cursor pause get cursor
1:37
position is a special function c28 is
1:40
located in the user32 library api
1:43
application programming interface and
1:45
can be accessed through vba the first
1:49
word is arbitrary that’s why i replace
1:51
this with some arbitrary text the second
1:54
word
1:55
is the name of a variable
1:57
and you can put any kind of word you
1:59
want but make sure it’s relevant in this
2:02
case this variable it describes a
2:04
variable
2:06
is a variable in which the function will
2:09
dump the xy position of the cursor
2:12
and another thing we need to declare
2:15
we need to define right
2:17
is a
2:19
structure
2:20
in order to define it we
2:22
we write the word type
2:26
and
2:28
and we need to define this
2:30
name here so we put this name we
2:33
we wrote this name we made up this name
2:36
relevant to our application detecting
2:38
the xy coordinates of the cursor
2:41
so control with weight
2:44
ctrl c
2:48
okay
2:50
and it ends with end
2:53
type
2:54
okay and inside
2:57
this structure is like a collection it
2:59
can have various
3:01
heterogeneous
3:03
style data in it so in our case it has
3:07
two values the x value of the cursor
3:09
let’s call it xx
3:11
and define it
3:13
as
3:17
long
3:18
so long integer integer
3:20
and yy which is a y-coordinate as
3:24
long
3:25
we also need to declare a variable cc
3:28
which is of cursor x y type that will
3:31
hold the instantaneous values of the
3:33
cursor coordinates
3:35
beam lean from dimension cc
3:39
as
3:40
cursor
3:44
x y since we will be using uh
3:48
an on off or uh start pause macro
3:52
we need to define a boolean variable a
3:54
logic variable d
3:57
on
3:59
off
4:00
as
4:02
boolean
4:04
so these are all the declarations now we
4:06
can put an apostrophe and write a long
4:09
comment like these so we know these are
4:12
just declarations so let’s write the
4:15
macro now that detects the position of
4:17
the cursor inside this um the screen and
4:21
i tell you from now on the origin is
4:23
right here in this corner the
4:25
x-coordinate of the cursor is measured
4:27
from this corner right here you see
4:28
northwest corner upper left corner
4:31
towards the right so increases towards
4:33
right
4:34
and the y y position is determining from
4:37
the same point
4:38
increases
4:40
going down let’s write sub
4:43
let’s give a name let’s say detect
4:50
absolute apps
4:55
absolute coordinates
4:59
and inside what we do we place a toggle
5:02
statement so
5:04
on let’s copy from here okay copy this
5:10
ctrl v
5:12
is equal to not
5:15
don’t forget if you watch the other
5:17
tutorials before this we learn how to
5:20
make an on off or a start pause button a
5:23
toggle you need to toggle this and then
5:25
create a do loop a do loop is written
5:28
like this
5:29
do
5:30
it starts with the word do
5:32
and it ends with the word loop
5:34
okay if we want to make it
5:37
on off so with the toggle button we need
5:40
to put a
5:41
conditional here so do while
5:45
on
5:47
off is
5:49
equal to true
5:51
and what do we put in we write some
5:53
statements at the end we definitely need
5:55
to put do
5:57
events
5:59
okay and inside let’s do this first
6:03
let’s retrieve the coordinates
6:05
of the cursor every loop cycle this will
6:08
run very fast it’s an infinite loop but
6:10
it’s a conditional infinite group so if
6:12
on off becomes false it stops so let’s
6:15
use this function let’s copy this the
6:17
name of this function a special function
6:19
control c to retrieve the value of the
6:22
coordinates uh we have the variable cc
6:24
here that’s how the syntax is cc okay
6:28
i don’t know why they make it like this
6:31
but
6:32
that’s that’s the syntax so you put the
6:34
name of the function and then the
6:36
variable you want the function to dump
6:38
in the output of these coordinates of
6:41
the the cursor on the screen and then
6:44
let’s say cell
6:46
b5
6:48
so b5 in
6:50
square brackets let’s bring the x
6:53
coordinate okay so it is equal to
6:56
cc
6:57
dot x x okay i’ll show you why so cc is
7:01
a
7:02
cursor x y style variable so cursor x y
7:06
is a collection having two elements x x
7:09
the x coordinate and y y so c c x x
7:12
means the x coordinate of this cursor xy
7:15
and
7:17
then we can copy this
7:19
and let’s put this in cell
7:22
c5 which is ccy
7:26
and i think we are ready to rock and
7:28
roll we go here in the spreadsheet so we
7:31
expect to see this in cell b5 c5 let’s
7:35
call this
7:37
x
7:38
or
7:39
x coordinate and here
7:45
coordinate okay i don’t like that name
7:48
let’s call it x
7:50
cursor
7:54
and let’s call this y cursor
8:02
and insert a shape to create a button it
8:06
takes a while
8:08
let’s call this
8:09
on
8:11
off
8:12
and go to home
8:18
and assign macro
8:22
this is the only marker we have and
8:24
let’s see if it works
8:26
okay you see that
8:28
now the macro is running
8:30
so if you look at the x-coordinate and
8:32
the y-coordinate let me stop this let me
8:34
center these because i like i like to
8:37
center the data sometimes not always
8:44
if we go right here everything 0-0 okay
8:47
if we go here at this other corner so
8:50
south east corner is
8:53
1919
8:56
and
8:57
10.79 this is standard resolution
8:59
nowadays
9:01
1920 pixels and 1080. but since we start
9:05
with pixel 0 0 is
9:08
1080-1 1920-1
9:12
and in the middle they vary if i take a
9:14
chart let’s say insert and
9:18
charge
9:19
let’s move this up a little bit let’s
9:21
put here a 0
9:22
0 and inside source data edit
9:27
so remove this add another series let’s
9:30
pick up the x information from here so
9:33
let’s say to from 4 to 34 some 30
9:37
element and here
9:39
4 to 34.
9:43
okay
9:44
let’s start the macro
9:47
you see this is moving the problem is
9:49
right now
9:50
the chart has automatic
9:53
axes
9:54
so if we go
9:56
back and
9:59
instead of
10:00
the y-coordinate let’s put the minus
10:03
coordinate
10:03
and
10:07
let’s make this from zero
10:09
to
10:11
format axis
10:13
and here
10:16
this axis minimum is zero
10:21
and maximum is
10:25
1920 avenue
10:30
so check this out
10:32
now
10:34
this follows my movement here is
10:36
coordinate 0 0 0 you see
10:39
and here it goes to the end of the chart
10:47
we need to be
10:48
pay attention because if we want to have
10:50
animation animation will never like
10:52
objects will never pass this edge here
10:56
so we need to write or this edge right
10:58
here the cursor can go anywhere and you
11:02
can have zero zero but for practical
11:04
reason probably if you have a small
11:06
picture you can only bring it right here
11:08
you cannot bring it here we need to pay
11:09
attention on these to this in the future
11:12
developments another thing i want to
11:14
show you is if i go here to
11:18
to this um
11:19
macro i want to introduce a short-term
11:22
memory feature within the macro
11:24
check this out
11:26
i take a range okay let’s say
11:29
b
11:30
6
11:32
c 6
11:33
take a range from
11:35
b 6 to c 36 okay this is a range of b 6
11:41
c 36
11:43
and let’s say this is equal
11:46
let’s copy this this range ctrl c ctrl v
11:50
dot value
11:51
this is a a trick this is a big trick
11:54
okay very important and not many people
11:56
know about this if we do this nothing
11:58
will happen because you copy a range on
12:00
itself
12:01
but let’s uh have here 5
12:06
35
12:07
okay
12:08
what happens in this case you copy look
12:11
at me you copy this
12:14
this
12:15
the whole range one cell down okay here
12:20
check this out okay
12:24
you see
12:26
so in
12:27
the range b6 to c36
12:31
we created a trailing history
12:34
of our cursor movement or our mouse
12:37
movement
12:42
let’s
12:43
remove the zero from here
12:55
and another thing we can do
12:57
we can only
12:59
plot this i mean do this movement for
13:03
let’s place a
13:04
let’s place a counter here okay so i is
13:07
equal i plus one and we can plot it only
13:11
every let’s say fifth
13:13
ten okay then loop so if
13:17
i
13:17
modulo
13:19
10
13:24
10
13:27
is equal to zero
13:29
so this is a process called decimation i
13:32
plot the data only every 10th cycle then
13:36
then plot it we saw before that if we
13:39
plot this data every loop cycle the tail
13:43
is too short the points are too dense
13:46
and
13:47
if
13:48
check this out now
13:50
let’s put some spaces
13:52
is a little bit more
13:54
intelligible
14:13
i can even go every let’s say 30.
14:36
i stopped the macro wait the macro is
14:38
not stopped is it stopped yes it stopped
14:41
and it’s still blinking because of the
14:43
screen recorder so this being said in
14:45
the next tutorial i will show you how to
14:47
finish up this and make a nice
14:50
joystick thanks for watching
