User Defined Function in VBA

What is Excel VBA Defined Function (UDF)?

In Excel VBA, a User Defined Function (UDF) is a customized function created by the user to perform calculations or tasks that cannot be accomplished using Excel’s built-in functions alone. Excel’s built-in functions cover many common calculations, such as summing numbers, finding averages, and performing other basic mathematical operations. However, there are situations where these built-in functions may provide a different solution than what is provided. It is where UDFs come into play.

For example, let’s consider a scenario where you need to calculate the shipping cost based on specific criteria not covered by Excel’s built-in functions. By creating a User Defined Function in VBA Macro, you can define a formula that incorporates the necessary calculations, such as considering the weight, distance, and shipping method. See below for the code.

This UDF can be used in any worksheet cell like any other Excel function.

In the provided example, we have created a UDF in Excel VBA to calculate the shipping cost. This UDF considers the rate per kilogram (KG), rate per kilometer (KM), and the type of shipping chosen and provides us with the accurate shipping cost.

How to Create User-Defined Functions?

Examples

Let’s look at some examples to understand better the concept and usage of user-defined functions in VBA:

Example #1 – Create a Simple UDF Sum Function

In this example, we will create a simple user-defined function in VBA that will add any two values and provide the result in the cell we enter the function.

• Step 1: Open the VBE, press the Alt + F11 keys in Excel, click on the Developer tab in the ribbon, and then click on the Visual Basic button.

• Step 2: In the VBE, click the Insert menu and select Module to create a new module.

• Step 3: In the Module, define the function using the keyword “Function” followed by the function name. Name this example “SumFunction” and specify the function’s arguments inside parentheses. In this case, we’ll use two arguments: “num1” and “num2” of type Double.

The ‘As Double’ part at the end of the line indicates that the UDF will return a value of type Double. In other words, it will calculate the sum of num1 and num2 and provide the result as a Double data type.

Note: “Double” refers to a numeric data type used to represent decimal numbers with higher precision than the basic “Integer” data type. It is a floating-point data type that can store positive and negative numbers.

• Step 4: Write the code that performs the desired calculation. In this example, the code adds the two numbers:

• Step 5: Now, save the module and close the VBE.

Once you have created the User Defined Function in VBA, i.e., “SumFunction,” you can easily utilize it within your Excel worksheet. When you start typing “=SumFunction” in a cell, Excel automatically displays a highlighted suggestion for the UDF.

You can now use the UDF “SumFunction” created in your Excel worksheet. For example, in a cell, you can enter =SumFunction(5, 10), which will return the sum of 15.

Here is the complete code:

Function SumFunction(num1 As Double, num2 As Double) As Double
SumFunction = num1 + num2
End Function

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Example #2 – Create a Function to Test Logical Values

In this example, we will create a User Defined Function in VBA that tests logical values. If the selected cell has a numeric value, the UDF will return the message “The value is True,” if the cell is empty or has zero, the UDF will provide the output “The value is False.”

Step 1: In the new module, first let’s start by declaring a function named “TestLogicalValue” that takes in one argument called “value” of type Boolean. It means the function expects a logical value (True or False) as input and will return a result as a string data type.

Note: A Boolean value is a data type that represents two possible states: True or False.

• Step 2: Using an If statement, let’s provide a condition that checks if the input value is equal to True. If the condition is true, the logical value passed to the function is True. If the condition is true, the code assigns the “Value is TRUE” string to the function “TestLogicalValue.”

This means that when the function is called with a logical value of True, it will return the string “Value is TRUE”.

• Step 3: If the condition in Step 2 is false, meaning the logical value is not True, the code proceeds to the Else block. In the Else blocks, the code assigns “Value is FALSE” to the function “TestLogicalValue.”

It indicates that when the function is called with a logical value that is not True (i.e., it is False), it will return the string “Value is FALSE.”

• Step 4: Now save the Function and close the VBE.

Enter the formula =TestLogicalValue(A1) in any cell, assuming the logical value you want to test is in cell A1.

Once you press enter, you will see that the cell will display either “Value is TRUE” or “Value is FALSE” based on the logical value in cell A1.

Since the value in cell A1 is 500, the output is “Value is TRUE” in cell A3, where we have entered our formula.

Here is the full code:

Function TestLogicalValue(value As Boolean) As String
If value = True Then
TestLogicalValue = “Value is TRUE”
Else
TestLogicalValue = “Value is FALSE”
End If
End Function

Example #3

In this example, we’ll create a function that calculates the area of a rectangle.

• Step 1: In the new Module, declares the UDF named “CalculateRectangleArea” that takes in two arguments: “length” and “width,” both of type Double. Furthermore, it specifies that the function will return a value of type Double, representing the area of the rectangle.

• Step 2: Now, declare a variable named “area” of type Double. It will be used to store the calculated area of the rectangle.

• Step 3: Now, let’s assign a variable that calculates the area of the rectangle by multiplying the length and width arguments.

This line assigns the value of the “area” variable to the UDF’s name, “CalculateRectangleArea.” Then, it sets the function’s return value to the calculated area.

• Step 4: Save the module and close the VBA.

In any cell, enter the formula =CalculateRectangleArea(A1, B1), assuming the length of the rectangle is in cell A1, and the width is in cell B1.

• Step 5: Once you press enter to execute the formula, the cell will display the calculated area of the rectangle based on the values in cells A1 and B1.

Here is the full code:

Function CalculateRectangleArea(length As Double, width As Double) As Double
Dim area As Double
area = length * width
CalculateRectangleArea = area
End Function

Important Things to Note

• When creating a User Defined Function in VBA Macro, choosing a unique and descriptive name for your function is important. Ensure that the name accurately represents the purpose of the function.
• Specify the arguments for your User-Defined Functions in VBA and define their data types within the parentheses. Ensure that the data types match the expected inputs for your function.
• Implement proper error handling within your UDFs to handle potential errors gracefully. Use error handling techniques like “On Error Resume Next” or “On Error GoTo” to handle specific errors or display meaningful error messages.
• Keep in mind that User Defined Function in VBA may have specific limitations. They may not support certain functionalities in Excel’s built-in functions, such as array formulas or dynamic ranges. Additionally, UDFs created in newer versions of Excel may not be compatible with older versions.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the User Defined Function in VBA, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to User Defined Function in VBA. Here we learn how to create & user-defined functions (UDF) in Excel VBA, examples & downloadable template. You can learn more from the following articles –

• VBA CDEC
• VBA ComboBox
• VBA Active Cell