In this tutorial, a very simple 2D house will be built from scratch. Three parameters, will then be set up to control the scale, x offset and y offset.
A demo macro is then created to animate the house with the three parameters changing in time.
The purpose of this tutorial is to demonstrate at a very basic level how a wire-frame animation can be created on a 2D scatter chart.
Enjoy!
0:02
hello my friends and welcome back today we will be talking about wireframe objects
0:08
namely we will be building a house a two-dimensional house and i will show you how to
0:14
create this and how to scale it and translate it like here
0:20
check this out there are essentially two different uh houses on top of each other
0:26
i’m gonna translate one and i don’t know why it’s not doing anything okay so translate on vertic on
0:34
horizontal translate it on vertical and then scale it
0:39
down and up
0:44
or i have a macro that handles both all three
0:50
parameters the scaled x translation and the y translation
0:56
check this out is using sinusoidals sinusoidals are
1:01
also called harmonic functions and you can see the definition here go to google then type
1:09
sign function and it shows you the uh the shape it has an amplitude
1:16
the regular uh sine function has amplitude of one goes between minus one and plus one so varies
1:23
between plus one and minus one there’s also period appeared is the distance between
1:33
two consecutive actually let’s say two tops or two bottoms
1:40
and half period is this distance between two consecutive
1:46
crossovers here in the origin along the x-axis
1:52
so this being said i already built a model here i’m gonna open a new sheet
1:59
so insert a worksheet so
2:06
this one we’re gonna take a chart so insert chart
2:17
i like this one and let’s name here the x-coordinate and the y-coordinate
2:27
let’s also select
2:34
scaling scaling factor
2:40
center this a scale factor
2:46
an x translation
2:51
x translate a y translate
2:56
so copy this ctrl v and change the x to y
3:04
and let’s start building a house i will use
3:10
four columns so you go here select data and
3:16
add a series house name it house
3:22
here we select from let’s say 11 to
3:28
40 and the y
3:34
the y coordinate is taken from c 11 to c 40.
3:43
okay and i want to make sure
3:51
let me see format axis let’s make this from
3:59
minus 12 to plus 12. why these values because i i already did
4:06
this and like i’m kind of choosing something convenient so minus 12 plus 12
4:13
and here actually let’s choose from -15
4:20
to 15 weight 15 here plus 15
4:25
and minus 15.
4:32
oh something is wrong but let’s see minus h maximum minimum minus 15
4:41
plus 15 maximum
4:46
major 1 1 okay
4:51
also here on this axis minus
4:57
minimum minus 15 to plus 15
5:07
and one one okay
5:13
let’s make this chart larger here like this
5:19
we can also click on it and check out the dimension make sure it’s
5:24
size is uh square so let’s say 7 7
5:30
okay and lock here so nice square
5:35
except it’s not quite square because of this word house so let’s just delete this
5:42
nice square also you can you can play with this extend it as much as you
5:48
can okay and uh
5:54
i’m gonna fill it with the say a yellow light yellow
6:03
this one wait a minute what is this this is the border i don’t care about
6:09
the border i want to feel the feel right here
6:16
okay that’s good is it good
6:21
more colors okay
6:28
so um let’s start the house with a point
6:34
since i did this before i’m gonna start it at five so minus five
6:41
minus five and five
6:47
so minus five five then we go to minus five zero
6:58
zero and we have we have the first line click on the line
7:04
go here and fill and uh let’s make it thicker like three
7:12
yeah and let’s build a line between here on the base okay from minus five to plus
7:19
five so uh the y will be zero zero for both points
7:26
and here minus five and here plus five
7:32
okay so we drew a plot point from minus five zero to plus five zero then we
7:38
we’re going to draw a line up to uh five let me see five five
7:46
so five five so we have this
7:51
let’s build the door okay so the door was stuck no let’s build
7:57
let’s build a window a window first i’m gonna do you know this this uh
8:06
oh no go to from minus four to minus one so window here so this is the body of
8:13
the house let’s call it body
8:19
let’s call this window and i’m making a mistake now because i
8:25
want to deliberately make this mistake so you understand something let’s start here at window from
8:33
-4 to [Music]
8:39
minus one so the x goes from minus minus four
8:46
to minus one okay
8:51
and then the the y stays at two so two two
8:57
okay we drew this line but there’s a problem we don’t want that we don’t want this tilted line so what
9:03
we do check out i highlight this and leave an empty space
9:10
so now you have a single line drawing all these objects but the line is broken because we left an empty space
9:18
this is a very very useful property of charting in excel so let’s continue
9:23
let’s go from here up there to minus one four so
9:29
minus one four
9:37
then from to -4 4
9:45
and then to the first point right here so -4
9:51
2 so we close the window
9:56
uh let’s do another thing actually let’s make this window a little bit bigger so instead of -1 here let’s put
10:03
zero zero
10:11
and bring a separator right right here in the middle so i want to make that the same mistake
10:18
okay to get um i’ll just say errors so you learn something so let’s
10:26
start from here so minus two plus two we try to build a vertical separator in
10:32
the middle so minus two plus two
10:39
okay i’m sorry minus two plus two okay
10:49
let’s start here then we can see it better so minus two plus four okay you see that oblique line i don’t
10:56
like it but i’m gonna show you and then go to -2
11:08
2. everything looks good except this oblique line if we don’t want it there
11:14
we take the two the last two points drag them a little bit down and let’s uh introduce a vertical
11:20
separator here between
11:26
minus four and three so minus four
11:31
and three and also my zero
11:37
and three okay so now we have a vertical separator
11:43
let’s uh build a door from two
11:49
so right here door
11:57
i’m going to repeat the mistake so we learn even more so we we build a door from
12:03
plus to zero so two zero
12:09
go up to okay you see that of the oblique line we don’t want it there
12:14
we go to two four two [Music]
12:19
four then we go to four four
12:28
and then we go to four zero
12:34
zero so in order to get rid of this parasitic line we just take the door
12:41
put it down i mean bring it down so there’s an empty split space here
12:47
let’s now build a roof let’s build a roof right here
12:54
okay and
13:01
select data okay let’s uh select the data from these the following two columns
13:09
so add a series so this is from here to 40
13:18
and on y we need to delete that first and then select
13:27
let me see okay
13:32
okay and the roof let’s start right here minus seven
13:38
four minus seven four
13:45
then go to plus seven four actually we should go to five okay so my
13:51
plus seven five four is too low
13:56
so here five two click on this
14:02
we go let me see
14:09
three okay then from this point now we are here we go
14:16
right here okay so three and nine
14:22
so three to nine then we go to minus three
14:32
to 9 and
14:38
let’s click on this
14:44
somehow we only thicken one line okay so now we thicken all the everything right here okay so
14:50
you see that’s what it is and then let’s go back to -7 just this copy this ctrl c
14:59
ctrl v so we have the house we can build a haste uh not a haystack a smoke stack or
15:06
whatever but this is good enough so the next step is gonna be introduce
15:11
the translation two translations one along the x-axis one along the y-axis so
15:18
horizontal and vertical and i will say first of first let’s just scale it
15:23
so introduce scaling as a parameter then introduce x x translation as a parameter and y
15:31
translation as a parameter for this let’s take all this data and put it here everything will be blue now okay
15:37
turns into blue and what we will build here we build the second house that will be
15:45
scaled translated in x and translated in y
15:50
so let’s first put a scale factor of one here x translation of zero y translation
15:58
of zero okay and let’s build some buttons here so that we can change them
16:04
i’m going to go to [Music] developer go in design mode insert
16:12
you see this guy here spin button activex control
16:18
let’s draw a horizontal button here
16:24
properties call this scale
16:31
factor factor okay we’re gonna copy this ctrl c
16:37
and go between i don’t know between 0 to 10
16:44
okay between you know between 0 to 20.
16:50
okay now we are in design mode okay we clicked on this
16:55
so when we are in design mode we can change the we can edit the button but when we are out like now we can use
17:03
the button the button is not finished so let’s go back in design mode double click on it and it creates it created uh
17:10
how do you say a seed of a macro the first statement the last statement in the right syntax so now scale factor
17:18
so in cell what is this b3 in b3
17:25
so in square parenthesis b3 is equal to
17:32
let me see equal to scale factor control v dot value
17:39
okay there are other you see here it doesn’t need to be value okay it can be
17:46
formula or whatever in this case we use value you can use that formula there’s other
17:51
things the default is value so you don’t need to put it i don’t put it but
17:57
in order to be rigorous well i’m going to use it now value
18:03
and when i hit return you see the v is going to turn into capital which means it recognized that
18:09
this is a dedicated word so okay
18:15
scale factor value divided by 10 i want to divide it by 10 because it goes between 0 to 20. i want the scale to go
18:22
in small steps from 0 to 2. okay check this out
18:27
go out of design mode we can use the button okay it goes from zero
18:33
to two in in steps of zero point one let’s keep it at one
18:41
let’s now uh copy this button go back in design mode from here or we can go in design mode
18:47
also from here clicking here but we already are in design mode
18:53
so copy and ctrl v
18:59
so this is the x translate so properties
19:06
let’s uh copy this ctrl c and
19:12
name this button x translate go from -10
19:21
to 10. and
19:28
now ctrl c copy this button paste it here ctrl v
19:35
being a vertical translation i like to make it like this so just changing the
19:42
shape factor can turn it horizontal or vertical
19:47
so property properties instead of spin button one control v
19:53
wait ctrl v let’s call it y translate
20:02
okay and go from minus 10 to 10 that’s good everything is fine and dandy
20:08
um double click on this and we have something created let’s copy this control actually no
20:14
let’s uh first insert something so b 5 x translates so
20:23
b5 is equal to
20:34
let’s copy this draw c ctrl v and change this to x
20:46
x x and let’s copy the whole macro ctrl c
20:53
ctrl v so here y translate
20:59
y translate is equal to b goes into b seven
21:04
okay seven let’s see if they work we need to get out of design mode
21:12
this does this one doesn’t work this one works no it does work it’s just slow
21:17
i don’t know why this new excel is just slow so it goes from -10 to plus
21:25
10 okay the same here from from minus 10
21:34
to plus 10 okay and now let’s write the
21:41
scaled interest translated value of the house the coordinates of the house in these two columns so
21:48
x y let’s call this
21:53
scaled
22:00
scaled and translated okay so how do we scale it
22:06
we scale it by multiplying with this value this scale so x
22:12
is equal to the original x times
22:18
the scale okay but here we need to be careful because if we copy this down
22:26
he is going to move this reference down so we we need to lock it on this row in
22:32
order to lock it on this row this is called absolute referencing otherwise the default is relative
22:38
in order to lock the row when you copy down we need to put a dollar sign in
22:43
front of the row number which is three okay so here in front of the three
22:49
in front of three we put the dollar sign okay and also we need to add
22:54
the x translate factor so plus
23:00
this this value here okay and again
23:05
we need to reference put the dollar sign flow in front of five but much easier to hit f4 several times
23:12
it scrolls through several absolute referencing option options okay so i hit f4 one
23:20
once f4 twice that’s what i need hit return
23:26
so now i can copy here and verify if everything is okay is not
23:32
okay because i tell you why we need to
23:39
absolute reference the scale and the translate
23:45
both the row and the column so dollar sign in front of both okay
23:51
dollar sign uh just hit f4 once
23:57
twice three times okay and also this one hit the f4 once
24:05
okay so now we can copy this right here
24:11
let’s verify everything is if everything is okay it’s not because we need to use
24:16
we need to add the y translate to the y coordinate and still multiply it with a scale okay so this is it
24:23
so now let’s copy this down okay
24:29
looks good except let me show you uh let’s first go to translate zero
24:37
translate zero we have the identical house but we need to delete some points right here okay so delete this
24:44
check out the image as i delete this so delete let me see
24:52
delete delete delete you see all these artifacts are
24:58
deleted so we have this and
25:04
check this out scaling i go low to 0.4 0.3
25:11
zero nothing is there okay or up now is one
25:17
one point one all the way to two let’s go back to one
25:24
and the x translate should translate it to the right
25:30
you see steps now is seven the offset is to the right
25:35
seven units it goes to ten or back
25:41
i mean translate it to the left with negative numbers let’s put it as
25:47
zero also y translate should do the same vertically
25:53
go up 10 units or go down then units
26:05
another thing we can do let’s do this let’s write a macro to
26:10
automatically change these three numbers so we have some automatic movement and
26:16
scaling of the house similar to what happened with a roller coaster i mean people don’t understand i
26:22
mean this is simple this is basic stuff we have a few parameters we change them and the
26:28
house or the roller coaster image moves it looks like it’s moving it looks like the perspective changes but essentially
26:36
you act on a few parameters so let’s do this go to
26:44
the visual basic editor and write a macro let’s call this
26:51
animate okay sub
26:57
subroutine animate and parenthesis hit return
27:02
program the program automatically added and sub let’s make a button here to run the
27:10
animation insert where is that [Music]
27:16
shapes it takes a while
27:24
call this on
27:29
off okay go back to home and center them
27:36
center the text increase this by 16
27:42
wait highlight it and increase it to 16 okay it’s good
27:49
and assign macro i’m going to assign the house run
27:57
okay and in the house run what do we do first we want this to be an on off
28:02
button if you watched my previous tutorials you see that we need to declare a
28:10
variable a boolean variable variable which is a logic variable boolean means
28:17
logic it can have value true or false it comes from the mathematician george bull
28:24
who is one of the pioneers of computation mathematics
28:29
so dim let’s call the variable
28:35
on underscore off as
28:40
boolean okay hit return so this we declare this logic or boolean
28:48
variable right here on top which means it will have a scope valid on the whole
28:55
worksheet on the whole object and it will have memory
29:00
so we can use it to toggle the running or the running and stopping
29:06
the macro so in the animate sub let’s say
29:11
on underscore of so the boolean variable is equal to not
29:21
not on underscore off
29:27
so this will toggle the on off variable which has memory from true to false and vice versa
29:35
another thing we need to write a loop a do loop so do loop starts with with the word do
29:41
ends with a word loop okay and in between there’s some calculations
29:47
okay in order to be able to start and stop from the same button we need to make it
29:53
do conditional do okay so do only while
29:59
on off is equal to true
30:05
okay so make sure you go and watch the other tutorials it explains in details how
30:12
this button works how the spin buttons work how the on off button all this stuff
30:19
so inside let’s uh focus on these three parameters so b3
30:25
okay b b3 is equal to
30:33
now i use a sine function you can go here and read about sign just type sine
30:38
function or or sin okay is the same
30:43
you get all this and read about this is what the circle describes
30:50
rotating circle the the radius the projection of the radius of a rotating circle
30:55
on a vertical looks like in time or with respect to the angle of the
31:01
rotation okay but this is used a lot as time varying function is very very important
31:08
if not the most is one of the most important function in animation
31:13
excel or non-excel this you’re gonna use this like bread and butter every day
31:19
many times okay so let’s go back and uh
31:25
b3 is equal to let’s say sine of
31:30
i and i i’m gonna
31:36
implicitly define a variable i an increment okay variable is equal to
31:42
i plus one
31:47
let me see plus it’s dark here i cannot see that the
31:53
keyboard so i is equal to i plus 1
32:02
so b what was that b3
32:09
b3 is equal to sine of i over 10 let’s say or 20.
32:15
i kind of know the values here you need to try if you leave it i it’s gonna move very
32:22
fast every essentially every pi which is 3.14
32:30
units it goes through a whole cycle you don’t want that you want to move much slower
32:37
but and also another thing you need to use i explained in the other tutorials you
32:43
need to use something called do events do events okay
32:49
this means when this this when this this loop is run the program
32:56
usually doesn’t update the screen doesn’t update the chart and especially in the new excel and you
33:02
need to tell the program to update the chart i usually put it several times in the
33:08
new excel the old excel you don’t need to control c will be ctrl v make sure
33:15
is doing the two events it interrupts and updates everything and then run
33:20
continues the loop so check this out okay
33:27
stop this b3 not b3 oh yeah b3 i don’t know why it’s not
33:33
doing it b3 is equal to
33:44
okay okay okay okay so it’s running the only problem is i don’t think we assign the right macro
33:51
assign macro animate is this called let me see is it called
33:57
house run animate it’s not called house run i don’t know where we got a house run from
34:04
okay so animate check this out
34:12
the sound macro animate
34:17
okay you see the scaling goes from
34:24
plus one to minus one fairly slowly if we want to decrease the
34:31
speed okay here instead of 20 we can put like
34:36
50. now we need to stop this 50.
34:41
now it’s gonna run through the cycle much slower okay
34:49
whatever let’s stop it from here and another thing i want to add
34:55
translation in the same using a harmonic sinusoidal function sinusoidals are also
35:01
called harmonics harmonic functions so b5
35:08
okay let’s copy this ctrl c
35:14
ctrl v ctrl v so b3 b 5
35:20
and b7 and
35:25
here let’s put different numbers like i don’t know 40 and 30.
35:34
and check this out another the house
35:41
it only moves it to plus and minus one offset so let’s put here five times
35:47
sine
35:53
and again five times
36:20
so what we have now we have three buttons
36:25
that can manually change the scale of the
36:31
of the house from zero to plus two and we also have two buttons
36:38
for horizontal and vertical translation
36:44
and another thing we created a dynamic macro that takes an infinite
36:50
loop infinite conditional loop so do while and
36:56
it increments an index i and uses a use it as argument in
37:04
sinusoidals in sinus functions to
37:09
nicely and gently change the scale the x translate the y translate i can
37:15
show you one more thing let’s uh disable the scale
37:20
okay so comment it out here with an apostrophe
37:27
and instead of b5 is equal to i over let’s
37:32
put d5 is equal to our 30. so now they both
37:39
the same thing so what you will see the house this house will become let’s uh set it to a scale of
37:47
small like 0.2 and the house will move like this
37:54
on a 45 degree straight segment
37:59
see moving up and down up and down like this
38:06
if we go and put a minus here we should move it like this okay
38:15
see
38:21
i’m going to stop it if instead of
38:26
5 sine we put 5 cos cosine okay
38:32
complementary function complementary harmonic function
38:38
you should describe a circle check this out
38:45
okay so these are small things you need to play with them and get a feel of them
38:50
maybe read a little bit just not much mostly trial and error read as little as
38:56
you can but you might have to read a little bit if i put here
39:02
minus okay minus cos
39:07
it should rotate the other way so counterclockwise okay
39:13
i don’t know why it’s stopping this new excel is unpredictable
39:19
if we want to make it move much faster check this out instead of 30
39:26
let’s put three it should turn very very fast
39:34
okay we can even even leave it over two
39:41
over two
39:46
another thing if we make the uh
39:52
the sign double the frequency let’s make the cosine two times slower instead of to
39:58
make it for four this will should move in an eight okay
40:04
you see it moves in an eight but anyway this is something that we will talk later it’s
40:10
called lissajou or whatever the way we combine two sinusoidal function even the cosine is a
40:17
sinusoidal but uh d phased so with a phase difference of
40:23
pi over 2 i think 90 degrees and this being said this will be
40:29
available to download this demo file and next time we will continue with the
40:37
three dimension will i will explain how actually no before of that i will need
40:42
to show you how to rotate the house so we will add rotation to the house
40:49
we will play a little bit then we will get get into deeper into three-dimensional
40:54
representation and animation and this being said thanks for your attention
