5.2 Declaring and Calling Procedures.

In VBA, Procedures are blocks of code that perform specific tasks. There are two types of procedures: Subroutines (Subs) and Functions. Both can be declared (defined) and called (executed) in various ways. Understanding how to declare and call procedures allows you to structure your code for maximum reusability and clarity.

Declaring a Subroutine

A Subroutine is declared using the Sub keyword, followed by the name of the subroutine, and then the code block enclosed in End Sub. A Subroutine can accept parameters (inputs) if necessary, and it doesn't return any value.

Syntax to Declare a Subroutine:

Sub SubroutineName(Optional Param1 As DataType, Optional Param2 As DataType)
    ' Code to perform the task
End Sub

• SubroutineName: The name of the subroutine.
• Optional Param1, Param2: Parameters are optional and can be used to pass values into the procedure.

Example of Declaring a Subroutine:

Sub DisplayMessage(message As String)
    MsgBox message
End Sub

In this example, the DisplayMessage subroutine takes one parameter (message) and shows a message box with the given text.

Declaring a Function

A Function is similar to a subroutine but is used when you need to return a value. It uses the Function keyword followed by the function name, the parameters, and the return value.

Syntax to Declare a Function:

Function FunctionName(Param1 As DataType, Param2 As DataType) As DataType
    ' Code to perform the calculation
    FunctionName = [Return Value]
End Function

• FunctionName: The name of the function.
• Parameters (Param1, Param2): The inputs that the function takes.
• Return Type: Specifies what type of value the function will return (e.g., Integer, String).

Example of Declaring a Function:

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

This AddNumbers function takes two numbers, adds them, and returns the result.

Calling a Subroutine

You can call a subroutine by simply typing its name. If the subroutine requires parameters, you pass the required values when calling it. You can also use the Call keyword, although it's optional and less common.

Syntax to Call a Subroutine:

Sub CallSubroutine()
    SubroutineName [Optional Parameters]
End Sub

Example of Calling a Subroutine:

Sub ShowMessage()
    DisplayMessage "Hello, World!"  ' Calling the DisplayMessage Subroutine
End Sub

Alternatively, you can use the Call keyword:

Sub ShowMessage()
    Call DisplayMessage("Hello, World!")
End Sub

In both examples, the DisplayMessage subroutine is called with the message "Hello, World!".

Calling a Function

Functions are called similarly to subroutines but are often used to assign their return value to a variable. You cannot use a function in place of a subroutine unless you need to work with its return value.

Syntax to Call a Function:

Sub CallFunction()
    result = FunctionName([Parameters])
    MsgBox result
End Sub

Example of Calling a Function:

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

In this case, the AddNumbers function returns the sum of 5 and 10, which is stored in the total variable and displayed in a message box.

Passing Parameters to Procedures

When declaring a procedure (whether a subroutine or a function), you can pass parameters to modify how the procedure behaves. Parameters can either be required or optional.

• Required Parameters: Parameters that must be provided when calling the procedure.
• Optional Parameters: Parameters that can be omitted, and a default value will be used if not provided.

Example with Required Parameters:

Sub ShowGreeting(name As String)
    MsgBox "Hello, " & name
End Sub

Here, name is a required parameter.

Example with Optional Parameters:

Sub ShowGreeting(Optional name As String = "Guest")
    MsgBox "Hello, " & name
End Sub

In this example, name is optional, and if no value is provided, "Guest" will be used as the default value.

Best Practices for Declaring and Calling Procedures

1. Keep Procedures Small and Focused:
   Each subroutine or function should do one thing well. This makes the code easier to maintain and debug.
2. Use Meaningful Names:
   Name your procedures descriptively, reflecting the action or purpose, such as DisplayMessage, CalculateTotal, or SaveData.
3. Use Parameters Effectively:
   Use parameters to make procedures flexible and reusable. Consider using Optional parameters with default values to handle cases where some inputs might not be provided.
4. Comment Your Procedures:
   Add comments to explain the purpose of the procedure, the parameters it accepts, and any important details, especially if the code is complex.
5. Avoid Overloading:
   Unlike some other languages, VBA doesn't support function overloading (having multiple functions with the same name but different parameters). Instead, try to handle different inputs by using Optional parameters or different function names.

Summary

• Subroutines (Sub) perform actions and do not return values. They can be called directly or with the Call keyword.
• Functions perform actions and return a value, which can be used elsewhere in your code or worksheet.
• Procedures can accept required or optional parameters, allowing for flexibility in how they are used.

By mastering how to declare and call procedures in VBA, you can write modular and reusable code that simplifies complex tasks and makes your programming more efficient.