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.
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.
How to Use VBA User Defined Function: 4 Examples
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.
- Firstly, go to the Developer tab from Ribbon.
- Following that, select the Visual Basic option.
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.
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
Code Breakdown
- First, 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.
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 units. 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.
Steps:
- Firstly, use the steps mentioned in Step 01 of Example 01 to create a blank Module.
- Following that, write the following code in the Module.
Function km_to_mile(kilometers As Double) As Double
   km_to_mile = kilometers * 0.62137
End Function
Code Breakdown
- First, 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, multiply the variable named kilometers by 0.62137 and assign 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.
Read More: How to Use VBA Input Function in ExcelÂ
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.
Steps:
- Firstly, use the same steps used in Step 01 of Example 01 to create a blank Module.
- After that, use the code given below 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
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.
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.
Steps:
- Firstly, follow the procedure mentioned in Step 01 of Example 01 to create a blank Module.
- After that, write 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
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.
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.
Steps:
- Firstly, use the steps mentioned in Step 01 of Example 01 to create a blank Module.
- Then, write 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
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 assign 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.
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.
Read More: How to Make VBA Function with Arguments in Excel
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.
Download Practice Workbook
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. Happy learning!
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
