Excel VBA Functions And Sub Procedures [With Examples]

In this tutorial, we will learn about Excel VBA functions, Sub procedures, and the difference between them:

If you have just started learning to code in VBA, then you will obviously find it easy to write the entire code in one Sub. You might not even know that VBA does not just support SUB, but it also supports functions.

We will also learn how to write our own custom functions and Sub, how to use them in worksheets, along with all the details about passing the values between different functions.

=> Explore The FULL Excel VBA Series

What Is A VBA Function

A function is a program that has a set of statements that are performed and the result is returned. Functions are basically used when there is a need for certain tasks to be performed repeatedly.

Functions are mainly used to avoid redundancy and achieve reusability in a large program. A function is normally used when you wish to return a value.

Syntax:

[Modifier] Function Functionname [ ( arglist ) ] [ As type ]
[ statements ]
End Function

Modifier: It is an optional field, if not specified it takes the default value of Public. More about Modifier and scope will be discussed later in this tutorial.

Function: It is the keyword and has to be mentioned while declaring a function.

Functioname: You can mention any name that you choose for a function. There are certain naming conventions that are to be followed.

• The first character should be a character
• Usage of a space, period (.), exclamation mark (!),@, &, $, # is not allowed.
• The name should not exceed 255 characters in length.
• It cannot have any keyword as a name.

argList: List of variables that are passed to a function when it is called. Multiple variables are separated by commas. An argument can be passed by ByVal or ByRef. It will be discussed later in this tutorial.

Type: It is the data type of the value returned by the function.

Statements: Set of actions that are performed within the function.

VBA Functions Example

Let’s try to find the diameter of a circle.

Function diameter(Radius As Double) As Double
diameter = 2 * Radius
End Function

In the above code, we have not added any modifier i.e. the function is publicly accessible.

• Function is a keyword that is used while declaring a Function.
• diameter is the name of the function.
• Radius is the argument of type Double.
• Datatype of the value returned by the function is Double.
• Diameter =2*Radius is the statement.

Adding VBA Code

Before we proceed, let’s make ourselves clear on where to add the procedure in Excel.

• Open the Excel workbook.
• Go to the Developer tab. If you don’t have the Developer tab refer here
• Developer -> Visual Basic or alternatively Alt+F11.
• This will open a new window of the VBA Editor.
• Go to Insert -> Module, this will open a new module where you can write your code.

Executing The Code

Go to the Excel worksheet where you have placed your command button and disable the Design mode from the Developer tab and click on the command button.

Scope Of VBA Functions And Procedures

We have discussed the scope of the variable earlier.

Those have the same meaning for the functions and subprocedures in VBA.

If a modifier is not specified while declaring a function or a sub-procedure, then by default it is treated as public.

Calling VBA Functions

Let’s try to call the above function in our worksheet. To call a function we have to use the function name.

Go back to the worksheet and in any cell hit =diameter(value). Refer to the screenshot below.

Once you hit =dia, VBA will give you a recommendation of all the functions that are available. In this example after selecting diameter, the argument for the function is given as cell E9, which contains value 1.2.

As mentioned in the diameter function diameter = 2*(value in E9), hence the result is 2.4 and is populated in the cell where you have added the diameter function.

Returning Values From Function

It is always recommended to divide the program into small parts so that it becomes easier to maintain. In that case, calling a function and returning a value from a function becomes important.

In order to return a value from or to a function, we need to assign the value to the function name.

Consider the below example

Function EmployeeDetails()
     Debug.Print GetName & "'s" & " Bonus Is " & GetBouns(400000); ""
End Function
________________________________________
Function GetName()
     GetName = "John"
End Function
________________________________________
Function GetBouns(Salary As Long) As Double
     GetBouns = Salary * 0.1
End Function

In the example above we have a function, EmployeeDetails that will print the bonus of the employee.

Instead of adding all the details in one function, we have divided it into 3 functions, one to print values, one to get the employee name, and one to calculate the bonus.

GetName() function takes no argument hence you can directly call it by the name in the main function which is EmployeeDetails() and GetBonus takes one argument, hence you are passing the value of salary from the main function

The result will be as shown below.

Exit Function

VBA allows us to make an early exit from a function using the Exit Function statements.

Let’s understand the same with an example.

Private Function MainFunction()
      Debug.Print "Calling ExitFunExample"
      Value = ExitFunExample()
      Debug.Print " Result is " & Value
End Function
________________________________________
Private Function ExitFunExample() As Integer
    For i = 1 To 10 Step 2
         If i = 7 Then
               Debug.Print "Calling Exit Function and Returning to Main Function"
               ExitFunExample = i
               Exit Function
            End If 
          Next i
End Function

In the above example, the MainFunction prints the message “Calling ExitFunExample” and the control then goes to ExitFunExample().

