How to Create a User Defined Function [UDF] in Excel using VBA

But…

Excel gives you the ability to create a custom function using VBA.

Yes, got it right.

User Defined Function, in short UDF.

And there’s one thing which I can say with confidence that every aspiring VBA user wants to learn to create a User Defined Function.

Don’t you?

Say “Yes” in the comment section, if you are one of those people who want to create a custom function.

So today, I going to share everything which you need to know about creating UDF.

Well, it’s an all in all guide.

Note: Here I’ll be using the words User Defined Function, custom function, and UDF interchangeably.

…so stay with me you are going to be a VBA rock star in next couple of minutes.

how to create a user define function in excel using vba

Why You Should Create a Custom Excel Function

But…

…sometimes, in specific situations, you need to do create a UDF.

And, I’m gonna list some of the reasons to show when you need to go with a custom function.

1. When there is no Function for this

It happens sometimes that you need to calculate something and there is no specific function for this.

Function MyWordCount(rng As Range) As Integer

MyWordCount = UBound(Split(rng.Value, " "), 1) + 1

End Function

count word udf to learn why creating custom function important

2. Replace a Complex Formula

If you work with formulas I’m sure you know this thing that complex formulas are hard to read and sometimes harder to understand by other.

So a custom function can a solution to this problem.

a user defined function can help you avoid long calculations

And once you create a UDF you don’t need to write that complex formula again and again.

3. When you don’t want to use SUB Routine

While you can use a VBA code to perform a calculation but VBA codes are not dynamic*.

You need to run that code again if you want to update your calculation.

But if you convert that code into a function then you don’t need to run that code again and again.

How to Create Your First User Defined Function in Excel

Typically, to create a VBA function, you need to follow the below steps:

  • Declaring your Procedure as a Function
  • Defining its Arguments and their Data Type
  • Add code to Calculate the Desired Value

Let me tell you a simple example, but before that, download this sample file to follow along.

OK so look:

You need to create a function which can return the name of the day from a date value.

You got it what I’m saying?

Yes?

So let create this VBA function using below steps.

  • First of all, open your VB editor by using the shortcut key ALT + F11 or go to Developer Tab and click on "Visual Basic" button. 
open vb editor to write code for udf
  • Insert a module: Right-click on the VBA project window and then go to insert and after that click “Module”.
add a module in vba editor to write a udf
  • The next thing is to define a name for the function and here I’m using “myDayName”. So you must write “Function mydayName”.
write a name of the custom vba function

Why Function before the Name?

  • After that, you need to define arguments for the UDF. So insert starting parentheses and write “InputDate As Date”.
the next step to add argument to the user defined function
  • Here InputDate is the Argument’s name and date is its data type. It’s always better to define a data type for the argument.
  • Now, close the parentheses and write “As String”. Here you are defining the data type of the result returns by the function. As you want day name which is a text so its data type should be as “String”.
after that define data type for the UDF
  • In the end, hit ENTER.

At this point, your function’s name, its argument, argument’s data type, and function’s data type is defined and you have something like this in your module:

vba custom function after defining name arguments

Now within the “Function” and “End Function”, you need to define the calculation or you can say working of this UDF.

In Excel, there is a worksheet function called “Text” (learn more about it from here) and we are using the same here.

And for this, you need to write the below code:

myDayName = WorksheetFunction.Text(InputDate, "dddddd")

With this code, you are defining the value which should be returned by the function.

after writing code to use within the vba custom function
  • Now, close your VB editor and go back to the worksheet.
  • In the cell B2, enter “=myDayName(A2)” hit enter and you’ll have the day name.
insert your custom vba function into worksheet

Congratulations! You have just created your first User Defined Function.

This is the moment of real Joy.

Isn't it?

Type “Joy” in the comment section.

How this Function Works and Return Value in a Cell

Your first custom function is here, but the thing is, you need to understand how it works.

If I say in simple words, it’s a VBA code but we are using it as a function.

Let’s divide it into three parts.

  • You enter it in a Cell as Function and Specify the Input Value
  • Excel runs the code behind the function and use the value which you have referred.
  • You got the result in the cell

And now understand it in details;

The moment you enter it in a cell and refer a cell for the value in the argument(s) the code behind runs and use the value you have referred to return the result.

Let’s slow it down:

Here’s the code for the function we have created and you know what is what in this code.

Right?

When you refer a cell in your function this block of code works to calculates the value.

It takes the date from the InputDate argument and returns the day name.

how a user defined function works

As you have used an equal sign to assign the returning value to “myDayname”.

Important: When you write a code for a custom function there one thing you need to take care that the value which that code return must be assigned to the function’s name.

How to Improve a UDF for Good

Well, you know how to create a custom VBA function.

But…

There’s one thing you need to take care that the code you have used to function should be good enough to handle all the possibilities.

