Blog

Procedures in VBA are a set of instructions designed to perform a specific task. The purpose of creating a procedure is to better organise code, improve readability, and enable the reuse of repetitive sections of a program.

Defining a Procedure

Procedures in VBA are divided into three main categories: Sub, Function, and Property. Each has a specific use, but they are very similar in their overall structure.

Sub Procedure

A Sub procedure is used to execute a set of instructions but does not return a value. It is typically used for tasks such as displaying messages, performing calculations, or changing cell values.

The complete structure of a Sub procedure is as follows:

[ Private | Public | Friend ] [ Static ] Sub name [ ( arglist ) ]
    [ statements ]
    [ Exit Sub ]
    [ statements ]
End Sub

Section	Explanation
Private | Public | Friend	Scope: Public (default) from all modules, Private only from the current module, Friend at the project level
Static	If used, the values of local variables are preserved between calls
Sub name	The Sub keyword followed by the procedure name
[ ( arglist ) ]	Optional list of input arguments
[ statements ]	Procedure body – executable statements
[ Exit Sub ]	Conditional exit from the procedure
End Sub	End of the procedure

Basic Sub Procedure Example

The following example calculates and displays the sum of two numbers:

Public Sub SumNumbers(a As Integer, b As Integer)
    MsgBox "The sum of the numbers is: " & (a + b)
End Sub

'Call:
Call SumNumbers(5, 7)

Early Exit from a Sub Procedure

You can use the Exit Sub statement to exit the procedure early. For example, if an input value is invalid:

Sub CheckValue(x As Integer)
    If x < 0 Then
        MsgBox "Negative numbers are not allowed!"
        Exit Sub
    End If
    MsgBox "Entered number: " & x
End Sub

Error Handling in Sub Procedures

In Sub procedures, you can also handle runtime errors using the On Error GoTo statement and a label. This method prevents the program from stopping abruptly and allows an appropriate message to be displayed to the user.

Consider the following example:

In this example, the ThisWorkbook.Sheets(sheetName).Activate command attempts to activate the specified sheet in the current workbook (ThisWorkbook). If the sheet does not exist, a runtime error occurs. Using error handling, the program responds in a controlled manner and prevents sudden termination.

Sub OpenWorksheet(sheetName As String)
    On Error GoTo errHandler   ' Enable error handling

    ' Attempt to activate the target sheet
    ThisWorkbook.Sheets(sheetName).Activate
    Exit Sub                   ' Successful exit from Sub

errHandler:                     ' Error handling label
    If Err.Number = 9 Then
        MsgBox "The sheet '" & sheetName & "' does not exist in this workbook."
    Else
        MsgBox "Error Number " & Err.Number & ": " & Err.Description
    End If
End Sub

📌 Explanation:

• On Error GoTo errHandler: If an error occurs, execution of the Sub is transferred to the errHandler section.
• Exit Sub: After the commands execute successfully, we exit the Sub so the error handling section is not run.
• In the errHandler section, you can manage specific errors or display a general message for other errors.

This method makes your programme more reliable and stable and prevents sudden termination due to errors.

Function Procedure

A Function procedure is similar to a Sub, with the key difference that it can return a value. This means it can be used like a variable in other parts of the programme.

The complete structure of a Function procedure is as follows:

[Public | Private | Friend] [ Static ] Function name [ ( arglist ) ] [ As type ]
    [ statements ]
    [ name = expression ]
    [ Exit Function ]
    [ statements ]
    [ name = expression ]
End Function

Section	Explanation
Public | Private | Friend	Scope (similar to Sub)
Static	Preserves values of local variables between calls
Function name	The Function keyword and the function name
[ ( arglist ) ]	Optional list of input arguments
[ As type ]	Data type of the return value
[ name = expression ]	Assigning the return value to the function name
[ Exit Function ]	Conditional exit from the function
End Function	End of the function

Basic Function Procedure Example

Function Multiply(x As Double, y As Double) As Double
    Multiply = x * y
End Function

' Using the function:
Dim result As Double
result = Multiply(5, 3.5)
MsgBox "The product is: " & result

Return Value in a Function Procedure in VBA

The return value in a Function procedure must correspond to the data type defined for that function. In other words, if the function is explicitly defined with a data type such as Integer, Double, String, or any other type, the return value must also be of that type or a type convertible to it.

To specify the return value, the desired value is assigned to the function name using the = assignment operator. This tells the VBA compiler that this value is the final result of the function.

Typically, a function’s return value is set at the end of the procedure, before End Function. However, if for any reason you need to set the function’s value within the body of the function, remember to use the Exit Function statement immediately after the assignment to stop the function’s execution promptly.

Consider the following example:

