Up next in 10
User Defined Function - Comprehensive Tutorial - Create Your Own Excel Functions using VBA
48 views
May 9, 2025
In this video, you'll learn how to create your own custom function (user defined function or udf) in Excel using VBA. A User Defined Function (UDF) is a function that you create with arguments you specify, to use as a worksheet function or as part of a macro procedure, when a task is otherwise impossible or too cumbersome to achieve with Excel’s built-in formulas and functions We'll explore: 00:00 Introduction 00:09 Customize Quick Access Toolbar to Add Visual Basic Editor 00:37 Anatomy of UDF - A very basic function 01:28 Declare Arguments 03:08 Declare Optional Arguments 05:00 Skip Optional Arguments 05:45 Control the output of UDF 06:36 VBA Data Types 06:51 Declare data types to prevent errors and improves the performance 09:07 Create static random number function 09:36 Volatile vs. non-volatile function 11:47 How to make your function Volatile
View Video Transcript
0:00
hi in this video I'll show you how to
0:02
create your own function in Excel called
0:04
a userdefined function or UDF to get
0:07
started we need to open Excel's built-in
0:09
VBA editor by default this option isn't
0:12
available on the ribbon to make it
0:14
accessible click the customize quick
0:16
access toolbar dropdown at the top of
0:18
the Excel window and choose more
0:20
commands in the Excel options dialogue
0:22
change the drop-own to all commands then
0:25
scroll down and select visual basic
0:27
editor click add to move it to the
0:30
toolbar then click okay now the Visual
0:33
Basic Editor icon will appear in the
0:34
title bar ready for quick access press
0:37
Alt F11 or click the Visual Basic Editor
0:41
icon this opens the VBA editor go to the
0:44
insert menu and choose module a new VBA
0:47
module will be inserted and its code
0:48
window will appear let's write our first
0:51
custom function it's a very simple one
0:53
it just returns the text high
0:56
every function starts with a keyword
0:58
function followed by a name you choose
1:00
it ends with end function just like
1:02
built-in Excel functions a UDF returns a
1:05
result inside the function we return the
1:08
output by assigning it to the functions
1:10
name so this line tells Excel to return
1:12
hi when you type into a cell let's try
1:15
it out go back to Excel and type
1:18
function say hi into a cell when you
1:20
press enter the cell displays hi that's
1:23
the return value from our function
1:27
functions don't just return outputs they
1:29
can also take inputs called arguments
1:32
let's modify our function to accept an
1:34
input we'll define an argument called
1:36
name inside the parentheses of the
1:38
function declaration next we'll use this
1:40
argument to return a personalized
1:42
greeting by combining it with the text
1:44
high in VBA the in symbol is used to
1:47
join or concatenate text strings or
1:50
variables together let's write the
1:53
function and pass it the argument
1:54
brainbell when you press enter the
1:57
function returns hi brainbell you might
1:59
notice that any cell where you
2:01
previously enter the function say hi now
2:04
shows a value error that's because the
2:06
function has been updated to require an
2:08
argument to fix the error just update
2:10
the formula by passing a value for that
2:15
argument functions aren't limited to a
2:18
single argument you can use as many as
2:20
you need just separate each argument
2:22
with a comma let's add a second argument
2:24
called message which we'll use for a
2:26
custom message we'll also concatenate
2:29
this new argument along with the name to
2:31
return a full greeting now you need to
2:34
pass two arguments first the person's
2:36
name and second the greeting message for
2:39
example when you pass John and welcome
2:42
the function returns hi John
2:48
welcome again the previous formulas that
2:51
used just one argument will now display
2:54
a value error because the function has
2:56
been updated to expect two inputs make
2:58
sure to update any older formulas to
3:00
include both
3:06
arguments you can also make arguments
3:08
optional which is perfect for situations
3:10
where an argument isn't always required
3:12
if you don't provide a value for an
3:14
optional argument the function will
3:16
still work and won't return an error in
3:18
VBA you do this using the optional
3:20
keyword in the function definition when
3:22
using optional you also need to specify
3:25
a default value for that argument this
3:27
default value is used when the argument
3:29
is not provided in the formula let's
3:31
make the message argument optional by
3:33
assigning it a default value an empty
3:35
string now if no value is passed for the
3:38
second argument the function will simply
3:40
return a basic greeting now you can call
3:42
the function with just one argument Nick
3:45
and it will return hi Nick if you do
3:47
provide the second argument good morning
3:49
then it will return hi Nick welcome
3:51
optional arguments give your function
3:53
more flexibility by allowing users to
3:56
skip inputs they may not always need
4:14
keep in mind when you declare an
4:16
argument as optional then every argument
4:18
that follows it in the function
4:19
definition must also be optional if you
4:22
don't follow this rule VBA will show a
4:24
compile error let's see how this works
4:26
by adding a third argument called score
4:29
we'll assign it a default value of zero
4:31
if you try to add score without marking
4:33
it as optional VBA throws a compile
4:36
error so once you make one argument
4:38
optional all arguments after that must
4:40
also be optional now the function has
4:43
three arguments you must provide the
4:45
name argument but if you don't provide
4:47
message or score the function uses their
4:49
default values
4:54
when you call a function with an
4:55
optional argument you can choose whether
4:57
to supply a value or let it use the
4:59
default value defined in the function
5:01
it's easy to omit the optional argument
5:03
if it's the last one you just leave it
5:05
out and VBA uses the default but what if
5:08
the optional argument is in the middle
5:10
and you still want to provide a value
5:11
for a later argument for example in our
5:13
function message is the second argument
5:16
and optional while score is the third
5:18
now imagine you want to skip the message
5:20
but still provide a score it's easy just
5:23
use a blank placeholder the two commas
5:25
tell Excel that skip the second argument
5:27
and move on to the
5:33
third notice that the function returns
5:36
default values in the output when you
5:38
don't supply certain optional arguments
5:40
but sometimes you might want the
5:42
function to only process and display
5:44
those optional values if you explicitly
5:46
provide them for example let's say you
5:48
only want to show the score in the
5:49
output if it was actually passed in not
5:52
if it's just using the default of zero
5:54
to do that we can use the if and else
5:57
condition in the function we'll check
5:58
the value of score if the value is zero
6:01
which is the default we assume the user
6:03
didn't provide it so we skip it if the
6:06
value is not zero we include it in the
6:08
result now when you update the formulas
6:11
in Excel the message your score is zero
6:14
no longer appears on the sheet this way
6:16
the function adapts dynamically based on
6:18
whether or not the optional value was
6:21
explicitly passed
6:33
in next let's talk about data types for
6:36
arguments and return values data types
6:39
define the kind of data a variable can
6:41
store like numbers text or dates the
6:44
table on screen shows some common VBA
6:47
data types in our current function we
6:49
haven't declared any data types when you
6:52
don't specify a data type in VBA it
6:54
automatically assigns the variant type
6:56
which can hold any kind of data while
6:58
this offers flexibility it can slow down
7:01
performance and lead to unexpected
7:03
behavior for example if we expect a
7:05
numeric value like 50 for the score
7:07
argument but someone types 50 using
7:10
letters the function will still accept
7:12
it without any error that's because the
7:14
argument is treated as a variant not
7:16
specifically as a
7:24
number to avoid this kind of issue we
7:26
should explicitly declare a data type
7:28
for the score argument so it only
7:30
accepts numeric values let's go back to
7:32
the VBA editor and update the argument
7:34
by writing as integer after the name
7:36
score now the function expects a whole
7:39
number for the score and if you enter a
7:41
text value instead Excel will return a
7:43
value error now try it on a sheet when
7:46
you enter text instead of a number for
7:48
the score argument the function returns
7:50
a value
7:53
error but when you enter a valid number
7:55
the function runs correctly and returns
7:58
the expected
8:04
result it's a good practice to declare
8:06
data types for all arguments let's go
8:09
back to the VBA editor and declare
8:11
string as the data type for both the
8:13
name and message arguments since they
8:15
take text values you can also specify a
8:17
data type for the return value of the
8:19
function to do this place the return
8:21
type after the closing parenthesis for
8:23
example since our function returns text
8:26
we write as string after the parenthesis
8:28
declaring data types not only helps
8:31
prevent errors but also improves the
8:33
performance of your workbook
8:39
to test the return data type let's
8:41
temporarily change it from string to
8:43
integer now we've explicitly declared
8:46
the return type as integer instead of
8:48
string when you test a function on the
8:50
sheet it returns a value error because
8:52
the function is actually returning a
8:54
text value not a number let's change it
8:56
back to string to fix the error this
8:58
example shows how declaring the correct
9:00
data types for arguments and return
9:02
values helps reduce errors and prevent
9:04
unexpected
9:06
behavior another important concept in
9:09
VBA functions is whether they're
9:11
volatile or nonvolatile to understand
9:13
this behavior let's clear the sheet and
9:15
start from scratch we'll create a new
9:17
function called static random this is a
9:20
simple oneliner function that returns
9:22
the result of the R&D function the R&D
9:24
function generates a random number
9:26
between 0ero and one specifically a
9:29
random double value now let's try it out
9:31
in Excel when you enter equals static
9:34
random in a cell it returns a random
9:36
number but here's the key observation
9:38
each time you enter the formula in a new
9:40
cell a new random number is generated
9:43
only for that cell for example enter the
9:46
formula in cell A1 you get a random
9:48
number now enter the same formula in A2
9:51
it gives a new random number but a one
9:53
stays unchanged enter in A3 again a one
9:58
and a2 remain unchanged now let's
10:00
compare this with Excel's built-in rand
10:03
function enter rand in A5 you get a
10:06
random number now enter the same
10:07
function in A6 not only do you get a new
10:10
value in A6 but A5 updates too when you
10:13
enter the formula in A7 both A5 and A6
10:17
update again this behavior is called
10:20
volatility a volatile function is
10:22
recalculated every time any change
10:24
happens in the worksheet so Excel's rand
10:27
is a volatile function whereas our
10:29
static random is nonvolatile its values
10:32
do not change unless the formula is
10:34
re-entered or recalculated directly
10:37
that's why we named it static random
10:39
because it produces random values that
10:41
stay static after generation now let's
10:43
go back to the VBA editor and enhance
10:45
the function we'll assign a data type to
10:47
the return value double and we'll add an
10:50
argument named max then we modify the
10:52
return statement to multiply R&D with
10:55
max now try it on the sheet the argument
10:58
value 10 returns a decimal between zero
11:01
and just under 10 the argument value 100
11:04
returns a decimal number between zero
11:07
and just under 100 similarly 1,000 gives
11:10
you a value from 0 to just under 1,000
11:12
to return whole numbers instead of
11:14
decimals we can modify the function
11:16
using the int function in VBA now what
11:19
does this updated version do it
11:20
generates a static random integer
11:22
between zero and a number just below the
11:25
value you provide for the value 10 it
11:28
returns a whole number between 0 and 9
11:30
for the value 100 between 0 and 99 for
11:34
the value 1,000 between 0 and 999 this
11:38
can be useful for simulations static
11:40
random ids testing or when you want to
11:43
generate random values that don't keep
11:47
changing but what if you want a
11:49
userdefined function that behaves like a
11:51
volatile function updating itself every
11:54
time the sheet recalculates you can do
11:56
that by adding application volatile line
11:59
inside your function now your function
12:01
becomes volatile it will update its
12:03
result every time Excel recalculates
12:06
just like the built-in rand function try
12:08
it again on the sheet now every time you
12:11
type or change a formula all cells using
12:13
static random update automatically this
12:16
is the power of understanding volatile
12:18
versus nonvolitile functions in VBA
#Computer Education
#Other