If you talk about the function which you just wrote above can return the day name from a date.

But…

What if the value you have specified will not a date?

And if the cell you have referred is blank?

There are can be other possibilities but I’m sure you got my point.

Right?

So let’s try to improve this custom function which could be able to deal with the above problems.

Alright.

First of all, you need to change the data type of the argument and use:

InputDate As Variant

With this, your custom function can take any kind of data type as input.

The first condition is if the cell is blank or not.

And for this, you need to use below code:

If InputDate = "" Then

myDayName = ""

This will make the function return blank if the cell you have referred is blank.

One problem is solved, let’s get into the next one.

Other than a date there are possibilities that you can have a number or a text.

So for this, you also need to create a condition which should check whether the value referred is an actual date or not.

The code would be:

If IsDate(InputDate) = False Then

myDateName = ""

Note: Here I’m are using a blank for both of the conditions, so that if you have large data you could easily filter values where the input value is not valid.

So after adding the above conditions, the code would look like:

Function myDayName(InputDate As Variant) As String

If InputDate = "" Then

myDayName = ""

Else

If IsDate(InputDate) = False Then

myDateName = ""

Else

myDayName = WorksheetFunction.Text(InputDate, "dddddd")

End If

End If

End Function

And here’s how it works now:

I’m sure you can still make some changes in this function but I’m sure you got my point clearly.

Let’s talk in the comment section.

How to use a Custom VBA Function

At this point, you’re pretty much clear about how you can create a VBA function in Excel.

But once you have it, you need to know how you can use it.

And in this part of the post, I’m gonna share with you how and where you can use it.

So let’s jump into it.

1. Simply within a Worksheet

Why we create a custom function?

Simple.

To use it in the worksheet.

You can simply enter a UDF in a worksheet by using equal sign and type name of the function and then specify it's arguments.

insert custom function into a worksheet by typing

You can also enter a user defined function from the function library.

Go to Formula Tab ➜ Insert Function ➜ User Defined.

insert custom function into a worksheet from formula

From this list, you can choose the UDF you want to insert.

2. Using in other Sub Procedures and Functions

You can also use a function within other functions or in a “Sub” procedure.

Below is a VBA code where you have used the function to get day name for the current date.

Sub todayDay()

MsgBox "Today is " & myDayName(Date)

End Sub

3. Accessing Functions from Other Workbook

If you have a UDF in one workbook and you want to use it in another workbook or in all the workbooks, you do it by making an add-in for it.

Follow these simple steps:

  • First of all, to you need to save the file (in which you have the custom function code) as an add-in.
  • For this, go to the File Tab ➜ Save As ➜ “Excel Add-Ins (.xalm).
save file as add in to use all the custom functions in other wworkbooks
  • After that, double-click on the add-in you and install it.
click on add in to add all theuser defined functions into other workbook

That’s it.

Now you can use all of your VBA functions in any of the workbook.

Different Ways to Create a Custom VBA Function [Advanced Level]

At this point, you know about to create a custom function in VBA.

But the thing is when we use In-Built functions, they come with different type of arguments.

So in this section of this guide, you gonna learn how to create a UDF with the different type of arguments.

…let’s move ahead.

1. Without Any Arguments

Yes.

You can create a User Defined Function where you don’t need to enter an argument.

Let’s do it with an example:

Let’s create a custom function which can return the location of the current file.

And here’s the code:

Function myPath() As String

Dim myLocation As String

Dim myName As String

myLocation = ActiveWorkbook.FullName

myName = ActiveWorkbook.Name

If myLocation = myName Then

myPath = "File is not saved yet."

Else

myPath = myLocation

End If

End Function

This function returns the path of the location where the current file is stored and if the workbook is not stored anywhere it will show a message says “File is not saved yet”.

creating  a user defined function without argument

Now, if you pay close attention to the code of this function, you don’t have defined any argument (within the bracket).

You have just defined the data type for the function’s result.

The basic rule of creating a function without argument is a code where you don’t need to input anything.

In simple words, the value you want to have in return from the function should be calculated automatically.

And in this function, you have the same thing.

This code “ActiveWorkbook.FullName” returns the location of the file and this one “ActiveWorkbook.Name” returns the name.

So here you don't need to input anything.

2. With Just One Argument

We have already covered this thing while learning how to create a user-defined function.

But let’s dig a bit deeper and create a different function.

Function giveMeURL(rng As Range) As String

On Error Resume Next

giveMeURL = rng.Hyperlinks(1).Address

End Function

Now in this function, you have just one argument.

simple custom function to extract url from a hyperlink

When you enter this in a cell and then specify the cell where you have a hyperlink and it will return the URL from the hyperlink.

Now in this function, the main work is done by:

rng.Hyperlinks(1).Address

but the rng is what you need to specify.

Say “Easy” in the comment section if you find creating a UDF easy.

3. With Multiple Arguments

