excel - ByVal 与 ByRef VBA

Question

我尝试尝试 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.

我尝试使用以下代码对其进行测试，但使用 ByRef 似乎无法使其正常工作将单元格的值更改为 0如果是 1
这是我正在测试的下面的代码，我做错了什么？ Range("A1") 的值仍然是 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 子例程不需要在参数列表周围加上大括号。但是，如果您传递一个参数并将其括在大括号中，则您传递的是一个表达式。表达式不能通过引用传递。他们被评估并且他们的结果被通过。因此，您必须删除调用 testingRoutine (trythis) 中的大括号。并写testingRoutine trythis注意:如果你调用一个函数而不使用它的返回值，它必须写成一个过程调用(在参数列表周围没有大括号)。举个例子:

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

当然，当一个函数或子函数有多个参数时，问题会立即清楚，因为逗号不能出现在表达式中。