ByVal vs ByRef VBA
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.
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