In ExitFunExample() the control enters the loop and iterates from 1 to 10 incrementing by 2. When the i value reached 7, the control goes inside the if block, assigns the i value to the function and exits from that function, and returns to the MainFunction().

The result is as shown below.

What Is A Sub-Procedure

Sub-Procedure is a group of statements that perform the specified tasks but a sub-procedure will not return the result. Unlike function, Sub doesn’t have a return type in the syntax as shown below.

It is mainly used to divide a large program into small parts so that maintaining the code becomes easier.

Sub procedure is a series of statements enclosed between Sub and End Sub statements. The Sub procedure performs a specific task and returns control to the calling program, but it does not return any value to the calling program.

Syntax

[modifiers] Sub SubName[(parameterList)]
‘Statements of the Sub procedure.
End Sub

Sub-Procedure Example

Let’s create a sub-procedure to find the area of a circle.

Sub AreaOfCircle(Radius As Double)
             AreaOfCircle = 3.14 * Radius * Radius
End Sub

Head to Excel sheet and type =Area.

In the above code, though you have a sub-procedure as AreaOfCircle, it is not shown in the worksheet. The reason is Sub Procedure does not return any value. Hence your worksheet is not identifying the AreaOfCircle.

You can use Sub to clear the cell contents, Delete row, etc.

So let’s go ahead and write a code to clear the contents from rows 3 to 5.

Sub clearCell()
     Dim myRow As Range
     Set ClearRange = Worksheets("Sheet1").Range("A3:D5")
     ClearRange.Clear
End Sub

Let’s create an Excel with data from A1 to D10

To execute a sub procedure, click on the title of the code i.e. Sub clearCell(), Or select the entire code and hit on Run Sub/Userform (ShortCut F5).

After executing the code, the resulting table will be as shown below.

Calling A Sub Inside Another Sub

Like functions, we can break the subs into multiple subs and call one from another.

Let’s build a simple calculator where the main Sub makes 4 different Sub calls.

Sub mainSub()
Dim a, b As Integer

Call Add(2, 4)
Call Minus(4, 3)
Call Multiply(4, 4)
Divide 4, 4
Result
End Sub
________________________________________
Sub Add(a, b)
c = a + b
Debug.Print "Value of Addition " & c
End Sub
________________________________________
Sub Minus(a, b)
c = a - b
Debug.Print "Value of Subtraction " & c
End Sub
________________________________________
Sub Multiply(a, b)
c = a * b
Debug.Print "Value of Multiplication " & c
End Sub
________________________________________
Sub Divide(a, b)
c = a / b
Debug.Print "Value of Division " & c
End Sub
________________________________________
Sub Result()
Debug.Print "Results are displayed successfully"
End Sub

VBA provides us with the Call keyword to call a Sub.

Observe in the above code, that we have used the Call keyword to call Add, Minus, Multiple Subs, but we have not used the keyword for Divide.

Call keyword is optional. If you are not using any argument to call a sub, then you can just mention the sub name without the Call keyword as shown for the Sub Result in the above example.

But if you are using arguments and you wish not to use the Call keyword then you should not put parentheses, example for Divide we have not used parentheses and no Call keyword.

If you are adding arguments inside parentheses then you have to use the Call keyword as we have used for Addition, Minus, and Multiplication.vIt is recommended to use the Call keyword as it increases the readability of the code.

The result will be as shown below.

Exit Sub

Exit Sub is similar to the Exit Function but remember that Subs will not return any value.

Consider the below example.

Private Sub MainSub()
Debug.Print "Calling ExitSubExample "
     Call ExitSubExample
     Debug.Print " End of main sub"
End Sub
________________________________________

Private Sub ExitSubExample()
      Dim i As Integer

      For i = 1 To 10 Step 2
If i = 7 Then
             Debug.Print “Executing Exit Sub statement"
             Exit Sub
         End If
Debug.Print "The value of i is " & i
     Next i
End Sub

In the above example, the MainSub will start execution and print the message “Calling ExitSubExample”. Then the control goes to ExitSubExample Sub.

ExitSubExample, will enter the For Loop and loop till i value is less than 10 and increment by 2. If i value is equal to 7 then the If command will be executed and then Exit Sub and after every iteration i value is printed.

Once the control is back to MainSub “End of main function” will be printed.

As shown in the result, the i value is not printed after it reaches 7, because the sub is Exited when the i value reached 7.

Consider the same example but let’s put a condition as i=0 so that the control never goes into if block and hence Exit Sub is not executed.

Private Sub MainSub()
Debug.Print "Calling ExitSubExample "
     Call ExitSubExample
     Debug.Print " End of main sub"
End Sub
________________________________________

Private Sub ExitSubExample()
    Dim i As Integer

    For i = 1 To 10 Step 2
