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

What Is a VBA User Defined Function?

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

Dissection of a VBA User Defined Function

By dissecting 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. Let’s use the VLOOKUP function and dissect it.

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. 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, we need to enter the mandatory arguments. You need to provide the value of these arguments for the function to work. 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. You can always specify the optional arguments to get a custom output. The optional arguments are written inside a third bracket. [range_lookup] is the optional argument.

Examples of How to Use VBA User Defined Function

Example 1 – User Defined Function with No Arguments

Let’s create a user defined function with no arguments.

In the following dataset, we have different Date Formats. We will create a user defined function in VBA to return the current date.

Step 1 – Creating Module

• Go to the Developer tab from Ribbon.
• Select the Visual Basic option.

The Microsoft Visual Basic window will open.

• Go to the Insert tab in the Microsoft Visual Basic window.
• Choose the Module option from the drop-down.

A blank Module will open.

Step 2 – Writing VBA Code

• Enter the following code in the Module.

Function present_day() As Date

   present_day = Date

End Function

• Click on the Save icon as marked in the following picture.

Step 3 – Using Function in Worksheet 

• Press ALT + F11 to close the Microsoft Visual Basic window and open the worksheet.
• Enter the following formula in cell C5.

• Press ENTER.

The current Date will be displayed.

• Follow the same steps for the remaining cells.

Example 2 – User Defined Function with Mandatory Arguments Only

Mandatory arguments can be provided either by selecting the cells or by selecting an array.

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 units. We will create a user defined function in VBA that will convert the data in Kilometers to Miles.

Steps:

• Follow the steps mentioned in Step 1 of Example 1 to create a blank Module.
• Enter the following code in the Module.

Function km_to_mile(kilometers As Double) As Double

   km_to_mile = kilometers * 0.62137

End Function

• Click on Save.

• Enter the following formula in cell C5.

Cell B5 refers to the cell of the column Kilometers.

• Press ENTER.

The kilometers data will be converted into the Miles unit.

• Use the AutoFill feature for the remaining cells.

Read More: How to Use VBA Input Function in Excel 

2.2 Giving Input as Array in Mandatory Arguments

Using following sample dataset, we’ll create a user defined function in VBA that will calculate the Sum of Even Numbers in a selected range.

Steps:

• Create a blank Module.
• Enter the following code in the Module.

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

• Click on Save.

• Enter the following formula in the merged cell.

The range B5:B12 indicates the range of the Numbers.

• Press ENTER.

It will display the Sum of Even Numbers of the selected range.

Example 3 – User Defined Function with Multiple Arguments

In the sample dataset below, we have 2 sets of Dates. We will create a user defined function that will compute the Day Difference between the 2 Dates.

Steps:

• Create a blank Module.
• Enter the following code in the Module.

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

   day_difference = date_2 - date_1

End Function

• Click on Save.

• Enter the following formula in cell D5.

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

• Press ENTER.

You will get the Day Difference between the 2 Dates.

• Use the AutoFill option to get the remaining output.

Example 4 – User Defined Function with Optional Arguments

Using the sample dataset below, we will create a user defined function to convert the Texts into our Desired Format.

Steps:

• Create a blank Module.
• Enter the code given below in the Module.

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

• Click on Save.

• Enter the formula given below in cell C5.

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

• Press ENTER.

You will get the text in the Desired Format.

• Enter the following formula in cell C6.

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

• Press ENTER.

The text output will be same as the original.

• Insert the following formula in cell C7.

We haven’t used any optional arguments here. Excel will assume the optional argument as FALSE.

• Press ENTER.

You will get the following output.

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

Read More: How to Make VBA Function with Arguments in Excel

Benefits of Applying VBA User Defined Functions

• Creating custom functions to meet your needs is one of the main benefits of using user defined functions.
• Another benefit is the ability to substitute complex formulas.
• If you want to add dynamic features into your code, user-defined functions are a good alternative to subroutines. VBA code itself is not inherently dynamic, and needs to be run again every time you change input. However, with user-defined functions, the output will be updated automatically when you change the input.

Shortcomings of VBA User Defined Function

• You can’t use a user defined function to alter the value of another cell.
• User defined functions can’t add, format, or remove cells from your worksheet.
• User defined functions can’t change Excel’s environment settings.
• User defined functions are considerably slower than built-in functions, especially with large datasets.

Download Practice Workbook

Related Articles

• Excel VBA Function to Return Multiple Values
• How to Execute VBA Function Procedure in Excel
• How to Return a Value in VBA Function
• VBA Sub Vs Function in Excel
• How to Create and Use ColorFunction in Excel
• Difference Between Subroutine and Function in Excel VBA
• How to Create Custom Function in Excel VBA