How to Use VBA User Defined Function (4 Suitable Examples)

While working in Excel VBA, we often need to use user defined function. Users can use these functions as regular functions of Excel. When the inbuilt functions of Excel don’t meet our requirements, in those cases, we need to use user defined functions of Excel VBA. In this article, we will discuss 4 suitable examples of user defined functions in Excel VBA.


Download Practice Workbook


What Is a VBA User Defined Function?

A user defined function is a custom made function by users according to their needs. It is a collection of commands that is coded in VBA and returns us the desired output.


Dissection of a VBA User Defined Function

If we dissect a user defined function in VBA, we can understand the structure of the function. The structure of the VBA user defined function is similar to the in-built functions. For example, let’s use the VLOOKUP function and dissect it. The VLOOKUP function is structured in the following way.

=VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup])

We can divide the syntax of the function into 2 separate sections.

  • The first section of the function syntax holds the name of the function. Here VLOOKUP in the function name.
  • The second section consists of the function arguments. The arguments are written inside the parenthesis. There are 2 types of arguments in a function.
    • Inside the parenthesis, firstly we need to write the mandatory arguments. You must need to provide the value of these arguments for the function to work. In this case, lookup_value, table_array, and column_index_num are the mandatory arguments.
    • There is another type of argument called optional argument. The value of these arguments is not compulsory to provide. Excel will assume a pre-specified value for the optional arguments if it is not provided. However, you can always specify the optional arguments to get a tailored output. The optional arguments are written inside a third bracket. Here, [range_lookup] is the optional argument.

4 Examples to Create & Use VBA User Defined Function

In this section of the article, we will discuss 4 easy examples to get a grasp of how to use VBA user defined function in Excel.

Not to mention that we have used the Microsoft Excel 365 version for this article; you can use any other version according to your convenience.


1. User Defined Function with No Arguments

In the first example, we will create a user defined function with no arguments. In the following dataset, we have different Formattings of Date. We will create a user defined function in VBA to return the date of today.

Let’s use the following steps to do this.

vba user defined function

Step 01: Creating Module

  • Firstly, go to the Developer tab from Ribbon.
  • Following that, select the Visual Basic option.

User Defined Function with No Arguments

As a result, the Microsoft Visual Basic window will open as shown in the following image.

  • After that, go to the Insert tab in the Microsoft Visual Basic window.
  • Then, choose the Module option from the drop-down.

Creating Module to vba user defined function

Consequently, a blank Module will open as shown in the image below.

Step 02: Writing VBA Code

  • Firstly, write the following code in the Module.
Function present_day() As Date

   present_day = Date

End Function

Writing VBA Code to use vba user defined function

Code Breakdown

  • Firstly, we have initialized the Function statement.
  • Following that, we specified the name of our function as present_day().
  • Then, we declared the data type of the return value of the function As Date.
  • After that, we used the Date function to assign today’s date in the present_day function.
  • Finally, we ended the function.
  • After writing the code, click on the Save icon as marked in the following picture.

Step 03: Using Function in Worksheet 

  • Firstly, press the keyboard shortcut ALT + F11 to close the Microsoft Visual Basic window and open the worksheet.
  • After that, enter the following formula in cell C5.
=present_day()
  • Now, press ENTER.

Using Function in Worksheet to use vba user defined function

As a result, you will today’s Date as shown in the image below.

  • Next, use the same steps to get the following output on your worksheet.


2. User Defined Function with Mandatory Arguments Only

Now, we will discuss the user defined function with mandatory arguments only. We can provide the mandatory arguments either by selecting the cells or by selecting an array. Both of the variations are discussed in the following sections.


2.1 Providing Input by Selecting Cells in Mandatory Arguments

We select cells to provide input when we need to provide discrete values for the function’s arguments. In the following dataset, we have some data in kilometers unit. We will create a user defined function in VBA that will convert the data in Kilometers to Miles. Let’s follow the steps mentioned below.

User Defined Function with Mandatory Arguments Only

Steps:

Function km_to_mile(kilometers As Double) As Double

   km_to_mile = kilometers * 0.62137

End Function

Providing Input by Selecting Cells in Mandatory Arguments to use vba user defined function

