>.
This tutorial brings briefly introduces an older airplane/Ninja cat animation, then it shows how to flip a logic (Boolean) variable using the operator “Not”. Most of the tutorial however, is centered toward the Active X control button in Excel animation and the associated macro. It shows how to create and customize this button, then it shows how to introduce an upper and lower “rollover” property for this spin button. Finally it demonstrates how to turn the button’s variable into an argument of a one-dimensional look-up-table (LUT). The Excel file is available for download by clicking the green download button above. Download by hitting the light green button above. Enjoy!
Tutorial level: beginner
Thanks for your support!
0:00
hello my friends and welcome back today
0:02
we will continue our investigation into
0:04
the control macros the most important
0:06
macros in animation
0:09
but before that i’m going to talk a
0:10
couple of minutes about one of my old
0:12
older models here
0:15
this is made in
0:16
2019
0:18
can start it here
0:21
and i can zoom in
0:23
to make it look more dramatic
0:27
i can change okay there there’s a cat
0:29
there’s a walking cap here of course
0:32
it’s not really realistic because uh
0:36
her legs or his legs don’t move fast
0:38
enough
0:40
but
0:40
you can see it here you can change the
0:42
speed of the background
0:44
and if you actually stop it if you stop
0:47
the background
0:49
you can see what happens here the
0:51
the plane actually moves back and forth
0:53
up and down
0:54
there’s a maybe two or three
0:57
sinusoidal sine functions
1:00
harmonic functions
1:03
and
1:05
i can even go
1:07
backwards
1:09
you see
1:10
but let’s uh let’s go to a fast
1:13
speed
1:15
and introduce the other airplanes
1:39
and you can see here there’s a minimum
1:41
amount of
1:42
spreadsheet formulas actually
1:45
most of it is in vba
1:48
if you look here format let me see tool
1:51
macro vba visual basic editor
1:56
it’s a fairly simple
1:58
all these things are buttons here on the
2:00
on the
2:01
on the bottom yeah
2:03
and this is the macro actually what you
2:06
see i believe
2:10
let me see
2:13
from the top here
2:16
all the way to here it ends here that’s
2:19
that’s all it is
2:20
these are some
2:22
variable declarations
2:24
and how is this done
2:27
it’s done with sprites which means
2:29
images
2:31
let me zoom out so you can see
2:35
okay these are images of the airplane
2:38
of the airplanes in various
2:41
stances
2:42
let’s say shooting or shooting the smoke
2:45
if you see the smoke is a little bit
2:47
different in each picture
2:48
there’s a french there’s a british and
2:50
the german plane
2:52
and
2:53
i’m going to close this
2:55
if you look at
2:57
this is where i start building these
3:01
sprites these are all built from these
3:04
kind of shapes like if you go to auto
3:06
shapes
3:07
lines
3:09
see
3:10
i go like this
3:12
and then i can go and type
3:15
right
3:16
right click and
3:19
edit points
3:21
and i can let me see
3:26
i can add points okay
3:30
and
3:35
let me see smooth point
3:37
see i can always go to each point
3:41
and change the sprites
3:43
i’m sorry this this
3:45
spleens
3:47
so
3:49
smooth point
3:52
and all of these are
3:55
you see like
3:56
i’m giving example this is the body it’s
3:58
built just like this using auto shapes
4:01
and then
4:02
feeling the shapes like uh let me see
4:04
like this you know
4:06
say
4:09
fill effects
4:11
two colors
4:15
you pick up a yellow you pick up
4:21
and
4:22
you know then you can
4:25
draw
4:26
rotate actually you can grab it from
4:29
here
4:30
and rotate
4:31
so that’s how i created everything
4:34
like for instance this reflection is
4:36
just a white
4:38
it’s just a white uh
4:41
oval
4:42
and
4:44
you see how it is
4:46
then i worked on the propeller on the
4:48
smoke on everything and
4:51
so i made three planes a french a
4:53
british and a german each plane has six
4:57
sprites
4:59
and i’m gonna get out of here without
5:01
saving
5:04
so now let’s continue our
5:06
incursion
5:07
our investigation into these
5:10
control macros
5:12
first i want to show you in
5:15
the second in the second
5:18
worksheet here
5:20
you see this one i had this kind of
5:22
macro this
5:24
and since a loop underscore state this
5:28
is a name the name of a variable of a
5:30
boolean or logic variable
5:32
since this is boolean can have only two
5:35
values
5:36
true and false you can replace all these
5:39
uh block of code this
5:42
if statement
5:44
with loop state is equal to not loop
5:47
state so
5:48
and again here you don’t necessarily
5:50
need parentheses but i put in
5:52
parentheses i put it in parenthesis so
5:54
you is more visible
5:56
so if loop state is
5:59
true
6:00
not true means false
6:03
if loose state is
6:05
false not false means true so you can
6:08
replace this
6:09
with this
6:11
and we continue to
6:13
introduce another macro
6:16
this macro is
6:18
the control button the spin control
6:20
button
6:21
macro so let’s copy sheet 3
6:25
move our copy
6:27
to the end
6:30
and let’s call it
6:33
sheet four
6:40
we delete this button we don’t need it
6:44
we delete this number
6:46
we open um we go to developer visual
6:49
basic
6:51
goshi in sheet 4
6:53
delete this one
6:56
and actually
6:58
let’s delete everything
7:00
i don’t think we even need this
7:02
declaration
7:04
just go to
7:06
developer
7:08
go in design mode
7:10
and insert we are looking for this
7:12
button you see spin button activex
7:15
control
7:16
this is uh
7:18
it works very well in animation
7:22
there’s another spin button there
7:24
is let me see insert
7:27
i believe this one
7:29
form control this one doesn’t work very
7:31
well
7:32
it kind of
7:33
lags it’s not so responsive so i like
7:36
this active active x control so this is
7:39
design mode if we are in design mode
7:41
like now you see is highlighted we’re in
7:43
design mode we can design we can
7:46
edit this button if we click again
7:49
it’s not highlighted we can use the
7:51
button okay
7:52
so let’s go in design mode
7:55
properties let’s uh call this a certain
7:58
name let’s say um
8:00
parameter
8:02
param
8:03
parameter might be a reserved word so
8:05
param
8:07
so go from
8:09
there’s a minimum and a maximum value
8:11
for this button we can choose anything
8:14
we want
8:15
it
8:17
when we click on it can go between
8:19
minimum and maximum in increments of one
8:22
increments or decrements of one so from
8:25
0 to 9 which means 10 steps
8:29
and
8:30
another thing let’s uh i can show you we
8:32
can
8:34
change the colors
8:35
let’s say the bottom color is red now
8:38
the um
8:41
the arrows okay the arrows we can make
8:44
make them green uh blue
8:47
you don’t need to but i’m just showing
8:48
you how to do it so what is important is
8:50
the name here make sure it doesn’t have
8:53
it doesn’t have hyphens because it’s
8:54
interpreted as a subtraction it only has
8:58
it can only have underscores
9:00
if there’s a minimum maximum there’s a
9:02
delay
9:04
you can go
9:05
to much higher values or lower i would
9:08
not go below 20 milliseconds
9:11
why because if is like zero when you
9:14
click once it might jump several times
9:17
several units up otherwise if you keep a
9:19
delay like 50 is a decent value anything
9:22
between 20 to 50 is decent then when you
9:25
click once it moves up once when you
9:27
click once here it moves down
9:29
one by one
9:31
now we double click on it and it
9:33
generates a macro in sheet 4 because the
9:35
button is in sheet 4
9:38
it generates about a macro
9:40
it generates a shell of a macro i mean
9:43
when i say shell it’s not a
9:45
how do you say a scientific way to call
9:47
it
9:48
because it’s the first statement and the
9:50
last statement the the beginning and the
9:52
end statement of the macro so inside
9:55
what do we put
9:56
okay param
9:58
right here
10:00
copy
10:01
so let’s print the value of param in b9
10:06
b9 in square brackets
10:09
is equal okay b9 not b8
10:13
is equal to param
10:16
okay
10:16
let’s go now in this uh
10:19
i’m sorry
10:21
in the worksheet
10:23
and go out of design mode
10:26
and again you can go in and out of
10:28
design mode
10:29
in the editor or in the worksheet here
10:33
okay
10:34
so now we are out of the design mode and
10:38
check out the cell number b9
10:42
let’s go to
10:43
home and
10:47
center this
10:49
so developer okay you see it goes from 0
10:53
3 4 5 6 to 9 and when it goes back to
10:57
zero it’s stuck there it doesn’t go
10:59
lower when it goes
11:01
up to nine it’s stuck there it doesn’t
11:02
go higher
11:04
if you want to go higher you can
11:05
increase this max value okay you see
11:08
go in design mode click properties you
11:11
can increase the the max value to let’s
11:14
say 11 and the mean value to
11:16
[Music]
11:18
minus 1 okay
11:20
and then go out of design mode
11:22
and you see it goes down to
11:25
-1 and up to 11.
11:29
let’s go back and
11:30
design mode
11:32
properties are here
11:34
let’s go from zero
11:36
to
11:38
nine
11:39
and i want to show you this is one trick
11:41
okay one trick how to generate a spin
11:43
button a very reliable spin button the
11:46
second trick is uh
11:48
i’m showing you how to make a roll over
11:51
so
11:52
you can use only the upper arrow or
11:55
lower arrow when it goes to the maximum
11:58
it rolls over rolls rolls over the next
12:01
click and jumps to the minimum and vice
12:03
versa when it goes to minimum
12:06
it will roll over and jump to the max
12:09
again so let’s do this
12:11
in order to do this to do this roll over
12:15
we go here and
12:17
i’m gonna type here
12:19
roll
12:20
over feature
12:23
feature of the button
12:27
okay and say if
12:30
param
12:35
greater than 9
12:39
i’m sorry
12:42
if param
12:46
greater than 9
12:48
then
12:51
param
12:53
should be equal to
12:56
zero okay
12:59
and
13:00
let’s copy this
13:02
this is the upper rollover let me
13:04
demonstrate you so if b9 is equal of b
13:08
lines per parameter then copy this ctrl
13:11
c
13:12
and
13:14
through v
13:15
so okay so
13:18
these are
13:21
ctrl x i cop i cut it i’m going to put a
13:24
comment here
13:26
ctrl v it’s a roll over feature of the
13:29
button
13:30
on
13:31
top of
13:33
range
13:34
and here
13:36
copy this comment
13:38
and
13:40
paste it here
13:43
through the rollover feature on the of
13:46
the button on the
13:49
bottom
13:51
on the bottom of the range
13:54
okay
13:55
so
13:56
and in order to
13:58
do this if param smaller than zero
14:01
actually smaller than zero then param
14:03
should be equal to nine
14:06
okay
14:07
and we need to go here and go in design
14:11
mode
14:12
properties and instead of zero and nine
14:16
we need to increase the maximum to with
14:18
one element one unit so
14:21
nine plus one is ten and decrease the
14:23
minimum by one unit so minus one
14:28
and uh
14:31
check this out
14:33
click
14:34
up up up up
14:36
eight nine
14:38
now we should when i click one more time
14:40
should go to zero zero see one two three
14:42
four five six nine zero and here down
14:46
three two one
14:48
zero and if i click down again it
14:50
shouldn’t go to minus one should go to
14:52
nine
14:53
you see
14:55
this is bottom roll over
14:57
and top roll over
15:00
another thing i want to show you is how
15:02
to make a very cool
15:04
non-linear lookup table
15:07
because sometimes in a lot of these
15:09
models these animated models you don’t
15:11
just need
15:12
a parameter to go linearly from 0 to 10
15:15
or 200 into
15:17
one increment 1 2 3 4. you maybe want
15:20
something like
15:22
1 2
15:23
5 10 20 50
15:26
100
15:28
kind of like a logarithmic thing this is
15:31
very useful is used in the electronics
15:33
in um
15:35
oscilloscopes if you look at the buttons
15:38
it is always like say one microseconds
15:41
two microseconds
15:43
five microseconds ten microseconds and
15:46
then a hundred
15:48
uh i’m sorry
15:49
uh 20 microseconds it’s like this so
15:52
let’s let’s make a lookup table here
15:54
define an array let’s say um
15:59
let’s call this param
16:02
okay it didn’t like that that name so
16:04
param array is equal to array
16:08
and here let’s um
16:12
let’s
16:15
in the array for
16:17
again param can move from zero to nine
16:21
okay because if it moves to ten so
16:23
higher than nine is becoming zero if it
16:26
goes below zero it is going to nine so
16:28
between zero to nine so we need ten
16:30
values here
16:32
let’s say we can put um
16:34
one 1.2534
16:37
okay then
16:40
2.456
16:43
then let’s put some text
16:46
[Music]
16:48
this is a test okay
16:52
then
16:53
let’s put here another text ooh
16:58
okay
16:59
then let’s put another text so zero one
17:03
2 3
17:05
4
17:10
then let’s put param
17:14
so param is just
17:16
the parameter
17:17
one is uh
17:18
it can be between zero and
17:21
nine then again param
17:25
then um
17:28
let me see 0 1 two three four five six
17:34
seven
17:35
seven
17:37
point seventy seven
17:40
eight point eighty eight
17:43
and then prime again
17:46
okay so
17:49
and b8 instead of param let’s say is
17:52
equal to this
17:55
array ctrl c
17:59
of
18:00
having as argument
18:03
what did i do here i defined an array is
18:06
a lookup table for each of these nine
18:09
val 10 values of param
18:12
it will map into
18:15
uh
18:15
[Music]
18:17
an array of numbers and text
18:20
and then i say every time param changes
18:23
value
18:25
you don’t print in
18:27
b9 you don’t print parameter print
18:30
param of param array the array
18:34
when param is the argument
18:38
let’s check it out
18:40
you see
18:42
let’s start with 1.23
18:46
let’s uh
18:47
copy this ctrl c
18:50
and put it here so we can
18:52
see better
18:55
and also i want to
18:59
so param let’s print param in b8
19:04
so in b8
19:07
b8
19:09
we is equal to param
19:13
okay
19:23
so in b8 we we print param
19:26
and in b9 we print the array
19:29
function of the argument param okay so
19:32
check this out
19:34
uh this is array
19:46
so
19:47
if param is zero is one point you see
19:49
one point two three four then parameters
19:52
one
19:54
is becomes two point four whatever
19:57
if parameters two it becomes this is a
19:59
test if parameters three becomes woo
20:02
if param is four it becomes four written
20:05
four
20:06
if param is five becomes param if
20:09
parameter six becomes param right here
20:12
if parameter seven becomes seven point
20:14
seven seven if parameters a becomes
20:16
eight point point eight eight if param
20:19
is nine because param which is nine so
20:21
this is another trick okay very very
20:24
important so
20:25
this uh tutorial we learned
20:28
three important tricks actually four
20:31
one was uh to replace all these
20:33
complicated if statements
20:36
with um
20:38
just an inversion
20:40
like this uh what was this loop state is
20:43
equal to not loop state
20:45
so that will toggle the loop state
20:47
parameter from false to true and back
20:50
from true to false
20:52
the second trick will learn this this
20:55
tutorial is how to make a button
20:58
and how to customize it
21:00
the third trick is how to do a roll over
21:04
upper roll over and lower roll over
21:06
right here
21:08
and the fourth trick let me see the
21:10
fourth trick
21:11
is how to make this non-linear this
21:14
lookup table so we don’t just use param
21:17
we use of an array which takes
21:21
param as an argument
21:24
this being said
21:25
i’m going to save this
21:27
and you will be able to download this
21:29
thanks for watching and see you in the
21:32
next tutorial
Plse give basics of vba and excel .all are very advanced