5.3 Passing Arguments by Value and by Reference.

In VBA, when you pass arguments to procedures (Subroutines or Functions), you can pass them in two ways: By Value and By Reference. The way arguments are passed determines whether the procedure can modify the original data outside the procedure. Understanding the difference between these two passing mechanisms is crucial for controlling how data is manipulated within your procedures.

1. Passing Arguments by Value

When you pass an argument by value, you are passing a copy of the argument's value to the procedure. The procedure works with the copy, and any changes made to the argument inside the procedure do not affect the original variable outside the procedure.

Syntax to Pass By Value:

By default, arguments are passed by value in VBA unless specified otherwise. The ByVal keyword is used explicitly to indicate that an argument should be passed by value.

Sub ProcedureName(ByVal argName As DataType)
    ' Code using argName
End Sub

Example of Passing By Value:

Sub IncreaseValue(ByVal x As Integer)
    x = x + 10
End Sub

Sub Test()
    Dim num As Integer
    num = 5
    IncreaseValue(num)  ' num is passed by value
    MsgBox num  ' The value of num remains 5, as the original variable was not changed.
End Sub

In this example, num is passed to the IncreaseValue subroutine by value. Even though x is incremented inside the procedure, the value of num in the Test procedure remains unchanged, because only a copy of num was passed.

2. Passing Arguments by Reference

When you pass an argument by reference, you are passing the actual memory address of the variable. This means that any changes made to the argument inside the procedure will directly modify the original variable outside the procedure. This is useful when you want a procedure to alter the value of the argument passed to it.

Syntax to Pass By Reference:

To pass an argument by reference, you use the ByRef keyword. This tells VBA to pass the argument by reference (using the actual memory address).

Sub ProcedureName(ByRef argName As DataType)
    ' Code using argName
End Sub

Example of Passing By Reference:

Sub IncreaseValue(ByRef x As Integer)
    x = x + 10
End Sub

Sub Test()
    Dim num As Integer
    num = 5
    IncreaseValue(num)  ' num is passed by reference
    MsgBox num  ' The value of num will be 15 because the original variable was changed.
End Sub

In this example, num is passed to the IncreaseValue subroutine by reference. Because x refers to the same memory address as num, any changes made to x will directly affect num. Therefore, when the IncreaseValue subroutine adds 10 to x, the value of num becomes 15.

3. Key Differences Between ByVal and ByRef

4. When to Use ByVal and ByRef

Use ByVal when:

• You want to protect the original value of the variable from being modified.
• The procedure only needs to read the value, not change it.
• You are passing primitive types like Integer, String, or Double, and don't need to modify the original value.

Use ByRef when:

• You want the procedure to modify the original variable.
• You want to pass large data structures like arrays or objects and modify them directly.
• You want to improve performance when working with large datasets because ByRef avoids copying large objects.

5. Example of Both ByVal and ByRef in One Procedure

It’s possible to use both ByVal and ByRef in a single procedure. Here’s an example that demonstrates both:

Sub UpdateValues(ByVal x As Integer, ByRef y As Integer)
    x = x + 5  ' x is passed by value, so it won't change outside the procedure
    y = y + 10  ' y is passed by reference, so it will change outside the procedure
End Sub

Sub Test()
    Dim a As Integer
    Dim b As Integer
    a = 5
    b = 10

    UpdateValues(a, b)

    MsgBox "a = " & a  ' a will still be 5 (ByVal)
    MsgBox "b = " & b  ' b will be 20 (ByRef)
End Sub

In this case:

• a is passed by value, so the change inside the UpdateValues subroutine does not affect the original value of a.
• b is passed by reference, so the change inside the UpdateValues subroutine directly affects the original value of b.

6. Summary

• ByVal (Pass by Value): A copy of the argument is passed to the procedure. Changes made inside the procedure do not affect the original variable.
• ByRef (Pass by Reference): The address of the argument is passed to the procedure. Changes made inside the procedure affect the original variable.

Understanding when to use ByVal and ByRef helps you control how data is passed to and from procedures, ensuring your code behaves as expected.