Code Breakdown

  • Firstly, we initialized the function by using the Function statement.
  • After that, we named our function km_to_mile.
  • Next, we introduced the argument of the function and its data type inside the parenthesis by kilometers As Double.
  • Then, we specified the data type of the return value of the function As Double.
  • After that, multiplied the variable named kilometers by  0.62137 and assigned it to km_to_mile.
  • Finally, we ended the function.
  • Now, click on the Save icon as marked in the following image.

  • After that, use the following formula in cell C5.
=km_to_mile(B5)

Here, cell B5 refers to the cell of the column Kilometres.

  • Now, hit ENTER.

Consequently, you will see that the data is converted into the Miles unit.

  • Afterward, use the AutoFill feature of Excel to obtain the remaining outputs.


2.2 Giving Input as Array in Mandatory Arguments

We can also give input to the mandatory arguments as arrays. In the following dataset, we have some Numbers. We will create a user defined function in VBA that will calculate the Sum of Even Numbers in a selected range.

Giving Input as Array in Mandatory Arguments to use vba user defined function

Steps:

Function even_number_sum(input_range As Range)

    Dim input_cell As Range

    For Each input_cell In input_range

        If IsNumeric(input_cell.Value) Then

            If input_cell.Value Mod 2 = 0 Then

                Sum = Sum + input_cell.Value

            End If

        End If

    Next input_cell

    even_number_sum = Sum

End Function

Code Breakdown

  • Firstly, we started the Function statement.
  • Next, we named our function even_number_sum.
  • After that, we introduced the argument of the function and its data type by input_range As Range.
  • Subsequently, we declared a variable input_cell As Range.
  • Then, we initialized a For Next loop and we will run this loop for each input_cell in the input_range.
  • Following that, we used an If statement to check whether the cell value is a number or not.
  • If the cell value is a number then we will check whether the remainder of the number after dividing by 2 is 0 or not by introducing another If statement.
  • If the remainder is 0 that means the number is an even number. So, we added the number by using Sum + input_cell.Value argument and assigned it to the Sum variable.
  • After that, we closed the 2nd If statement.
  • Next, we ended the 1st If statement.
  • Then, we assigned the value of the Sum to even_number_sum.
  • Finally, we ended the function.
  • Now, click on the Save icon.

  • Following that, use the following formula in the merged cell.
=even_number_sum(B5:B12)

Here, the range B5:B12 indicates the range of the Numbers.

  • Next, hit ENTER.

As a result, you will have the Sum of Even Numbers of the selected range as shown in the following picture.

final output of method 2.2 to use vba user defined function


3. User Defined Function with Multiple Arguments

In this section of the article, we will learn to utilize the user defined function in Excel VBA with multiple arguments. In the following dataset, we have 2 sets of Dates. We will create a user defined function that will compute the Day Difference between the 2 Dates. Let’s follow the steps discussed below.

User Defined Function with Multiple Arguments to use vba user defined function

Steps:

Function day_difference(date_1 As Date, date_2 As Date) As Integer

   day_difference = date_2 - date_1

End Function

Code Breakdown

  • Firstly, we initialized a function by using the Function statement.
  • Then, we named the function day_difference.
  • Now, we introduced 2 arguments of the functions and their data types as date_1 As Date, and date_2 As Date.
  • After that, we declared the data type of the return value of the function As Integer.
  • Next, we subtracted date_1 from date_2 and assigned the value in day_difference.
  • Lastly, we ended the function.
  • After writing the code, click on the Save icon as marked in the following image.

  • Following that, enter the following formula in cell D5.
=day_difference(B5,C5)

Here cell B5 refers to the 1st Date and cell C5 indicates the 2nd Date.

  • Next, press ENTER.

Consequently, you will see the Day Difference between the 2 Dates as follows.

  • Now, by using the AutoFill option of Excel you can get the rest of the outputs.

final output of method 3 to use vba user defined function


4. User Defined Function with Optional Arguments

In this example, we will discuss how we can create a user defined function along with the optional arguments. In the following dataset, we have some Texts. We will create a user defined function to convert the Texts into our Desired Format. Let’s follow the steps mentioned below.

User Defined Function with Optional Arguments

Steps:

