How to Understand VBA Sub vs Function Differences in 3 Minutes (Excel)

Written by Kasper Langmann

VBA, or Visual Basic for Applications, is a programming language developed by Microsoft that is primarily used for automating tasks in Microsoft Office applications. In VBA, there are two types of procedures that you can create: Sub procedures and Function procedures. Although they may seem similar, there are key differences between them that can greatly affect how you write and use your VBA code. In this guide, we will explore these differences and provide you with the knowledge you need to use Subs and Functions effectively in your VBA programming.

Understanding Sub Procedures

What is a Sub Procedure?

A Sub procedure in VBA is a block of code that performs a specific task. Sub procedures are typically used for tasks that need to be performed multiple times throughout your code. They can be called or invoked from other parts of your program, allowing you to avoid writing the same code over and over again.

Sub procedures do not return a value. This means that when a Sub procedure is called, it simply performs its task and then control returns to the calling procedure. Because of this, Sub procedures are often used for tasks that do not require a result to be returned, such as updating a spreadsheet or displaying a message to the user.

How to Create a Sub Procedure

Creating a Sub procedure in VBA is straightforward. You start by writing the keyword ‘Sub’, followed by the name of your procedure. You then write your code inside the procedure, and end it with the keyword ‘End Sub’. Here is an example:

Sub MyProcedure()
' Your code here
End Sub

This creates a Sub procedure named ‘MyProcedure’. You can call this procedure from other parts of your code by simply writing its name followed by parentheses, like so: MyProcedure().

Understanding Function Procedures

What is a Function Procedure?

A Function procedure in VBA is similar to a Sub procedure in that it is a block of code that performs a specific task. However, unlike a Sub procedure, a Function procedure returns a value. This means that after a Function procedure has performed its task, it produces a result that can be used by the calling procedure.

Function procedures are typically used for tasks that require a result to be returned, such as calculations or data processing. The result of a Function procedure can be used in expressions, assigned to variables, or displayed to the user.

How to Create a Function Procedure

Creating a Function procedure in VBA is similar to creating a Sub procedure. You start by writing the keyword ‘Function’, followed by the name of your procedure. You then write your code inside the procedure, and end it with the keyword ‘End Function’. However, you also need to specify the return value of your function using the keyword ‘Return’. Here is an example:

Function MyFunction() As Integer
' Your code here
Return result
End Function

This creates a Function procedure named ‘MyFunction’ that returns an integer. You can call this function from other parts of your code by simply writing its name followed by parentheses, like so: MyFunction(). The result of the function can then be used in your code.

Key Differences Between Sub and Function Procedures

Return Value

The most significant difference between Sub and Function procedures is that Function procedures return a value, while Sub procedures do not. This means that Function procedures can be used in expressions or assigned to variables, while Sub procedures cannot.

For example, you could use a Function procedure to calculate the sum of two numbers, and then use the result in an expression, like so: total = SumFunction(5, 10) * 2. This would not be possible with a Sub procedure.

Usage

Another key difference between Sub and Function procedures is how they are used. Sub procedures are typically used for tasks that need to be performed multiple times throughout your code, but do not require a result to be returned. Function procedures, on the other hand, are used for tasks that require a result to be returned.

For example, you might use a Sub procedure to update a spreadsheet or display a message to the user, while you might use a Function procedure to perform a calculation or process data.

Conclusion

Understanding the differences between Sub and Function procedures in VBA can greatly enhance your programming skills and allow you to write more efficient and effective code. By using Sub procedures for tasks that do not require a result, and Function procedures for tasks that do, you can ensure that your code is well-structured and easy to understand.

Remember, the key difference between Sub and Function procedures is that Function procedures return a value, while Sub procedures do not. This affects how these procedures can be used in your code, and what tasks they are best suited for. So, the next time you’re writing VBA code, consider whether a Sub or Function procedure would be the most appropriate choice for your task.