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

 


 

Why You Should Create a Custom Excel Function

And, below I have listed some of the reasons or situations in which you need to go with a custom function.

1. When there is no Function for this

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 others. So a custom function can a solution to this problem because once you create a UDF you don’t need to write that complex formula again and again.

a user defined function can help you avoid long calculations

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 as you can simply insert it as a function.

How to Create Your First User Defined Function in Excel

OK so look. I have split the entire process into three steps:

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

But let me give you can:

You need to create a function that can return the name of the day from a date value. Well, we have a function that returns the day number for the week but not the name. You got it what I’m saying? Yes?

So let’s follow the below steps to create your first user-defined function:

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 you have used it as a function procedure.

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 uses the value which you have referred to.
  • You got the result in the cell.

But you need to understand how this function works from inside. So I have split the entire process into three different parts where you can see how the code which you have written for the function actually works.

working-user-defined-function

As you have specified “InputDate” as the argument for the function and when you enter the function in the cell and specify a date, VBA takes that date value and supply it to the text function which you have used in the code.

And in the example which I have mentioned above, the date you have in the cell A1 is 01-Jan-2019.

After that, the TEXT function converts that date into a day using the format code “dddddd” which you have already mentioned in the function code. And that day which is returns by the TEXT function is assigned to the “myDayName”.

So if the result of the TEXT function is Tuesday that value will be assigned to the “myDayName”.

And here the working of the function comes to an end. “myDayName” is the name of the function so any value is which is assigned to “myDayName” will be the result value and the function which you have inserted in the worksheet will return it in the cell.

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