Function CalcJalaliMonthDays(monthNum As Byte) As Byte
    If monthNum = 0 Or monthNum > 12 Then
        MsgBox "The month number has not been entered correctly."
    End If
    If monthNum < 7 Then
        CalcJalaliMonthDays = 31
    End If
    CalcJalaliMonthDays = 30
End Function

📌 Note:
In this case, regardless of the input value monthNum, the return value will always be 30. The only difference is that if monthNum is 0 or greater than 12, a warning message is displayed, but the function’s value is still 30.

The correct and standard code using Exit Function is as follows:

Function CalcJalaliMonthDays(monthNum As Byte) As Byte
    If monthNum = 0 Or monthNum > 12 Then
        MsgBox "The month number has not been entered correctly."
        Exit Function
    End If
    
    If monthNum < 7 Then
        CalcJalaliMonthDays = 31
        Exit Function
    End If
    
    CalcJalaliMonthDays = 30
    Exit Function
End Function

📌 Explanation:
In the corrected version:

• If the input is invalid, a message is displayed and the function terminates immediately.
• For months 1 to 6, the return value is 31, and after setting the value, the function terminates.
• Otherwise, the value 30 is returned for months 7 to 12.

Using Exit Function in such situations ensures the return value is exactly as expected and prevents subsequent commands from executing unintentionally.

Assigning a value to the function does not mean exiting it. If there are other commands in the function body after the assignment, those commands will also be executed. If you want the function’s execution to stop at that point, you must use the Exit Function statement.

🔹 Example 1: Function Without Using Exit Function

In this example, the return value is set, but because Exit Function is not used, the rest of the code also runs.

Function SquareNum(x As Double) As Double
    SquareNum = x * x           ' Set the return value
    MsgBox "This message will also be displayed after the return value is set."
End Function

📘 Explanation:
If the function SquareNum(4) is called, the return value will be 16, but the message box will also be displayed because we haven’t exited the function yet.

🔹 Example 2: Function Using Exit Function

In this example, the function terminates immediately after setting the return value.

Function SquareNumSafe(x As Double) As Double
    If x < 0 Then
        MsgBox "Negative input is not allowed!"
        Exit Function             ' Early exit from the function
    End If
    
    SquareNumSafe = x * x
    Exit Function                 ' Exit after setting the return value
End Function

📘 Explanation:
In this case, if the input number is negative, a warning message is displayed and the function does not continue without returning a value.
Otherwise, the square of the number is calculated and returned immediately.

Returning an Object with a Function in VBA

Sometimes the return value of a function is not a simple data type but rather an Object, such as a Worksheet or Workbook. In such cases, to set the function’s return value, the object reference must be assigned to the function name using the Set statement.

Consider the following example:

Function GetWorksheet(name As String) As Worksheet
    Set GetWorksheet = ThisWorkbook.Sheets(name)
End Function

📌 Explanation:

• The GetWorksheet function returns an object of type Worksheet.
• The line Set GetWorksheet = ThisWorkbook.Sheets(name) assigns the reference of the sheet object with the name name in the current workbook (ThisWorkbook) to the function.
• Using Set for object assignment is mandatory; if assigned without Set, the code will encounter a runtime error.

Note: The same method is used for other objects such as Workbook, Range, or Collection; always use Set when returning an object.

Early Exit from a Function Procedure

If you need to end the function early, you can use the Exit Function statement:

Function SafeDivide(a As Double, b As Double) As Variant
    If b = 0 Then
        SafeDivide = "Division by zero is not allowed!"
        Exit Function
    End If
    SafeDivide = a / b
End Function

Error Handling in Function Procedures

To handle errors in a Function procedure in VBA, the On Error GoTo statement is typically used along with a label. This method allows you to control the function’s execution if an error occurs and display an appropriate message without the programme stopping.

For example, in the GetWorksheet function, if a sheet named name does not exist in the ThisWorkbook, runtime error 9 (Run-time error 9) (Subscript out of range) occurs.

We can handle this error as follows:

Function GetWorksheet(name As String) As Worksheet
    On Error GoTo errHandler   ' Enable error handling

    Set GetWorksheet = ThisWorkbook.Sheets(name)
    Exit Function             ' Successful exit from the function

errHandler:                     ' Error handling label
    If Err.Number = 9 Then
        MsgBox "The sheet '" & name & "' does not exist in this workbook."
    Else
        MsgBox "Error Number " & Err.Number & ": " & Err.Description
    End If
End Function

📌 Explanation:

• On Error GoTo errHandler: If an error occurs, function execution is transferred to the errHandler section.
• Exit Function: After the code executes successfully, we leave the function so the error handling section is not executed.
• In the errHandler section, you can manage specific errors or display a general message for other errors.

