User Defined Functions in VBA – Define, Call, Example

In the previous blog, we implemented the usage of Sub Procedures. Today we are going to learn about User Defined functions in VBA. This is a fascinating topic to learn in VBA.

So let us begin learning.

What are User Defined Functions?

As the name suggests, User Defined Functions are written by us. We can pass parameters to a function. A Function can also return values as its result. Once we write a function code, we can use it in any part of our Excel workbook.

Writing functions to perform repetitive calculations or operations saves us time and effort. We can call a Function to perform the repetitive task.

Use the following workbook to practice User Defined Functions in VBA.

Syntax to Define a Function

The syntax to write a function is as follows:-

Function <function_name> (<param1> as <data_type1>.......) As<Function_return_data_Type>
'function code
End Function

The following points explain the syntax to define a function.

• We use the Function and End Function keywords to depict the beginning and end of the function.
• We need to specify the function name. It cannot contain blank spaces.
• We need to pass information to function in the form of parameters. Please specify the name of the parameter along with its data type in the brackets. It is not mandatory to pass parameters while defining a function.
• Thereafter, we tell the data type of the value returned by the function after As keyword.
• The value returned by the function can be a variable or object. It would be assigned to the <function_name>

Calling a Function in VBA

We can call a function from another sub-procedure or a function. When the function does not require any arguments and does not return a value then we can use the simple method.

Function_Name

Use this syntax when you need to pass arguments to function.

Function_Name(arg1, arg2........argn)

If the function returns a value, then returned result must be assigned while calling the function.

result=Function_Name(arg1,arg2.....argn)

Example – Functions in VBA

We would now implement some practical examples to learn about functions in VBA in Excel. So let us begin.

Example 1 – Simple Example of Function

In this example, we will write a function to print the message “Learning Functions with ExcelUnlocked”. Thereafter, we will use a Sub Procedure to call this function multiple times.

Write the following code in the VBE.

Function Print_Message()
MsgBox "Learning Functions with ExcelUnlocked"
End Function

This is the function definition. We will call this function using another Sub Procedure.

Sub Calling_Function()
Print_Message      'Call to function 1st time
Print_Message      'Call to function 2nd time
End Sub

Now run this Sub Procedure named Calling_Function. We are pressing the F8 key multiple times to run the Calling_Function() line by line.

Example 2 – Passing Parameters to a Function

We can write a function by passing parameters to it. Let us write a function to add two numbers.

Function Two_Sum(a As Integer, b As Integer)
MsgBox "Sum is :-" & a + b
End Function

Where a and b are the parameters of the integer type whose sum we are going to print through this function. The value of a and b is passed to the function at the time of the function call.

We would use the Sub Procedure named Calling_Function1 to call the Two_Sum function. The code for Calling_Function2 is as follows.

Sub Calling_Function2()
Call Two_Sum(20, 30)
End Sub

With your cursor in the Sub Procedure Calling_Function2(), press the F8 key multiple times. The Sub Procedure will call the Two_Sum function with a=20 and b=30 (parameters passed in function call).

As a result, we get the sum of a and b as 50.

Example 3 – Function Returning Values

A function can also return value. The Data type of the returned value can be Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String (except fixed length), Object, Variant, or any user-defined type.

We would now write a function to get the simple interest. Write the following code in VBE.

Function Simple(p As Integer, r As Byte, t As Double) As Double
Simple = p * r * t / 100
End Function

• The name of the function is Simple.
• We pass three parameters to Simple which are p,r, and t respectively.
• The return type of the function is double.
• The name of the value returned by the function would be the same as the function name.
• We assign p*r*t/100 to Simple ( returned value )

Now when you open an excel spreadsheet and enter the function in any cell:-

=Simple(1000,5,2)

As a result, we get the value of simple interest to be 100.

This brings us to end.

Thank you for reading.