VBA ByVal


Example

Passing by value

  • When a value is passed ByVal, the procedure receives a copy of the value.

    Public Sub Test()
        Dim foo As Long
        foo = 42
        DoSomething foo
        Debug.Print foo
    End Sub
    
    Private Sub DoSomething(ByVal foo As Long)
        foo = foo * 2
    End Sub
    

    Calling the above Test procedure outputs 42. DoSomething is given foo and receives a copy of the value. The copy is multiplied by 2, and then discarded when the procedure exits; the caller's copy was never altered.

  • When a reference is passed ByVal, the procedure receives a copy of the pointer.

    Public Sub Test()
        Dim foo As Collection
        Set foo = New Collection
        DoSomething foo
        Debug.Print foo.Count
    End Sub
    
    Private Sub DoSomething(ByVal foo As Collection)
        foo.Add 42
        Set foo = Nothing
    End Sub
    

    Calling the above Test procedure outputs 1. DoSomething is given foo and receives a copy of the pointer to the Collection object. Because the foo object variable in the Test scope points to the same object, adding an item in DoSomething adds the item to the same object. Because it's a copy of the pointer, setting its reference to Nothing does not affect the caller's own copy.