CODEWITHSUNDEEP
excelvba
Maldred
Maldred

Reputation: 1104

ByVal vs ByRef VBA

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

Upvotes: 6

Views: 19459

Answers (4)

Paul Ogilvie
Paul Ogilvie

Reputation: 25286

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.

Upvotes: 12

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1654

i can't remember where i read this, but i know that if you add the bracets () to arguments when calling a sub/function, it is a specific (not an error) way microsoft implented to workaround byref calls (to byval).

example : lets say you call a sub who has ONLY byref, and 2 arguments:

call Sub1 (a), (b)

is a proper way to force Byvalarguments, even if they were initially coded as Byval

Upvotes: 0

n8.
n8.

Reputation: 1738

I would do it this way.

Sub test1()

    Dim trythis As Boolean

    trythis = False

    If (Sheets("TESTING").Range("A1").value = 1) Then
        tr = testingRoutine(trythis)
        If tr 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"
    testingRoutine = True

End Function

Because trythis is not a global variable, changing it in the function will do nothing in the sub. It will just define trythis as True in the function and stay in that scope. To get trythis to be affected by the function and read by the sub you have to assign it to a variable within the sub.

Upvotes: 1

braX
braX

Reputation: 11755

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

Upvotes: 3

Related Questions