VBA: Sub vs Function

What is Sub vs Function?

In programming, a Sub is a subroutine that performs a task without returning a value, while a Function is a subroutine that returns a value after performing a task.

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 for automating tasks within Microsoft Office applications. VBA can easily automate repetitive tasks by creating macros, performing advanced data calculations, and interacting with other software applications.

What is a VBA Sub?

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 refers to 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, but they are optional. Therefore, when you call a Sub procedure, you can choose to pass it input parameters if needed. 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 subroutine called 'MySubroutine' using the 'Sub' keyword.

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.

Let's examine a few below:

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 and help create reusable code for 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 is a VBA Function?

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 it easier to work in Microsoft Office applications like Excel, Word, and PowerPoint.

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.

Like any other Excel function, the "AddNumbers" function can be called from a worksheet cell or another VBA module.

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

Sometimes, 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. Putting your function in a Sub procedure lets you keep your code organized and make it easier to read and maintain.

Free Resources

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

• VBA Do Loop
• VBA If Else
• VBA Loop
• VBA Variables, Data Types and Dim
• VBA Workbook Events