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