Excel Animation Tutorial – 2D Wire-frame House #3 (adding UDF – user defined functions)

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