User Defined Function in VBA

Excel VBA User Defined Function (UDF)

Microsoft serves us with many built-in functions to speed up the work in excel. However using VBA coding we can create our own functions and those functions are technically called “User-Defined Functions” (UDF). They are called “custom functions” in excel VBA.

Any formula which can be accessed from the worksheet with a piece of code is called as UDF. In simple terms, any formula which is not built-in but available in Excel is called User Defined Functions.

How to Create User Defined Functions using VBA?

Even though UDF is part of our module they are not part of our regular Sub Procedure. This is called a Function Procedure in VBA. Like how we start the macro coding with word SUB similarly we need to start this by using the word “Function”. Sub procedure has start and End, similarly Function Procedure to has Start and End.

Example #1 – Create a Simple UDF Sum Function

We will create our own SUM function by adding up two numbers.

To start the coding start the word “Function” in one of the modules.

Like how we name the macro similarly we need to give a name to our Function as well. This name is used as a formula name.

Unlike subroutine, we cannot simply hit enter to create a procedure but we need to mention arguments here.

For example, look at the below syntax of the worksheet function SUM.

Number 1, Number 2 are arguments of the function SUM.

Similarly, we need to mention our arguments.

Here I have declared the arguments as “x as Integer” and “y as Integer”. Since we add numerical value we need to assign the data type as numerical data type only.

After declaring the arguments I have assigned the return type of the Integer too. Because result given by the Function “OurSum” is also a numerical value.

Now inside the function, we need to mention the formula we are going to use. Here we need to use the function to start with.

We mentioned the formula name “OurSum” should add x and y.

Ok, save the code and go to the worksheet.

I have entered a few numbers here. Open equal sign and start typing OurSum. You can see the formula name appearing here.

Like how we select cells similarly select two cells separately.

After selecting two cells hit enter key to have results.

So it works exactly the same as our regular SUM function.

Ok, now I will change the numbers.

Now again I will apply the function we have created.

Oh!!! We got error values except for the first cell.

You must be thinking about why we got an error.

If you observe the cell A2 and B2 we have values 48170 and 21732. Since we have assigned the data type as Integer it cannot hold any number more than 32767. This is the reason we got the excel error as #NUM!.

Now I will add numbers that are less than 32767.

In the above image, all the numbers are integer numbers i.e. less than 32767.

In the first cell, we got the results. But in the second cell i.e. C2 cell we got error value as #VALUE!.

Even though both the numbers are less than 32767 still we got the error.

This is because we have declared the end result also as Integer.

So when we add 16000 and 17229 we will get the value like 33229 which is more than the integer data type limit of 32767. So the end result is an error value.

Example #2 – Create a Function to Test Logical Values

We will create one more function to test the logical values. We need a function that returns “Good” if the number is >=60 and we need the result as “Bad” if the number is <=50.

Step 1: Start the Function Procedure and give a name to the procedure.

Step 2: Give the argument as TestScore as Integer.

Step 3: Our end result should be either “Good” or “Bad” so the result should be in String.

Step 4: First we need to test whether the score is >=60 or not. Use IF condition to test.

Step 5: If the TestScore is >=60 we need the function to return the result as “Good”. So TestResult = “Good”.

Step 6: If the TestScore is less than 60 then the function result should be “Bad”.

Ok, we are done.

Save the code and go to the worksheet.

I have entered a few numbers here as a test score.

Apply function we have created to arrive results.

Select the cell and hit enter.

This is what UDF is all about.

Recommended Articles

This has been a guide to User Defined Function in VBA. Here we learn how to create a user defined function in VBA along with examples and downloadable excel template. Below are some useful excel articles related to VBA –

• If Else in Excel VBA
• VBA GetObject Function
• Excel VBA INSTRREV
• Chr in VBA
• Variable Declaration in VBA
• Global Variables in VBA
• String Functions in VBA