ByVal vs ByRef VBA

Question

I've tried to attempt something that was answered by JaredPar ByRef vs ByVal Clarification

ByVal in VB.NET means that a copy of the provided value will be sent to the function. For value types (Integer, Single, etc.) this will provide a shallow copy of the value. With larger types this can be inefficient. For reference types though (String, class instances) a copy of the reference is passed. Because a copy is passed in mutations to the parameter via = it won't be visible to the calling function.

ByRef in VB.NET means that a reference to the original value will be sent to the function (1). It's almost like the original value is being directly used within the function. Operations like = will affect the original value and be immediately visible in the calling function.

And I've tried to test it with the following code and I can't seem to get it to work use the ByRef to change the value of the cell to 0 if it's 1

Here's my below code that I'm testing with, what am I doing wrong? The value of Range("A1") is still 1

Sub test1()

    Dim trythis As Boolean

    trythis = False

    If (Sheets("TESTING").Range("A1").value = 1) Then
        testingRoutine (trythis)
        If (trythis) Then
            Debug.Print "Value changed to 0"
            Sheets("TESTING").Range("A1").value = 0
        End If
    End If

End Sub

Private Function testingRoutine(ByRef trythis As Boolean)

    Debug.Print "Ran Function"
    trythis = True

End Function

Answer 1

VB subroutines don't require braces around the argument list. However, if you pass a single argument and you enclose that in braces, you are passing an expression . Expressions cannot be passed by reference. They are evaluated and their result is passed. Therefore you must remove the braces in the call testingRoutine (trythis) and write testingRoutine trythis

Note: if you call a function without using its return value, it must be written as a procedure call (without braces around the argument list). As an example:

myVal = myFunction (trythis)   ' trythis will be passed by reference
myFunction (trythis)           ' trythis will be seen as an expression
myFunction trythis             ' trythis will be passed by reference

myVal = mySub (trythis)        ' invalid: mySub is not a function
mySub (trythis)                ' trythis will be seen as an expression
mySub trythis                  ' trythis will be passed by reference

Of course, the problem will be clear immediately when a function or sub has more than one parameter because a comma cannot appear in an expression.

Answer 2

Change this:

testingRoutine (trythis)

to this:

testingRoutine trythis

then try it.

Also, look what happens if I change it to this, where the function is being used as a function (returning something)

Sub test1()

    Dim trythis As Boolean
    Dim testit As Boolean

    trythis = False

    If (Sheets("Sheet1").Range("A1").Value = 1) Then
        testit = testingRoutine(trythis)
        If (trythis) Then
            Debug.Print "Value changed to 0"
            Sheets("TESTING").Range("A1").Value = 0
        End If
    End If

End Sub

Private Function testingRoutine(ByRef trythis As Boolean) As Boolean

    Debug.Print "Ran Function"
    trythis = True

End Function