By popular demand I am coming back to Excel after a long hiatus. This is the first in the series of beginner level tutorials targeted to building Excel animated models and games.
>.
Past a few minutes of introduction into a 4-stroke engine Excel animation model, the video dives into how to build the first and perhaps the most important part of an animated model, either for science or for games. It’s about creating a “toggle switch” macro, one that allows the models to be started (animated) and stopped with clicks from the same “spreadsheet button”. A link for file download is posted above (green button). Thanks for visiting!
Tutorial level: beginner
Thanks for your support!!!
0:00
thank you my friends and welcome back
0:01
this is a animated four-stroke model in
0:05
excel you can see a crankshaft rotating
0:08
you can add a piston then you can add a
0:11
connection row the control let’s
0:13
decrease the speed you can also add
0:15
other elements such as
0:17
valves
0:18
spark plug
0:21
you can see the spark there
0:24
you can change the duration of the spark
0:26
from
0:28
one to five
0:30
let’s keep it on two
0:33
you can also bring into visibility the
0:37
uh
0:39
intake
0:40
the exhaust
0:42
the crankcase
0:44
and the cylinder
0:46
and you can also
0:48
see this from the outside
0:50
from the inside and not at all
0:55
you can also bring in a flywheel
1:00
smoke
1:02
which can be
1:04
white or black
1:07
also
1:10
gas visibility inside the cylinder
1:14
you can change the speed of this thing
1:16
make it run very fast
1:19
or
1:20
much slower
1:22
you can also change the
1:24
colors
1:26
you see the color of the background the
1:28
color of the
1:30
ceiling there the color of the crankcase
1:32
everything has a
1:34
an array of colors you can
1:36
choose from
1:38
you can also run it into
1:41
a color changing demo mode
1:44
in which the
1:45
there’s a routine that changes the the
1:48
color of each
1:50
element
1:52
with respect to
1:55
a certain table
1:58
you can also create your own custom
2:00
everything
2:02
presence or absence of different
2:04
elements colors and then save it
2:07
into uh let me stop this
2:09
into a certain location like here
2:11
there’s a long table literally thousands
2:14
of cells you can make thousands of
2:18
setups
2:19
and
2:21
i give an example you set this to
2:24
location 10 so in location 10
2:27
i can read
2:28
location 10 you have this configuration
2:30
location
2:32
9 weight location
2:34
11
2:35
8
2:36
read that’s what you have in location
2:39
let’s change this to
2:41
i don’t know let’s say this and store it
2:45
in location
2:46
10 so story then i can go here
2:49
that’s what i have in location nine
2:51
location then i have that how is this
2:53
done is created using powerpoint give an
2:56
example this is a powerpoint i
2:58
i use one of the files i used to create
3:00
this i downloaded some real engine
3:03
blueprint try to maintain certain
3:05
proportions and create my own files here
3:08
powerpoint use shapes
3:12
so here is another one
3:16
i’m running through some pages
3:20
show you how i made it
3:26
so this is not yet ready to download i
3:28
mean it’s ready the file is ready i just
3:30
need to upload it on the site this model
3:33
is made with sprites which means
3:37
every shape
3:40
is created in powerpoint here and
3:43
you can see i have a bunch of them one
3:46
for each color
3:58
so soon you will be able to download
4:00
this right now let’s go into a tutorial
4:02
i will show you the first
4:05
two macros that i used when i started
4:08
animation
4:09
close this and open a new version of
4:12
excel how i started i had a girlfriend
4:15
who had some kind of knowledge about
4:17
this
4:18
microsoft product ask her to show me two
4:21
things first show me a macro that can
4:24
count up a number in a cell so zero one
4:27
two three four five so she said um i’m
4:30
trying to translate to the new excel
4:32
because at that time it was the old
4:33
excels go to developer visual basic
4:37
hit sheet one select this and type
4:39
create a macro now you can go to google
4:42
i always advise you
4:44
even when you’re advanced to not to
4:46
memorize too much
4:48
and actually
4:49
google search on use old files don’t
4:52
load your mind with
4:54
syntax keep your mind for creativity and
4:57
enjoyment you can say how to write
5:01
macro
5:03
vba
5:06
and then you get all these examples here
5:08
you can
5:09
again people will write a lot much more
5:12
than you need what you need is just
5:14
say they write tons of stuff because
5:16
they advertise and the more they write
5:18
the more visitors google sends so it’s
5:21
hard you know you need to make your own
5:23
basic
5:25
primitive
5:26
you know simple macros and copy paste or
5:29
just go there and copy something try to
5:31
clean it to the almost not usually
5:34
people write in excel in
5:35
vba 10 times more than they need the
5:37
code is and again there’s all kind of
5:40
cocky people trying to say oh you need
5:43
this you need no you don’t three to five
5:45
lines maybe 10 lines will do it the same
5:47
as 50 lines in vba and again if you work
5:50
in a job you might need to follow the
5:53
rules you know
5:54
use option explicit and all this
5:56
nonsense in my opinion and make it look
5:59
complicated so you get paid more you get
6:02
a better job review i always like to
6:04
clean it up so how you start is write a
6:07
very simple macro like i said google how
6:09
to write a marker macro starts like this
6:12
sub
6:13
give a name let’s say count up
6:17
so this is a name and then this
6:20
again
6:21
if you hit return
6:24
automatically the excel will fill out
6:26
the end of the end state you can put
6:27
private
6:29
private means
6:31
the marker is not visible when you have
6:33
a shape and try to attach a macro to
6:36
associate the macro assign a microtubule
6:38
shape it’s not going to be visible so
6:41
for certain macros that you don’t want
6:42
the users to see you put private or you
6:45
can put public i’m not going to put it
6:47
because
6:48
if i don’t use anything the default is
6:51
public and then you can say b
6:53
10 okay so
6:56
range
7:00
b10
7:02
is equal to
7:04
range b10 plus one
7:06
and let’s see how it works check this
7:08
out
7:09
one two three i run the macro from here
7:11
okay one two three four five i can
7:14
also insert a button such as a shape i
7:18
like shapes because they work cleaner
7:19
than the bottoms insert shapes or
7:22
pictures you can choose a picture but
7:25
either picture or shape
7:28
and type in let’s say start
7:35
and assign macro right click assign
7:38
macro
7:40
count up
7:41
and check this out when i click on it
7:44
this number
7:46
will increase its values okay
7:50
another thing i like to do is uh try to
7:54
use this button to initiate a loop so
7:56
because when you animate anything you
7:58
have a clock like this
8:00
counts continuously and as it counts
8:02
there’s certain calculations in the
8:04
sheet and certain charts that takes this
8:07
into co take this in consideration so
8:09
which means the calculation i have this
8:11
as a argument
8:13
and that’s how you get the generate the
8:15
movement so when bitten changes
8:18
the tables and the chart are updated and
8:21
this is perceived as movement let’s look
8:24
for loops you can you can go here and
8:26
type
8:27
loop
8:29
how to
8:30
how to
8:32
loop vba
8:35
and then you can get a lot of these
8:37
tutorials and again the tutorials have
8:40
10 to 100 times more than you need and
8:42
this is because these sites are
8:44
optimized for seo search engine
8:46
optimization
8:48
and the more useless stuff they add
8:50
there
8:51
the more visitors they get and from
8:53
google
8:54
and the more they can sell with vba the
8:57
critical part is learn how to take
9:00
usually a very small fraction of what
9:02
you see there it will do the same thing
9:04
as the big stuffy code
9:06
people like to write big big code
9:10
so i like a loop called do
9:13
is an infinite loop
9:16
the first the beginning of the
9:18
loop syntax is the word do and the end
9:22
is the word loop
9:24
so let’s look at this
9:26
thing
9:27
start it you see it’s running up
9:29
counting up very very fast
9:33
okay
9:35
i hit uh escape
9:37
and as usual you know because microsoft
9:39
will crash i mean the
9:42
that’s how it is it has all this new
9:44
every time they put new stuff new crap
9:47
and the basic functionality is lacking
9:49
i mean
9:50
reliability is just
9:52
zero
9:53
in any microsoft problem it just crashes
9:57
end of memory you know end of everything
10:01
why i mean this is just a basic there’s
10:04
nothing here
10:05
why has to crash
10:07
now i have to shut down your computer
10:09
whatever
10:10
close the window yeah
10:14
look
10:16
the object invoked has disconnected from
10:19
its client why
10:22
okay so
10:24
sometimes you know
10:26
you can end it
10:27
but i’m telling you you need to use
10:30
something called do events
10:32
this is a very extremely important
10:35
statement
10:37
every time the loop runs
10:39
it will stop it for a moment look do i
10:42
need to update the screen do i get
10:44
an escape or whatever command from the
10:46
user
10:47
now it’s not gonna crash okay
10:50
so let’s run it again
10:53
and now escape
10:56
well landscape doesn’t
10:58
work we can stop it from so in order to
11:01
stop it from the same button we need to
11:04
uh do something before that i would like
11:07
to show you something okay i don’t like
11:10
if there’s an easier easier way to do it
11:12
i will do it easier ways instead of
11:14
range b10
11:16
i use square brackets
11:18
b10 and that’s equivalent to range
11:22
b10 round bracket round parenthesis
11:25
check this out
11:26
it’s the same thing
11:28
so here the same thing
11:30
square bracket
11:32
b10
11:34
square bracket close them see much
11:36
easier
11:37
so in order to make this button start
11:41
stop i’m gonna
11:43
i’m gonna go here and
11:47
inside the button i will say start
11:50
pause
11:52
i don’t wanna start top i want start
11:54
pause i want to be able to start the
11:56
macro
11:57
from the place we left it so this is
12:00
just a name
12:02
so go here
12:04
and say count up
12:06
start
12:14
you can not use hyphen because it
12:17
interprets the hyphen as a minus sign
12:19
there’s a subtraction
12:22
so i change the name to start pause
12:24
i go here and say
12:26
assign macro
12:28
start pause
12:29
okay
12:32
and install pause let’s let’s do this so
12:36
let’s copy this
12:40
and say
12:42
go here and type
12:45
conditional
12:48
statement
12:53
in vba
12:55
and look at all these conditions the
12:57
main one is if statement okay
13:00
if else if whatever
13:03
so
13:04
check out like i said people write 10 to
13:07
100 times more than is needed
13:10
just pick up the simplest try it if it
13:13
doesn’t work go to the next site don’t
13:15
even give a chance to the offside go to
13:16
the next one
13:18
till you find something simple okay
13:20
this is also too complicated but you you
13:22
can
13:23
after a while you develop a sense and
13:25
you know how to clean it in seconds
13:28
conditional logic in vba they try to
13:30
make it pathetic you know sound like
13:33
official like whatever let’s close this
13:36
so if
13:37
the syntax is this since i used it
13:39
recently i know the syntax
13:41
if b
13:43
let’s say b7
13:45
7
13:47
is equal to running
13:49
the word running i need to put it in
13:52
in quotation marks
13:55
running
14:00
then b7
14:03
so ctrl v
14:05
b7
14:10
is equal to
14:21
else
14:26
you go here
14:31
and you need to
14:32
end this if statement
14:35
with a line and if
14:39
so
14:41
the program corrected me because i kept
14:43
i
14:44
wrote them together if b7 is running
14:47
then december will be turned into
14:49
stopped
14:50
so let’s see if it runs if it works
14:55
you see running stopped running stop but
14:58
it doesn’t really stop so in order to
15:00
stop the macro stop it from here and put
15:03
a conditional do loop which means do
15:06
while
15:08
and here logical condition
15:13
okay logic condition i just say logic
15:16
condition to give you a general idea but
15:18
actually we need to put a condition like
15:21
b7
15:22
is equal to running okay
15:25
so if b if the condition is ctrl c
15:31
b7 is equal to running if this is true
15:34
if b7 is truly equal to running then it
15:36
runs through the loop otherwise it stops
15:39
the loop
15:40
so
15:41
you can go to google and type do while
15:44
or conditional do loop
15:47
so this should
15:49
work
15:49
if you have
15:51
running here in this cell if you have
15:54
running the loop should run
15:56
if you hit and it turns into stopped it
15:58
should stop it okay
16:00
so let’s run it again
16:03
you see running stopped it stops the
16:06
loop
16:06
so it printed the loop does the macro
16:10
prints here stopped
16:12
and then it stops the loop
16:14
if i hit again it should change the stop
16:16
into running because in the if statement
16:20
if this is equal to stopped if this is
16:23
not running so else
16:25
it turns into a running and it starts
16:28
the loop again
16:30
check this out you say
16:31
start stop macro
16:34
the other
16:36
important macro is a
16:38
macro associated with a spin button
16:41
that you can control a parameter
16:43
but we will do this in the next video
16:47
thanks for watching
Very nice video