This is a continuation tutorial, the third in the wire-frame tutorial series. Here we take the 2D basic house we created on an 2D scatter chart that had scaling, translations and rotation and replace all the cell formulas with two user defined functions (UDF). These basic tutorials use elementary math/geometry and trigonometry (middle school and high school level). As the series progresses, the tutorials will reach 3D level and the creation of more complex shapes and landscapes (i.e. roller coaster and beyond).

Thanks for your support!

0:00

hello my friends and welcome back

0:03

so the previous tutorials we created a

0:06

house you see this blue thing here using

0:09

a series of points these points will

0:12

draw a line on the

0:14

chart

0:15

and if we go on the line we can select

0:17

it to be non-smooth okay so broken so

0:21

that’s what we want always when we build

0:23

this like a roller coaster for instance

0:26

and also if we interrupt the line we can

0:29

interrupt the line by

0:31

deleting points here so leaving empty

0:33

spaces

0:35

and then

0:36

even though we have only one line

0:39

right here you see

0:40

the blue line is

0:42

continuous

0:46

column data look on two continuous

0:48

column data but

0:50

since we

0:51

provided these empty spaces these blank

0:54

spaces we avoid uh we can jump from

0:58

groups

0:59

of uh

1:00

lines together so

1:02

the roof is a group okay here the roof

1:05

you see

1:06

the door is another group

1:08

between them we leave blank spaces then

1:11

what we did is we did a scaling with a

1:14

scale factor between 0 to i believe 2

1:17

then we did x offset so x translate x

1:21

y translates so y offsets of vertical

1:24

offset

1:25

and then we added rotation

1:28

so the

1:30

red house is the blue house

1:33

except it has scaling okay let me

1:36

demonstrate

1:38

scale up

1:39

scale down

1:41

it has

1:43

horizontal translation so x offset

1:47

and vertical translation okay

1:51

so y offset

1:53

and the greenhouse

1:55

if i click on it you see the data is

1:56

coming from here

1:58

the greenhouse is it’s a rotated version

2:02

of the red house

2:05

we can

2:06

see we can rotate it

2:12

in both

2:13

directions in both senses

2:19

so now i want to show you how using a

2:22

fairly simple user-defined function

2:25

we can replace four columns of data with

2:27

only one column of data

2:29

with only two columns of data

2:32

and in the in each cell there’s only one

2:35

function a user defined function

2:38

the syntax of the user defined function

2:40

is this

2:42

first you write a word function

2:46

and then the name you give it a name and

2:48

give your name custom name

2:51

make sure it doesn’t it’s not there is a

2:53

word

2:54

make sure you don’t put

2:57

hyphen because it’s interpreted as a

2:59

minus

3:00

hyphen

3:02

and also in parentheses you have a list

3:04

of arguments

3:06

in our case we will use

3:07

six arguments

3:09

then inside the body of the function we

3:11

have some math some logic and then the

3:13

statement

3:15

and

3:16

function

3:18

i already wrote a very simple one for

3:20

you

3:21

the function i called it my underscore

3:24

addition

3:26

arguments are a and b

3:29

we can also

3:31

write them as

3:33

declare them as double

3:36

double precision

3:39

b as double

3:42

okay

3:44

wait um

3:49

and also we can

3:52

declare the return on function

3:54

double yes

3:57

i usually don’t declare that if you

3:59

don’t declare them the default is

4:01

variant a variant can be a double it can

4:04

be an integer can be a long

4:06

integer can be even a matrix

4:09

but

4:10

see

4:11

if you want to use a standard

4:14

vba practices it’s better to declare

4:16

them

4:18

personally i don’t declare them so

4:21

i like to keep it very simple

4:23

even though maybe it’s not so

4:26

correct and it’s all formal

4:29

so now i take this name here

4:32

my edition this custom name control c

4:37

go in the spreadsheet here

4:38

here are just two numbers a and b i gave

4:41

these numbers here 1.1 3.5

4:44

the sum should be 4.6 and here i say

4:47

equal control v

4:49

so the name of the function you see it

4:51

already recognize it

4:53

recognize it as a function parenthesis

4:57

a you can say a is this guy

5:00

so b7

5:02

comma

5:03

and

5:04

b is w7

5:09

and then it gives the

5:11

result the way we expect it

5:15

it adds

5:16

a and b

5:18

in order to

5:20

replace

5:22

these calculations over three columns

5:25

with only two functions we need to

5:27

declare this function the following

5:30

first we need to declare function for

5:32

the x

5:34

value of the

