ByRef (By Reference) and ByVal (By Value) are two methods of passing arguments (commonly referred to as ‘passing mechanisms’) to procedures in Visual Basic and VBA, each with distinct behaviors and implications for how variables are handled within functions or subroutines.

Let’s take a moment to examine them to better understand there purposes and pitfalls.
 

ByRef (By Reference)

Firstly, it should be noted that ByRef is the default argument ‘passing mechanism’, so if nothing is specified in the argument declarations of a procedure then VBA considers it to be ByRef.

When an argument is passed ByRef, a pointer to the memory location of that argument is passed to the procedure. This means that any changes made to the argument inside the procedure will affect the original variable in the calling code.

In plain English, a ByRef argument is basically passed as itself, so any changes that occur in the called procedure will be returned to the calling procedure.

Example 1 (ByRef):

Sub ModifyValueByRef(ByRef iSomeNumber As Integer)
    iSomeNumber = iSomeNumber * 2
End Sub

Sub MainByRef()
    Dim iNumber               As Integer

    iNumber = 5
    Debug.Print iNumber    ' Output: 5
    ModifyValueByRef iNumber
    Debug.Print iNumber    ' Output: 10 -> reflects the x * 2 from ModifyValueByRef()
    ModifyValueByRef (iNumber)
    Debug.Print iNumber  ' Output: 10 (not 20!) -> We'll discuss why later, spoiler it's because of the ()
End Sub

In this example, the original value of iNumber is modified because it’s passed ByRef to the ModifyValueByRef() and it is multiplied within it, thus that change is reflected back in the calling procedure.
 

ByVal (By Value)

When an argument is passed ByVal, only the value of the argument is sent to the procedure. The original variable remains unchanged, even if the parameter is modified within the procedure.

Example 2 (ByVal):

Sub ModifyValueByVal(ByVal iSomeNumber As Integer)
    iSomeNumber = iSomeNumber * 2
End Sub

Sub MainByVal()
    Dim iNumber               As Integer

    iNumber = 5
    Debug.Print iNumber    ' Output: 5
    ModifyValueByVal iNumber
    Debug.Print iNumber    ' Output: 5
    ModifyValueByVal (iNumber)
    Debug.Print iNumber  ' Output: 5
End Sub

Here, the original value of iNumber remains unchanged regardless of what occurs within the called procedures.
 

Key Differences

• Variable Mutability / Protection
  ByRef allows the procedure to modify the original variable, while ByVal protects it from changes.
• Performance
  ByRef generally offers better performance, especially with large data structures, as it avoids copying data. However, with today’s computing power, the difference is typically negligible.
• Output Parameters
  ByRef permits a procedure to actually return multiple values rather than just a single one as seen typically with Functions.

Example 3 (Combination ByRef & ByVal):

Sub ModifyDateByVal(ByVal d As Date)
    d = DateSerial(2025, 1, 1)  ' This doesn't affect the original
End Sub

Sub ModifyDateByRef(ByRef d As Date)
    d = DateSerial(2025, 2, 2)  ' This also doesn't affect the original
End Sub

Sub Main_ModifyDate()
    Dim myDate As Date
    
    myDate = #3/5/2025#
    Debug.Print myDate  ' Output: 3/5/2025
    
    ModifyDateByVal myDate
    Debug.Print myDate  ' Output: 3/5/2025, because it is ByVal
    
    ModifyDateByRef myDate
    Debug.Print myDate  ' Output: 2/2/2025, because it is ByRef
End Sub

Special Cases

Array argument must be ByRef (https://learn.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/array-argument-must-be-byref)

User-defined type may not be passed ByVal (https://learn.microsoft.com/en-us/office/vba/language/how-to/user-defined-type-may-not-be-passed-byval)

There may be more.

Forcing ByVal Behavior, Even In ByRef Defined Cases

You can force ByVal behavior even when a procedure expects ByRef by using parentheses:

Sub SetNewString(ByRef inString As String)
    inString = "New value"
End Sub

Sub Main()
    Dim str As String

    str = "Original"
    Debug.Print str
    SetNewString (str) ' Passed as ByVal because of () -> forces ByVal, str remains "Original"
    Debug.Print str
    SetNewString str   ' Passed ByRef, str becomes "New value"
    Debug.Print str
End Sub

This technique allows you to override the default ByRef passing mechanism and protects the original variable from unintended modifications.

The same principle applies when using the Call Statement, the normal way of using Call would be:

    Call SetNewString(str) 'Remains ByRef

but to force ByVal we need to, as shown above, add an extra set of surrounding parenthesis around the input argument, resulting in:

    Call SetNewString((str)) 'Is now ByVal

Best Practices

Here are a few tips when it comes to using ByRef vs. ByVal in your VBA code:

• You should always make an effort to explicitly declare ByRef or ByVal for clarity.
• Use ByRef for better performance especially with large data structures or when you need to modify the original variable.
  BUT, be cautious with ByRef to avoid unintended side effects in your code.
• Use ByVal when you don’t intend to modify the original variable.

In Summary

ByVal makes a copy of the original variable so any manipulations are never reflected back.  The original value remains intact.

ByRef manipulates the actual variable and thus allows altering values through called procedures.

Understanding the differences between ByRef and ByVal is crucial for writing efficient and bug-free code in VBA and Visual Basic. Choose the appropriate method based on your specific needs and always consider the implications for variable scope and mutability in your procedures.
 

Further Reading(s) On The Subject

https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/passing-arguments-efficiently
 

Page History