5.1 Subroutines vs. Functions.

In VBA, Subroutines and Functions are two of the main building blocks used to organize and execute code. Both serve different purposes but are essential for writing clean, modular, and reusable code.

Subroutines (Sub)

A Subroutine (commonly referred to as "Sub") is a block of code that performs a specific task but does not return a value. Subroutines are ideal for executing actions, such as updating a cell's value, formatting ranges, or interacting with the user interface.

Syntax of a Subroutine:

Sub SubroutineName()
    ' Code to perform the task
End Sub

Example:

Sub DisplayMessage()
    MsgBox "Hello, this is a Subroutine!"
End Sub

In this example, the subroutine displays a message box when called.

When to Use Subroutines:

• When you need to perform an action (e.g., displaying a message, modifying cells).
• When no value needs to be returned.

Functions

A Function is similar to a subroutine, but the key difference is that a function returns a value after performing its task. Functions are typically used when you need to return a calculated result, process some data, or perform a computation that will be used elsewhere.

Syntax of a Function:

Function FunctionName() As DataType
    ' Code to perform a task and return a value
    FunctionName = [Value]
End Function

Example:

Function AddNumbers(a As Double, b As Double) As Double
    AddNumbers = a + b
End Function

In this example, the function calculates the sum of two numbers and returns the result.

When to Use Functions:

• When you need to perform a calculation or return a result.
• When the result of the task will be used in another part of the code or in a worksheet formula.

Key Differences Between Subroutines and Functions

Examples of Calling Subroutines and Functions

Calling a Subroutine

Subroutines can be called directly by their name or using the Call keyword.

Sub CallSubroutine()
    DisplayMessage  ' Calling the DisplayMessage Subroutine
End Sub

Calling a Function

Functions are called by assigning the returned value to a variable or using the function in expressions.

Sub CallFunction()
    Dim result As Double
    result = AddNumbers(5, 10)  ' Calling the AddNumbers Function
    MsgBox "The result is: " & result
End Sub

Using Functions in Excel

You can use VBA functions in Excel formulas, just like built-in Excel functions. After creating a function in a module, you can use it in an Excel cell formula:

1. Write the function in VBA:

   Function MultiplyNumbers(a As Double, b As Double) As Double
       MultiplyNumbers = a * b
   End Function
   

2. Use it in Excel: In a cell, type: =MultiplyNumbers(5, 10)
   The result will be 50.

Summary

• Subroutines are best for performing actions and tasks that don’t need to return a result, such as modifying cell properties or interacting with the user.
• Functions return values after performing a task and are used when you need the output to be used in other parts of your code or in Excel formulas.

By understanding and using both Subroutines and Functions appropriately, you can write more organized, efficient, and reusable code in VBA.