5:36

let me see the rotated house right here

5:40

so let’s call it

5:43

uh the series of operations of function

5:48

first what do we have we have scaling sc

5:51

scaling

5:52

underscore what else we have

5:54

we have translation

5:56

so tr translation and then what else we

5:59

have rotation rot

6:02

and then one is this one is for x

6:05

because we have to fill two columns we

6:07

need two functions

6:09

x

6:10

so input is x

6:13

y

6:15

what else scale sc

6:18

what else um

6:22

dx delta x

6:25

so the horizontal offset or translation

6:29

d y so

6:31

the vertical translation so

6:33

the vertical offset

6:36

and then rot

6:37

the angle of rotation

6:40

in degrees

6:42

inside let’s divide this in three

6:45

different sections

6:46

one apostrophe so we follow with a

6:50

comment

6:51

one is for scaling

6:54

scale

6:55

okay

6:57

then another comment let’s call it

7:00

offset or translation

7:03

translate translate

7:06

and the

7:07

the last one is rotate

7:12

i have the formulas for rotation right

7:14

here okay the new x the rotated x is a

7:19

combination between

7:21

the previous non-rotated coordinate

7:23

coordinates x and y

7:26

and

7:27

the angle theta

7:29

but this angle has to be in radians not

7:31

in degrees i will show how to convert

7:34

them okay

7:36

so we go back and say scale

7:39

x the new x is equal to the scaling

7:41

factor sc

7:43

times

7:44

x the old x okay so the new x is equal

7:48

to the over x

7:49

is equal to the old x times s c s c is

7:53

the scaling factor

7:55

then copy this

7:59

y

8:01

the new y is equal to

8:03

the scaling factor times the old y okay

8:08

now let’s uh do the translation

8:11

so the new x is equal to the old x x

8:15

plus

8:16

dx

8:19

and the new y

8:22

is equal to the old y

8:24

plus d y

8:28

for rotate we use

8:31

this first line in this group of

8:33

formulas so the x prime which is the

8:36

rotated x

8:38

is equal to x the old x times cos of

8:42

theta plus the old y

8:45

times sine of theta

8:47

so

8:48

in this case for this particular column

8:51

right here we only use the first formula

8:55

and x prime is actually the return of

8:58

the function so we copy this name

9:01

control c

9:03

is equal to control v is equal to

9:06

the old x

9:08

which is the x we calculated here okay

9:11

here is a typo

9:14

this is equal okay

9:16

so the old x times

9:19

look here cos of theta

9:22

times

9:23

cos of

9:25

rot

9:27

because this is the angle right here

9:29

right here

9:31

the argument for rotation

9:34

plus look here at the formula

9:38

plus y

9:39

times

9:40

sine of

9:42

rot

9:44

okay so oops

9:47

something

9:50

and

9:51

i tell you we have a problem here okay

9:53

because

9:56

we declare we we use degrees here for

9:59

rotation however the functions the

10:01

trigonometric function and even the sine

10:04

cosine in the spreadsheet except only

10:08

radians okay so look what we did here

10:11

click here you see a radius of that

10:14

rotation angle okay

10:17

so there’s a spreadsheet sheet function

10:19

called radiance

10:22

so this is a built-in function

10:25

okay so let’s test it this is equal to

10:28

rotation

10:31

of

10:32

this guy okay

10:35

oh

10:36

no i’m sorry not rotation what am i

10:38

doing radius

10:41

okay

10:43

so if this 360 should be zero zero if is

10:47

180 how many radians are in 180 degrees

10:51

is pi 3.14 check this out i’m going to

10:55

180 from here

11:00

180 you see 3.14 so the function works

11:04

so let’s do this radians okay

11:08

copy this ctrl c

11:10

and i’m making a mistake here on purpose

11:12

okay to show you something

11:15

we need to escape here

11:18

so go and type radiance i mean

11:22

radians of rot

11:26

and we will get an error okay check this

11:28

out

11:30

i’m telling you it’s not the function is

11:32

not gonna work but uh let’s just use it

11:34

like this and i will demonstrate ctrl c

11:39

true

11:40

v okay

11:44

and

11:45

then let’s apply it okay so

11:48

copy here ctrl c

11:51

how do we use the function say equal

11:54

control v the name of the function you

11:56

see it already recognized it excel

11:58

recognized it

12:00

parenthesis what argument we have for

12:03

this function what arguments x y

12:06

first x y

12:08

so x we take it from here

12:12

and we need to i would like to copy this

12:14

formula to the right so i would like to

12:17