So it’s a must for you to learn how you create a custom function with multiple arguments.

Let’s take an example:

You want to remove particular letters from a text string and want to have the rest of the part.

But here we don’t need this.

All we need is a custom function using VBA.

So here’s the function:

Function removeFirstC(rng As String, cnt As Long) As String

removeFirstC = Right(rng, Len(rng) - cnt)

End Function

OK so look:

vba function with multiple arguments

In this function, you two arguments:

  • rng: In this argument, you need to specify the cell from where you want to remove the first character of a text.
  • cnt: And in the argument, you need to specify the count of the characters to remove (If you want to remove more than one character from the text).

When you enter it in a cell it works something like below:

3.1 Creating a User Defined Function with Optional as well as Required Argument

If you think about the function we have just created in the above example where you have two different arguments, well, both of them are required.

And, if you miss any of these you’ll get an error like this.

Now if you think logically, the function we have created is to remove the first character…

…but here you need to specify the count of the characters to remove.

So my point is this argument should be optional and must take one as a default value.

What do you think?

Say “Yes” in the comment section if you agree with me on this.

OK so look

To make an argument optional you just need to add “Optional” before it.

Just like this:

But…

…the important thing is to make your code to work with or without the value for that argument.

So our new code for the same function would be like this:

Now in the code, if you skip specifying the second argument.

4. Using Array as the Argument

There are few In-built functions which can take arguments as an array and you can also make your custom VBA function to do this.

Let’s do with a simple example where you need to create a function where you sum values from a range where you have numbers and text.

Here we go.

Function addNumbers(CellRef As Range)

Dim Cell As Range

For Each Cell In CellRef

If IsNumeric(Cell.Value) = True Then

Result = Result + Cell.Value

End If

Next Cell

addNumbers = Result

End Function

In the above code of the function, we have used an entire range A1:A10 instead of a single value or a cell reference.

a custom vba function to sum number froma alist

By using FOR EACH loop it will check every cell of the range and sum the value if the cell has a number in it.

The scope of a User Defined Function

In simple words, the scope of a function means if it can be called from other procedures or not.

A UDF can have two different types of scopes. 

1. Public

You can make your custom function public so that you can call it in all the worksheets of the workbook.

To make a Function public you just need to use the word “Public”, just like below.

add pubilc before the name of vba custom function

But…

…a function is a Public function by default if you don’t make it private. In all examples we have covered, all are public.

2. Private

When you make a function private you can use it in the procedures of the same module.

add private before the name of vba custom function

Let’s say if you have your UDF in “Module1” you can only use it in procedures you have in “Module1”.

And it won’t appear in the function list of the worksheet (when you use = sign and try to type the name) but you can still use it by typing its name and specifying arguments.

Limitations of User Defined Function [UDF]

UDFs are super useful.

But…

They are limited in some of the situations.

Here are the few things which I want you to note down and remember while creating a custom function in VBA.

  • You can’t change, delete, or format cells and a range by using a custom function.
  • Also can’t move, rename, delete, or add worksheets to a workbook.
  • Make a change to another cell's value.
  • It also can’t make changes to any of the environment options,

Is there any difference between an In-Built Function and a User Defined Function?

I’m glad you asked.

Well, to answer this question I want to share some of the points which I believe are important for you to know.

  • Slower Than In-Built: If you compare the speed of inbuilt functions and VBA function, you’ll find earlier is fast. The reason behind this is that the in-built functions are written using C++ or FORTRAN.
  • Hard to Share Files: We often share files over email and cloud so if you are using any of the custom function you require to share that file in “xlam" format so that other person can also use your custom function.

Conclusion

Creating a User Defined Function is simple.

All you need to do it use “Function” before the name to define it as a function, add arguments, define arguments data type and then define the data type for the return value.

a simple illustration to understand how to create a user defined function

In the end, add code to calculate the value which you want to get in return from the function.

This guide which I have shared with your today is the simplest one to learn how to create a custom function in VBA and I’m sure you have found it useful.

But now, tell me one thing.

UDFs are useful, what do you think?

Please share your views with me in the comment section. I'd love to hear from you, and please, don’t forget to share this post with your friends, I am sure they will appreciate it.

Content Protection by DMCA.com
2018-11-16T06:13:19+00:00

3 Comments

  1. Luis 18 Nov, 18 at 12:56 am - Reply

    How I can put help texts for my own functions, to guide users the arguments type.

  2. Damien 20 Sep, 18 at 6:02 pm - Reply

    Love the guide and the content, great stuff. Just a heads up that the links in the Table of Contents did not work for me. It didnt move to those particular bookmarks on the page. Although I realized all I needed to do was scroll down, just wanted to make you aware. I use Firefox 62.0

    • Puneet 21 Sep, 18 at 3:48 am - Reply

      Hey, Damien. Can you try it again?

Leave A Comment