If i = 0 Then
              Debug.Print “Executing Exit Sub statement"
              Exit Sub
          End If
Debug.Print "The value of i is " & i
     Next i
End Sub

The results below show that Exit Sub is not executed at all.

Difference Between Functions And Sub-Procedure

Passing Variables ByRef And ByVal

If there are multiple functions and subs used in the program, then it is necessary to pass variables or values between them.

VBA allows us to pass the values in 2 ways ByVal and ByRef. By default, if you don’t mention anything then VBA treats it as ByRef.

ByVal: It will create a copy of the variable i.e. if you make a change to the value of the parameter in the called function, then its value will be lost when you return to the calling function. The value won’t be retained.

ByVal is useful when you don’t want to change the original data, and you simply want to use that value and manipulate it in another sub or function. ByVal will help you to protect the original value by making a copy of the same, and the copy is passed to another sub or function thereby preserving the original value.

ByRef: It will create a reference of the variable i.e. if you make a change to the value of the parameter in the called function, then its value will be retained when you return to the calling function.

ByRef is useful when there is a genuine requirement to change the value of the variable or object in the calling program.

Consider the below example.

Sub byValexample()
Dim a As Integer
a = 10
Debug.Print " Value of a before calling AddTen ByVal function " & a
ByValAddTen (a)
Debug.Print " Value of a after calling ByValAddTen function " & a
End Sub
________________________________________
Function ByValAddTen(ByVal a As Integer) As Integer
a = a + 10
ByValAddTen = a
Debug.Print " Value of a inside ByVal AddTen function " & a
End Function

In the above example, we are demonstrating how ByVal works. The original value of the variable is not changed.

Given below is the result.

If you observe, the value of a is manipulated inside the function but when the control returns back to the main function, then a value is not changed.

Let’s write the same code but this time by using ByRef.

Sub byRefExample()
Dim a As Integer
a = 10
Debug.Print " Value of a before calling AddTen ByRef function " & a
ByRefAddTen a
Debug.Print " Value of a after calling ByRef AddTen function " & a
End Sub
________________________________________
Function ByRefAddTen(ByRef a As Integer) As Integer
a = a + 10
ByRefAddTen = a
Debug.Print " Value of a inside ByRef AddTen function " & a
End Function

The resulting window shows that the value of a is retained after it is being retuned back to the called function as it is using the reference of the variable.

ByRef With Parentheses

You have to be very careful while using ByRef. If you use ByRef with parentheses then the function will not be able to change the value though you have used ByRef.

Let’s write the above code but this time with parentheses.

Sub byRefwithparentheses ()
Dim a As Integer
a = 10
Debug.Print " Value of a before calling AddTen ByRef function " & a
ByRefAddTen (a) ‘ enclose an inside parentheses
Debug.Print " Value of a after calling ByRef AddTen function " & a
End Sub
________________________________________
Function ByRefAddTen(ByRef a As Integer) As Integer
a = a + 10
ByRefAddTen = a
Debug.Print " Value of a inside ByRef AddTen function " & a
End Function

The result above shows that though we have used ByRef, since we are using parentheses while calling the function, the value of a is not changed.

Frequently Asked Questions

Q #1) What are VBA Functions?

Answer: Function is a set of actions that are called anywhere in the program. This helps us to reuse the same program whenever necessary without the need to write it again.

VBA has many built-in functions and it also allows the users to create their own custom functions using the VB editor.

Q #2) What is ByVal in VBA?

Answer: ByVal will pass a copy of the variable to the Sub or function. Changes made to the copy will not alter the original value of the variable.

Q #3) How to use VBA functions in Excel?

Answer: Enable the Developer tab in Excel.

Go to Developer -> Visual Basic or Press Alt+ F11
This will open the VB editor.
Go to Insert -> Module
You can write functions or Sub-Procedure in this Editor.
To execute press F5 or click on the Run button on the menu bar.
Or go to the worksheet, click on any cell press = and you can find your function name.

Q #4) What is a Public and Private function in VBA?

Answer: Public subs or functions is visible and can be used by all the modules in that workbook.

Private subs and functions is visible and can only be used by procedures within that module. The scope of the functions or sub is limited to only that module.

Q #5) What is ByRef in VBA?

Answer: It will create a reference of the variable i.e. if you make a change to the value of the parameter in the called function, then its value will be retained when you return to the calling function.

Conclusion

In this tutorial, we have learned about Excel VBA functions and subprocedures. We also discussed the differences between them. We saw how to write custom functions and use them in the workbook.

Calling a function or a sub inside another has also been discussed in this tutorial and this will help us to reduce the length of the code and gives better readability.

We also learned about passing variables ByVal and ByRef between functions or subs.

=> Check ALL Excel VBA Tutorials Here