absolute reference the column so in

12:19

front of b

12:21

we put a dollar sign okay

12:24

then what comes in

12:26

the following the next

12:28

argument

12:30

is y so x y then s c scaling okay so

12:36

y is here and we put the dollar sign in

12:39

front of

12:42

the column okay

12:43

we can use f4 for that but in this case

12:46

i just typed it

12:48

what else sc

12:50

scale

12:51

this guy

12:52

hit f4 once

12:54

and we have absolute reference both the

12:58

column and the row

12:59

so no matter where we copy this formula

13:03

up down

13:04

right left

13:05

the scale will stay the same okay will

13:08

the get the scale scale

13:10

the scale uh argument will be picked up

13:13

from

13:14

b three

13:15

what’s next is delta x

13:18

okay see

13:20

delta x delta y

13:23

hit an f4 here

13:25

so we absolute reference both row and

13:29

column here

13:30

so whenever we copy this down or right

13:33

or wherever we copy it

13:35

this reference will stay fixed anchored

13:38

there

13:40

then we put

13:42

translate

13:44

the translate

13:45

term

13:47

f4

13:48

then comma the last one

13:51

is

13:52

the rotation okay

13:55

so rotation is from here and f4

13:59

close this

14:00

hit return okay

14:02

now it pointed me that i have an error

14:04

here there is no function called radians

14:08

in vba there is one in

14:10

worksheet okay the worksheet function

14:13

radians

14:14

but there’s nothing in vba so we need to

14:16

do this

14:19

application

14:20

dot radians

14:22

this way if we use the word application

14:25

with a dot

14:26

in front of the function it takes the

14:29

function

14:30

functionality of the

14:32

spread spreadsheet function radians

14:35

so let’s uh copy this okay

14:39

ctrl c

14:41

and paste it here

14:46

stop remove the error

14:48

so now it should work

14:50

let’s check this out

14:53

you see

14:56

5.379 whatever

14:59

5.379

15:00

if we copy this down

15:03

we can see

15:06

this data is identical to these

15:09

the numbers i mean not the data these

15:11

are formulas

15:13

it’s not data and these cells it’s only

15:15

formulas but i say the results

15:17

are the same which means

15:19

this function

15:21

right here

15:23

produ uh has the same effect produces

15:25

the same result as

15:28

using multiple formulas across this row

15:33

let’s copy this

15:36

ctrl c and define another function ctrl

15:39

v instead of x we put here y everything

15:43

else is the same

15:45

also we need to use you see here the

15:47

second row minus x sine plus

15:51

y

15:52

cos of theta

15:55

so

15:56

we replace in the name

15:58

the the letter x with y

16:02

and here we need a minus

16:04

look here minus

16:07

x times sinus

16:11

plus y times cos

16:16

okay

16:18

and now we can copy this formula right

16:21

here

16:22

and instead of

16:24

this function we replace it with

16:26

y

16:27

the second function

16:29

hit return okay there’s a problem

16:35

we need to replace this with y

16:38

so this name right here

16:41

is the return function right here has to

16:43

be the same

16:44

otherwise we get that error

16:46

let’s see now

16:49

[Music]

16:52

now we get the same thing okay minus 11

16:55

minus 11.

16:56

okay

16:58

you see minus 8.8.115

17:02

minus 8.115

17:05

we can also copy this down

17:07

we can pick up the

17:10

data for this last house from here

17:13

and you see it’s replacing it perfectly

17:17

we delete the artifacts right here the

17:20

unused

17:23

rows

17:24

you see the artifacts disappear

17:30

you change the color under us but

17:33

it’s okay

17:35

so now you can rotate

17:39

with a simple formula relatively simple

17:42

formula custom formula so use a defined

17:45

formula you can replace a lot of cells

17:49

a lot of cell formulas

17:51

so this being said

17:54

the file will be

17:55

available for download in the

17:57

description and

17:58

next i mean next time we’re gonna see in

18:00

the next tutorial we’re gonna

18:03

go from 2d to 3d three-dimensional

18:06

we’re going to generate the pyramid and

18:08

then a cube

18:09

and i’ll show you how to

18:12

rotate translate and turn it into a 2d

18:15

representation of the 3d space

18:19

so this being said i will

18:21

see you in the next tutorial

18:23

and thanks for watching

muchas gracias, por sus aportes a excel, es el Ășnico website donde se encuentra trabajos en excel muy complejos. saludos desde chile

Mucha gracias, caballero! Suerte!

We miss the 3D posting. Thanks for your amazing work