Up next in 10
How to Create Custom Functions in Excel Using VBA #UDF
203 views
Mar 19, 2025
Learn how to create a custom function (or user defined functions) in Excel using VBA that works just like built-in Excel functions.
View Video Transcript
0:00
hi in Excel buil-in functions like sum
0:03
or average are great but sometimes we
0:05
need something more specific that's
0:07
where custom functions come in these
0:09
custom functions work just like built-in
0:11
functions allowing us to perform
0:13
calculations tailor to our needs in this
0:16
tutorial we'll create two functions
0:18
using VBA let's open the VBA editor by
0:20
going to the developer tab or by
0:22
pressing alt
0:26
f11 if the developer tab is not
0:28
available in the ribbon right click on
0:30
the ribbon choose customize the ribbon
0:32
check the developer option and click
0:39
okay in the developer tab click Visual
0:42
Basic as shown on the screen this opens
0:45
the VBA editor now insert a new code
0:48
module by going to the insert menu and
0:50
selecting module a new module named
0:52
module one appears in the project
0:54
Explorer let's write our first custom
0:56
function named rectangle area in the
0:59
module 1 code editor a function must
1:01
begin with a function keyword followed
1:03
by the function name and any required
1:05
arguments or inputs the function keyword
1:08
tells Excel that you're defining a
1:10
reusable piece of code that performs a
1:12
calculation and returns a result this
1:14
function takes two inputs length and
1:17
width both declared as double in VBA
1:20
double is a data type that stores
1:22
decimal numbers allowing for more
1:24
precise calculations by specifying as
1:26
double at the end of the function we
1:28
ensure that our function can return a
1:30
decimal value instead of just an integer
1:32
if we use the integer data type any
1:34
decimal result would be rounded to the
1:36
nearest whole number potentially leading
1:38
to incorrect calculations you may have
1:40
noticed that we assigned the calculation
1:42
result to the function name itself in
1:44
VBA when we assign a value to the
1:46
function name inside the function it
1:49
means this value will be returned as the
1:51
result of the function this allows us to
1:53
use the function inside a worksheet just
1:55
like any built-in function now that
1:57
we've written the function let's see it
1:59
an action in side an Excel worksheet
2:01
switch back to Excel and enter the
2:03
formula as shown on the screen this
2:05
calculates the area of a rectangle with
2:07
a length of 50 and a width of 25
2:10
returning 1250 let's try another example
2:13
here the length is 100 and the width is
2:16
26 so the function returns 2600 now
2:20
let's see how it handles decimal values
2:22
since the length is 9.7 and the width is
2:26
2.5 the function accurately returns
2:28
24.25
2:30
demonstrating its ability to handle
2:32
precise decimal calculations as you can
2:34
see our custom function works just like
2:36
any built-in Excel function allowing us
2:39
to enter inputs and instantly get
2:47
results now let's go back to VBA Editor
2:49
to create another function named get
2:51
initials this function extracts the
2:53
initials from a full name it takes a
2:55
name as input which is type of string
2:58
splits it into words and picks the first
3:00
letter of each word these letters are
3:02
then converted to uppercase and combined
3:05
to form the initials for example joho
3:08
will return JD here's how it works the
3:11
name is split into words based on Spaces
3:13
a loop goes through each word and
3:15
extracts its first letter each extracted
3:18
letter is converted to uppercase and
3:20
added to the result the final output is
3:22
a string of initials
3:30
now return to Excel and see the function
3:33
and action on the screen for the name
3:35
brain Bell the formula returns BB since
3:38
both initials are B four John Cena it
3:41
returns J C as J is the first letter of
3:44
John and C is the first letter of Cena
3:47
similarly for Bruno Mars It returns BM
3:50
automatically converting the lowercase
3:52
initials to uppercase you're not limited
3:54
to entering values directly into the
3:56
function you can also use cell
3:58
references if the data already exists in
4:00
your Excel sheet simply enter the cell
4:02
address in the formula and press enter
4:05
Excel will automatically read the value
4:07
from the reference cell and return the
4:08
result in the formula cell as shown on
4:11
the screen as you can see creating a
4:13
custom function in VBA is not difficult
4:16
with a little effort you can build
4:17
functions that simplify your work
4:19
however keep in mind that VBA functions
4:22
are only available in the current
4:23
workbook if you want your functions to
4:25
be available in all workbooks you need
4:27
to save the file as an Excel addin if
4:30
you found this tutorial helpful please
4:31
like share and subscribe to my channel
4:34
for more Excel tips and tutorials thanks
4:36
for watching and see you in the next
4:37
video