By popular demand I am coming back to Excel after a long hiatus. This is the second in the series of beginner level tutorials targeted to building Excel animated models and games.
>.
This is the second part of the video tutorials about one of the most important Excel animation macros (or sub routines). I am reviewing there (with PowerPoint slides) the previous tutorial about the topic and show you how to use an element of memory inside VBA (a global Boolean variable) to create this “toggle” effect so that any button/shape/photo you assign this macro to, will act as a “switch”, like a light switch, turning on and off an animation loop. Click the light green button above to download. Enjoy and come back!
Tutorial level: beginner
Thanks for your support!
0:00
hello my friends and welcome back thank
0:02
you for being with me today
0:04
hello my friends and welcome back
0:07
today we will be continuing with the
0:10
our investigation into the
0:13
stop
0:14
start and pause macro
0:17
here is an application of it is a
0:21
bouncing ball
0:23
the soccer ball
0:26
model
0:27
you can change three parameters the
0:30
speed of the ball let’s say let’s make
0:31
it much bigger
0:35
we can increase the also the gravity
0:38
let’s
0:39
slow it down
0:44
another ball is very heavy
0:48
we can also
0:49
why was it that four i believe
0:53
increase the or decrease the loss in the
0:56
walls during the bounce let’s make it
0:59
zero
1:01
so now the ball essentially
1:04
experiences no loss
1:07
or we can increase the loss
1:14
how is this done
1:16
there is a start stop macro
1:19
right here so start pause and
1:23
this macro calls the main loop so the
1:25
main loop is is a
1:27
second macro that is being called from
1:29
this uh start
1:31
pause or such start stop and
1:34
essentially there’s a fairly simple
1:36
physics i apply the three laws of newton
1:40
uh the classical mechanics and uh
1:43
simulate the fly through the
1:45
the air of the ball actually there’s no
1:47
air here it’s just vacuum kind of it
1:50
also models rotation so when it hits the
1:54
wall with a certain tangential speed the
1:56
wall would give the ball a certain
1:58
rotation and this being said
2:01
let’s go and continue our investigation
2:03
into the start pause macro let’s develop
2:05
it even more so because of this let’s
2:08
take the sheep number one and copy it so
2:11
move our copy create a copy move it at
2:14
the end
2:15
and let’s uh rename it uh
2:18
sheet2 okay
2:21
two
2:22
so first let’s go over the the previous
2:25
tutorial and i have a little powerpoint
2:28
here so the previous tutorial we
2:30
discussed how to write a macro it starts
2:32
with the word sub
2:34
then give it some name your name make
2:36
sure you don’t use a hyphen is
2:38
interpreted as a subtraction open and
2:40
close parentheses without anything in it
2:42
there are certain cases where you put an
2:44
argument or a multiple argument here
2:47
then you write code inside and then ends
2:49
up this is the end statement how to
2:51
reference cell in a workbook you
2:53
reference by writing range open
2:56
parenthesis open quotation mark put the
2:58
name of the cell r7 whatever it is r
3:02
represents the
3:03
column and 7 represents the row and then
3:06
close the quotation and the parenthesis
3:09
or even simpler you write the address of
3:12
the cell r7 in
3:14
square brackets also we build a count up
3:17
a macro which increments the value in a
3:20
cell by one and this is the syntax sub
3:23
the name which is count up in this case
3:26
parenthesis and then you put b10 is
3:28
equal to b10 plus one loops in vba the
3:31
simpler loop the simplest loop is a do
3:33
loop it starts with a do and it ends
3:36
with a word loop so this is a syntax
3:38
inside the put code there’s a more
3:40
advanced form of the conditional of the
3:42
infinite loop is the conditional do loop
3:45
you add the word while do only while a
3:48
certain condition is true
3:51
logic
3:54
logical condition okay
3:57
and then you put is exactly the like the
4:00
previous one but this will allow you to
4:03
stop the loop when the this condition is
4:06
not true is false so you can attach that
4:09
condition to a certain button to change
4:11
it from true to false and then the code
4:14
will stop this next example here do
4:17
while the value in b7 is the word
4:20
running the text running and then you do
4:23
the same thing b10 is equal to b10 plus
4:26
one add do events which allow the
4:29
loop to update the chart if you have
4:32
user chart every looped cycle and also
4:34
allows the loop to
4:37
pay attention to other things that
4:39
happen in the worksheet such as input
4:41
commands from the user maybe hotkeys or
4:44
click on buttons otherwise if you don’t
4:46
have this the loop will just blindly run
4:48
forever and you have to hit escape maybe
4:50
crash the whole program the start stop
4:53
macro this is a again
4:56
an outline of what we did last
4:59
tutorial you call it start pause okay
5:05
or you can call it
5:16
or stop start stop
5:19
or on
5:21
off
5:23
sometimes i call them all these names
5:25
you can call them any anything
5:37
macro has two blocks one is the toggle
5:40
block essentially
5:42
is attached to a button and when the
5:44
button is clicked the macro will change
5:46
the value in a certain cell in this case
5:48
b7 from running to stop or vice versa if
5:52
there’s a certain value in the cell it
5:54
will change to the other one and can be
5:56
only two different situations so this is
5:59
it is a if state and the if statement
6:01
controls the value in the piece b7 and
6:03
value b7
6:05
controls the running status of the loop
6:08
which can be either running or stopped
6:10
which is a do conditional loop if b7 is
6:14
equal to running the text in b7 is
6:16
running so do one b7 is equal to running
6:18
then you increment b10 the value in b10
6:21
and then if not you stop the loop this
6:24
is a loop also you add two events i told
6:26
you before do events is a very very
6:28
important one of the most important
6:30
thing in animation do events is a
6:32
refresh style function used in any
6:35
display loop so any loop that is not
6:38
pure calculation even if it’s only
6:40
calculation sometimes you want to be
6:42
able the user to stop the process so
6:44
that’s why it’s good to put it in cycle
6:46
of the loop however if you
6:49
are sure you never have to stop the loop
6:50
and you want super fast execution you
6:53
leave the do events out that’s what
6:55
microsoft says this is their official
6:57
description yields execution so that the
7:00
operating system can process other
7:01
events and this is what i say we need
7:04
this
7:05
in animation we need it in animation to
7:09
make sure the charts are refreshed
7:10
during each loop cycle we also need to
7:12
be able to use hot keys that’s why we
7:15
always do events or control buttons
7:17
during the loop operation otherwise we
7:20
might not be able to use them general
7:22
advice from the previous tutorial and
7:25
i’m gonna bring this up in almost every
7:27
tutorial don’t memorize the syntax for
7:29
the most part i mean you’re gonna
7:31
remember something and you’ll you’re
7:33
always gonna know a little bit more but
7:35
you’re gonna develop a sense rather than
7:37
memorize use google or copy from the
7:39
previous file from previous files i
7:42
recommend you create a an excel sheet
7:45
excel workbook with maybe 20 30 examples
7:49
of the main components and there’s not a
7:51
lot you can do
7:53
gigantic very complex animated model
7:56
knowing say 20 different tricks you put
7:59
them there
8:00
in a very simplified and isolated case
8:02
for each of them and then copy and paste
8:05
also you can use record macros we’re
8:07
going to talk about this or even better
8:09
use a combination macro recording and
8:11
google search to clean up the macro you
8:14
recorded because whatever you record
8:16
whatever you get google’s
8:18
even google search
8:20
results are very dirty i call them i
8:23
mean they have a lot of stuff that you
8:24
don’t need like i said they can be 10
8:27
times even more than that at least three
8:29
times larger than they should and also
8:31
keep the macros as slim as possible so
8:34
let’s now go and
8:36
continue our journey here so if we look
8:39
here we see there is an element of
8:42
toggle the macro itself doesn’t have
8:44
memory but you can make it to have
8:46
memory and i’ll show you how so in this
8:48
case we didn’t use the memory to
8:51
memorize this toggle variable let’s get
8:53
let’s call it the text variable running
8:55
or stop this is like a text variable
8:58
that varies between running and stop it
8:59
has two values but we can use a certain
9:02
variable inside the vba so we don’t need
9:04
to write in a cell this is important
9:06
when you want your animation not to have
9:08
anything to do
9:09
called code wise with a worksheet and
9:12
this is okay when you have very small
9:14
number like a few
9:16
elements of memory if you have tens of
9:19
thousands of millions you have a certain
9:21
wireframe with thousands of nodes you
9:24
need to use a worksheet i mean you can
9:26
put it in the code but it’s just
9:29
more complicated we will do that later
9:31
let’s implement this rather than
9:33
this memory effect not in the worksheet
9:37
in that in a cell in cell b7 but in the
9:40
vba code so we copy this
9:43
we come here sheet1
9:45
developer visual basic and we are in
9:48
sheet1 so we need to declare a variable
9:51
so i recommend you to go to
9:54
google and type how to declare a
9:57
variable in vba and just get spend a
10:00
couple of minutes look in general
10:02
since i used it recently i remember it
10:05
okay so beam
10:07
then comes from demand uh let’s give it
10:09
a name let’s give it the name the name
10:14
toggle
10:16
no no loop stay a loop state
10:21
as
10:22
boolean
10:25
and boolean means logic a boolean
10:27
variable has two there was a
10:29
mathematician let me see
10:32
[Music]
10:41
george bull okay and he developed
10:45
some of this logic
10:47
theory helped establish modern
10:50
symbolic logic and
10:52
whose algebra logic now created boolean
10:55
algebra is the basic design of digital
10:57
computer okay
10:59
so this guy beam loop
11:01
loop state this is the name as boolean
11:03
and then instead of this
11:05
check this out this is very important
11:07
highlight it and say
11:09
insert uh i’m sorry edit
11:12
replace so we will replace b7
11:16
okay
11:17
with
11:20
state
11:23
so we replace b7
11:26
you replace in three three places okay
11:28
and uh also
11:30
instead of b7 also we should replace it
11:32
here loop state
11:36
now if we didn’t declare it here loop
11:39
state and just use this it will still
11:41
work but it would not preserve
11:45
the memory of loop state variable it
11:47
would be an implicit variable declared
11:50
moment how do you say locally within the
11:52
macro
11:53
by the program itself and he wouldn’t
11:55
have the power the memory power of this
11:57
loop state that we declare it here
12:00
otherwise i usually don’t declare
12:02
variables it just makes it very simple
12:06
and again if you work in a company or
12:08
you are under a certain rule do whatever
12:11
they tell you they tell you to declare
12:12
all variables declare all variables they
12:15
tell you to use
12:16
option explicit which forces you to
12:18
declare everything use option is
12:20
explicit i don’t i like to have
12:22
everything slim and being slim even if i
12:25
forget something it doesn’t work i have
12:27
any problem i can see rather than they
12:28
declare a lot of things and waste a lot
12:31
of time and a lot of energy and a lot of
12:33
good disposition turn it into
12:35
disposition handling a million variable
12:38
and we will talk more about variables
12:40
how some of them we need to declare them
12:42
like in this in this case and check this
12:44
out i’m coming here i delete this stop
12:46
we don’t need it anymore
12:50
wait
12:50
um something doesn’t work so start pause
12:53
if loose loop state is running okay okay
12:57
okay okay
12:58
so it’s not um it’s not running loop
13:01
state cannot be running or
13:03
stopped it can be only true or false so
13:06
we go here and say
13:07
edit
13:08
edit
13:09
replace
13:13
running
13:14
with true okay
13:19
replace all
13:21
ripple replace
13:23
stopped with false
13:27
you see you have options to choose
13:30
replace it in the current procedure in
13:32
current module in current project or in
13:34
selected text in this case i selected
13:36
text replace all okay so now check this
13:40
out i’m coming here back to the workshop
13:43
worksheet it still doesn’t run let me
13:45
see oh i know why it doesn’t run because
13:48
we don’t need to call it differently but
13:50
let’s call it differently because
13:52
we don’t need because in in is in a
13:54
different sheet but if we call it
13:57
one it’s better to identify them we go
14:00
in the sheet start stop
14:02
okay so this is start stop
14:05
let’s call this
14:06
one okay
14:08
let’s call this two okay
14:12
and then if we have more we’ll call it
14:14
three four whatever start pause and then
14:17
we go here in the first sheet and say
14:21
assign macro we go with one
14:24
it’s not automatically updated okay so
14:26
if we update the macro name we need to
14:28
update on the button
14:30
so now
14:31
you see it’s running it stops runs
14:34
now we go to the second one
14:37
assign macro okay stop start stop start
14:42
stop and
14:44
this is the the end of the second
14:46
tutorial about the start pause
14:49
macro we’re coming back and i will show
14:51
you more tricks about the same procedure
14:54
and then i will show you some
14:55
applications i have my general
14:58
philosophy is learn a little bit by
15:01
experimentation don’t memorize and
15:04
by redundancy which means repeat repeat
15:06
repeat go deep into
15:09
this
15:10
a certain procedure that you learn the
15:12
thing is very small i mean compared to
15:14
other youtubers who make a whole and
15:16
write a whole novel in a tutorial no you
15:19
need to learn a little bit every time
15:21
let’s say over five tutorials you learn
15:24
a little bit about it and then you
15:26
really know it you can apply it in
15:27
various situations
15:30
like a famous martial artist
15:32
once said i’m not afraid of the master
15:35
who knows 1000 techniques i’m afraid of
15:38
the master who knows one technique
15:40
and knows it well and that’s why we are
15:42
gonna learn a few tricks i would say
15:45
somewhere between now 50 to 100 max
15:49
which is less than one percent of the
15:50
whole vba maybe one percent of one
15:52
percent of the vba
15:54
and with this you can do amazing
15:56
applications
15:58
if once in a while maybe once a year
16:00
twice a year you don’t know anything you
16:02
don’t know something it’s very difficult
16:05
you go to any forum you go to mrexcel
16:07
whatever and place your question there
16:10
if
16:11
in let’s say a week you don’t get the
16:13
satisfactory answer
16:15
log under a different username or ask a
16:18
friend to put an answer to your question
16:20
but an answer that is completely wrong
16:22
something ridiculously wrong and then
16:25
10 people will rush to correct that guy
16:28
so no matter how difficult your question
16:30
you can get an answer and this being
16:32
said thanks for watching and have a nice
16:35
day i will see you in the next video