Function fetch_text(cell_reference As Range, Optional text_case = False) As String

Dim length_of_string As Integer

Dim output As String

length_of_string = Len(cell_reference)

For i = 1 To length_of_string

If Not (IsNumeric(Mid(cell_reference, i, 1))) Then output = output & Mid(cell_reference, i, 1)

Next i

If text_case = True Then output = UCase(output)

fetch_text = output

End Function

Code Breakdown

  • At first, we initialized a function by using the Function statement.
  • Following that, we named the function fetch_text.
  • Then, we specified the mandatory argument of the function and its data type by cell_reference As Range.
  • After that, we declared our optional argument as Optional text_case.
  • Next, we assigned the value of the optional argument, if it’s not specified by the user as False.
  • Then, we declared the data type of the return value of the function As String.
  • Following that, we introduced 2 variables named length_of_string As Integer and output As String.
  • Now, we used the Len function to calculate the length of the string and assigned the value to the variable named length_of_string.
  • After that, we initialized a For Next loop with the initial value of i as 1 and a maximum value up to the length_of_string.
  • Then, we used the If statement and the IsNumeric function.
  • Following that, we used the Mid function.
  • Now, we used another If statement to check the optional argument of the function.
  • If it is True then we used the Ucase function to return the text in upper case format and we assigned the value in output.
  • Then, we assigned the value of output in fetch_text.
  • Finally, we ended the function.
  • Following that, click on the Save icon.

  • Next, use the formula given below in cell C5.
=fetch_text(B5,TRUE)

Here, cell B5 represents the cell of the column Texts and the optional argument TRUE means that we want the upper case letters.

  • Subsequently, hit ENTER.

Using formula to use vba user defined function

As a result, you will have the following output in the Desired Format column.

  • Next, enter the following formula in cell C6.
=fetch_text(B6,FALSE)

Here, we have used FALSE as our optional argument. For this reason, the text will stay as it is.

  • Following that, press ENTER.

Consequently, you will see that the text has stayed as it was.

  • After that, insert the following formula in cell C7.
=fetch_text(B7)

Here, we haven’t used any optional arguments. So, according to the code, Excel will assume the optional argument as FALSE.

  • Then, press ENTER.

As a result, you will have the following output on your worksheet.

  • Follow the same steps to get the output for the remaining cell.

final output of method 4 to use vba user defined function


Benefits of Applying VBA User Defined Function

The user defined function in VBA can be quite advantageous. Let’s look at some benefits of using Excel VBA’s user defined function.

  • The ability to create our own functions to meet our needs is one of the main advantages of using user defined functions. Excel is a feature-rich, adaptable program with many built-in functions. However, occasionally even Excel’s extensive built-in functions are insufficient. Not to worry! Excel’s user defined function feature still has you covered. If there isn’t an Excel function that can be used for your purpose, you can write a custom function using the VBA code.
  • The capability to substitute a complicated formula is another benefit of using user defined functions. We frequently come across lengthy and complicated formulas while using Excel which is challenging to comprehend and repeatedly use. We can fix this problem by using VBA code to create a user defined function that will take the place of this complicated formula and return the results we want. Additionally, it is much simpler to use this function repeatedly than to write the complicated formula each time.
  • If you want to include a dynamic feature in your code, user defined functions can be a good substitute for sub routines. VBA codes are not dynamic, as is well known. Every time you change input, the code must be run again. However, you can have a dynamic output if you utilize a user defined function. This implies that updating your output only requires changing the input. No need to run the code each time.

Shortcomings of VBA User Defined Function

Even though utilizing the user defined function is highly advantageous, there are still some drawbacks to be aware of. They are demonstrated below.

  • You can’t use a user defined function to alter the value of another cell.
  • Additionally, you are unable to add, format, or remove cells from your worksheet.
  • Excel’s environment settings cannot be modified through its use.
  • User defined functions are considerably slower than built-in functions. Utilize user defined functions with caution when working with large datasets.

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

practice section to use vba user defined function


Conclusion

That’s all about today’s session. I strongly believe that this article was able to guide you to use VBA user defined function. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website, ExcelDemy. Happy learning!

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

1 Comment

Leave a reply

ExcelDemy
Logo