This method makes your function more reliable and free from sudden termination, and errors are handled in a controlled manner.

Defining Arguments or Parameters

Parameters are the named variables listed in the function’s definition. They act as placeholders for the values that will be passed into the function when it is called. Parameters define the type and number of inputs a function expects.

Arguments are the actual values or expressions passed to a function when it is invoked. These values are assigned to the corresponding parameters in the function’s definition, allowing the function to operate on specific data.

The complete structure for defining a parameter in VBA is as follows:

[ Optional ] [ ByVal | ByRef ] [ ParamArray ] varname [ ( ) ] [ As type ] [ = defaultvalue ]

Section	Explanation	Example
Optional	Optional parameter – can be omitted	Optional x As Integer
ByVal | ByRef	Passing method: ByVal (by value) or ByRef (by reference – default)	ByVal name As String
ParamArray	Variable parameter array – only for the last parameter	ParamArray args() As Variant
varname	Parameter variable name	username
[ ( ) ]	For array parameters	scores() As Integer
[ As type ]	Parameter data type	As String
[ = defaultvalue ]	Default value for optional parameters	= 0

Practical Examples of Parameters

Optional Parameters with Default Values:

Sub GreetUser(name As String, Optional title As String = "User")
    MsgBox "Hello " & title & " " & name
End Sub

' Call:
GreetUser "Ali" ' Without title
GreetUser "Ali", "Mr" ' With title

Difference Between ByVal and ByRef:

Sub TestByValByRef()
    Dim x As Integer, y As Integer
    x = 5: y = 5
    
    IncrementByVal x
    IncrementByRef y
    
    MsgBox "x (ByVal) = " & x & ", y (ByRef) = " & y ' x=5, y=6
End Sub

Sub IncrementByVal(ByVal num As Integer)
    num = num + 1
End Sub

Sub IncrementByRef(ByRef num As Integer)
    num = num + 1
End Sub

Using ParamArray for Variable Parameters:

Function SumAll(ParamArray numbers() As Variant) As Double
    Dim total As Double, i As Integer
    For i = LBound(numbers) To UBound(numbers)
        total = total + numbers(i)
    Next i
    SumAll = total
End Function

' Call:
Dim result As Double
result = SumAll(1, 2, 3, 4, 5) ' Result: 15

Property Procedure

Property procedures are typically used in classes and allow you to create readable and writable properties for objects. These procedures provide complete control over how private data within a class is accessed and set.

Types of Property Procedures

Property Type	Use	Structure
Property Get	Reading the value of a property	Property Get Name() As Type
Property Let	Setting the value for simple data types	Property Let Name(value As Type)
Property Set	Setting the value for object types	Property Set Name(obj As Object)

Practical Property Procedure Example

' In a class module named Employee
Private pName As String
Private pSalary As Double

' Property Get for reading the name
Property Get Name() As String
    Name = pName
End Property

' Property Let for setting the name
Property Let Name(value As String)
    If Len(value) > 0 Then
        pName = value
    Else
        Err.Raise 5, , "Name cannot be empty"
    End If
End Property

' Property Get for reading the salary
Property Get Salary() As Double
    Salary = pSalary
End Property

' Property Let for setting the salary
Property Let Salary(value As Double)
    If value >= 0 Then
        pSalary = value
    Else
        Err.Raise 5, , "Salary cannot be negative"
    End If
End Property

Using the Class in Code:

Sub TestEmployee()
    Dim emp As New Employee
    emp.Name = "Ali Rezaei" ' Calls Property Let
    emp.Salary = 5000000 ' Calls Property Let
    
    MsgBox "Name: " & emp.Name & vbCrLf & _
           "Salary: " & Format(emp.Salary, "#,##0") ' Calls Property Get
End Sub

Property Set for Objects

' In a class module named Department
Private pManager As Employee

Property Get Manager() As Employee
    Set Manager = pManager
End Property

Property Set Manager(emp As Employee)
    Set pManager = emp
End Property

Summary

In this tutorial, we familiarised ourselves with the concept of procedures in VBA and reviewed the differences between the three main types:

• Sub: A set of instructions that performs an action but does not return a value.
• Function: Similar to Sub but can return a calculated value.
• Property: Used to define custom properties for objects in classes and forms.

We also learned about the complete structure of parameters and how to use their different types. Understanding the differences between these three types of procedures and mastering how to define parameters will help you write more structured, maintainable, and flexible code in VBA.

Sources:

📘 Comprehensive VBA Guide in Microsoft Office
✳️ Tutorial on Writing a Sub Procedure in VBA
🔹 Tutorial on Writing a Function Procedure in VBA
🏷️ Tutorial on Writing a Property Procedure in VBA