VBA: Sub vs Function

MS Excel is a very powerful tool for organizing and analyzing data by formatting cells, sorting data, and creating charts manually. However, it can be time-consuming because they are repetitive tasks.

Visual Basic for Application (VBA) is a programming language that can help automate these tasks, reduce errors, and save time.

Custom codes are made with VBA that interacts with Excel and other Microsoft applications, giving you the power to automate complex workflows and perform advanced calculations.

Many industries, including finance, accounting, data analysis, academia, and research, widely use VBA - a versatile programming language.

VBA can easily automate repetitive tasks by creating macros, performing advanced data calculations, and interacting with other software applications.

This article will discuss two main types of procedures in VBA: Subs and Functions, and provide you with a comprehensive understanding of their differences.

This article will give the tools and information necessary to leverage VBA's capabilities in Excel fully.

What are subs in VBA?

In VBA (Visual Basic for Applications), a "Sub" is a type of procedure used to group a set of instructions that perform a specific task.

A Sub procedure in VBA is a block of code that performs a specific task. Sub procedures are defined using the "Sub" keyword followed by a name and optionally input parameters.

"Sub" is a short form for "Subroutine," which is used for a set of instructions that can do specific things in Excel, known as "macros."

These procedures are frequently employed to automate monotonous or complicated tasks within Microsoft Office programs like Excel, Word, and PowerPoint.

They help perform a series of tasks or calculations on a specific set of information or interact with other applications or systems.

A Sub in VBA is defined using the "Sub" keyword, followed by a name and a set of parentheses. Any arguments or parameters that the Sub requires can be specified within the parentheses.

Subs can have zero or more input parameters. Therefore, when you call a Sub procedure, you can pass it some variables called input parameters.

These parameters are like placeholders that allow the Sub procedure to use different data sets or values each time it is called.

How to use Sub in VBA?

Imagine that we want to add two numbers together using VBA in Excel. Then, we can create a "Sub" called "MySubroutine."

For adding the two arguments, "x" and "y,"

Sub MySubroutine(x As Integer, y As Integer)

   Dim result As Integer

   result = x + y

   MsgBox "The sum is " & result

End Sub

After creating the Subroutine, we must write its name and provide the necessary arguments in parentheses. This will release us from the hassle of rewriting the same code repeatedly.

For example, the following code calls the "MySubroutine" and passes the values 2 and 3 as arguments:

Sub CallMySubroutine()

   MySubroutine 2, 3

End Sub

When this code runs, a message box will pop up with the text "The sum is 5".

Subroutines can also manipulate Excel objects such as worksheets, ranges, and cells. For example, the following code defines a Sub called "FormatRange" that sets the font size and color of a specific range of cells:

Sub FormatRange()

   Range("A1:B10").Font.Size = 12

   Range("A1:B10").Font.Color = RGB(255,0,0)

End Sub

When executed, this code will change the font size and color of cells A1 to B10 to 12 points and red, respectively.

Types of Sub-Procedures in VBA

A Sub is a type of procedure that performs a set of actions or operations without returning a value.

There are several types of Sub procedures in VBA, each with its specific purpose and use case.

1. Standard Sub Procedures

Standard Sub Procedures are the most common type of Sub in VBA. They are used to perform a series of actions or operations without returning a value.

Standard Subs are declared using the keyword "Sub" followed by the name of the procedure and any necessary arguments.

2. Private Sub Procedures

Private Sub Procedures are similar to Standard Subs but are declared with the keyword "Private."This means that they can only be accessed within the module where they are declared.

Private Subs are helpful in encapsulating code and preventing other parts of the application from accessing or modifying it.

3. Public Sub Procedures

Public Sub Procedures are similar to Standard Subs but are declared with the keyword "Public." They can be accessed from any module or part of the application.

Public Subs are helpful in creating reusable code used across different parts of the application.

4. Event Procedures

Event Procedures are a special type of Sub that is triggered automatically in response to a specific event, such as clicking a button or opening a form.

Event Procedures are typically used to perform actions or operations in response to user input.

What are the Functions in VBA?

A Function procedure in VBA is a block of code that performs a specific task and returns a value. It is defined using the "Function" keyword, followed by a name and optional input parameters.

Function procedures can help automate repetitive or complex tasks, making working in Microsoft Office applications like Excel, Word, and Powerpoint easier.

These procedures are especially handy when doing a series of calculations or operations on a large data set or getting a specific value based on certain conditions or criteria.

Function procedures can have zero or more input parameters. When we call a Function procedure, we can pass in variables called input parameters.

These input parameters allow the Function to work with different data sets or parameters every time it is called.

Input parameters are defined in the Function procedure header, enclosed in parentheses, and separated by commas.

How to make a Function in VBA?

To make a Function in VBA, start by declaring it with the keyword "Function," followed by its name and parameters.

Let's create a function that adds two numbers; the function's name will be "AddNumbers" and specify that it takes two parameters.

The code would be:

Function AddNumbers(num1 As Integer, num2 As Integer) As Integer

    AddNumbers = num1 + num2

End Function

In the above-written code, the Function takes two integer parameters, adds them together, and returns the result as an integer value using the "As Integer" declaration.

The "AddNumbers" function can be called from a worksheet cell or another VBA module like any other Excel function.

For example, the following formula in a worksheet cell would call the "AddNumbers" function with the parameters 5 and 10 and return the result to the cell:

=AddNumbers(5, 10)

VBA Functions are a type of code module that can be created in Excel. Functions are used to perform calculations and return a value. They can be called from other VBA modules or a button or macro in the Excel user interface.

Vba Sub Vs. Function

VBA is a powerful programming language commonly used to develop macros and custom applications within Microsoft Office programs such as Excel, Access, and Word. 

Two of VBA's most fundamental building blocks are Sub procedures and Function procedures.

Sub and Function procedures define blocks of code that can be executed when called.

Although these two share some similarities, key differences make them suited to different tasks.

How to put a Function in a Sub VBA

In some cases, you may want to define a function within a Sub procedure. It helps you keep your code concise and organized without cluttering it with unnecessary functions.

Here's how to put a function in a Sub VBA:

Open your VBA project and create a new Sub procedure. You can do this by clicking "Insert" on the VBA editor menu and selecting "Module."

To define the Sub procedure, start using the keyword "Sub" and give it a name. Also, specify any arguments that you want to pass on to it. For example

Sub MySub(MyVariable As String)

When you're inside the Sub procedure, you must define your function using the "Function" keyword. Then give your function a name and specify the arguments.

For example

Function MyFunction(MyVariable As String) As Integer

Remember to declare any variables you need and to include a "Return" statement to return the value of the function. 

For example

Dim MyCount As Integer

MyCount = Len(MyVariable)

MyFunction = MyCount

End the function with the "End Function" statement.

Call your function from within your Sub procedure, passing any necessary arguments. 

For example

Dim MyResult As Integer

MyResult = MyFunction(MyVariable)

End the Sub procedure with an "End Sub" statement.

That's it! Your function is now defined within your Sub procedure and can be called from anywhere within your code.

Researched and authored by Mohit Bourai | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful  WSO resources:

• Excel Shortcuts
• Football Field Graphs
• Goal Seek Function
• Power Query
• VBA: How to Debug Code