vba 我是否需要将工作表作为 ByRef 或 ByVal 传递？

Do I need to pass a worksheet as ByRef or ByVal?

提问by Rdster

I broke out some code from a larger block and need to pass a worksheet to it...

我从一个更大的块中取出了一些代码，需要将一个工作表传递给它......

I'm not assigning any new value to the worksheet, but I am making changes to the Page Break settings for that sheet. Do I need to pass it as ByRef, or is ByVal good enough?

我没有为工作表分配任何新值，但我正在更改该工作表的分页符设置。我需要将它作为 ByRef 传递，还是 ByVal 足够好？

Private Sub SetPageBreaks(ByRef wsReport As Worksheet)
Dim ZoomNum As Integer

  wsReport.Activate
  ActiveWindow.View = xlPageBreakPreview
  ActiveSheet.ResetAllPageBreaks
  ZoomNum = 85
  With ActiveSheet
    Select Case wsReport.Name
      Case "Compare"
        Set .VPageBreaks(1).Location = Range("AI1")
        ZoomNum = 70
      Case "GM"
        .VPageBreaks.Add before:=Range("X1")
      Case "Drift"
        .VPageBreaks.Add before:=Range("T1")
      Case Else
        .VPageBreaks.Add before:=Range("U1")
    End Select
  End With
  ActiveWindow.View = xlNormalView
  ActiveWindow.Zoom = ZoomNum

End Sub

回答by Mathieu Guindon

Either will work, but for semantically correct code, prefer passing it by value (ByVal).

两者都可以工作，但对于语义正确的代码，更喜欢通过值 ( ByVal)传递它。

When you pass an object variable by value, you're passing a copyof the pointerto the object.

当您按值传递对象变量时，您传递的是指向该对象的指针的副本。

So what the procedure is working with isthe same object (i.e. changed property values willbe seen by the caller), except it's not allowed to Setthe pointer to something else - well it can, but it'll do that on its own copyand so the caller won't be affected.

那么，什么程序正在与是同一个对象（即更改的属性值将调用者可以看到），但它不能够给Set指针到别的-以及它可以，但它会做到这一点对自己的副本和所以调用者不会受到影响。

Public Sub DoSomething()
    Dim target As Worksheet
    Set target = ActiveSheet
    Debug.Print ObjPtr(target)
    DoSomethingElse target
    Debug.Print ObjPtr(target)
End Sub

Private Sub DoSomethingElse(ByVal target As Worksheet)
    Debug.Print ObjPtr(target)
    Set target = Worksheets("Sheet12")
    Debug.Print ObjPtr(target)
    'in DoSomething, target still refers to the ActiveSheet
End Sub

On the other hand...

另一方面...

Public Sub DoSomething()
    Dim target As Worksheet
    Set target = ActiveSheet
    Debug.Print ObjPtr(target)
    DoSomethingElse target
    Debug.Print ObjPtr(target)
End Sub

Private Sub DoSomethingElse(ByRef target As Worksheet)
    Debug.Print ObjPtr(target)
    Set target = Worksheets("Sheet12")
    Debug.Print ObjPtr(target)
    'in DoSomething, target now refers to Worksheets("Sheet12")
End Sub

In general, parameters should be passed by value. It's just an unfortunate language quirk that ByRefis the default (VB.NET fixed that).

一般来说，参数应该按值传递。这只是一个不幸的语言怪癖，这ByRef是默认的（VB.NET 修复了这个问题）。

The same is true for non-object variables:

对于非对象变量也是如此：

Public Sub DoSomething()
    Dim foo As Long
    foo = 42
    DoSomethingElse foo
End Sub

Private Sub DoSomethingElse(ByVal foo As Long)
    foo = 12
    'in DoSomething, foo is still 42
End Sub

And...

和...

Public Sub DoSomething()
    Dim foo As Long
    foo = 42
    DoSomethingElse foo
End Sub

Private Sub DoSomethingElse(ByRef foo As Long)
    foo = 12
    'in DoSomething, foo is now 12
End Sub

If a variable is passed by reference, but is never reassigned in the body of a procedure, then it can be passed by value.

如果变量通过引用传递，但从未在过程体中重新赋值，则它可以通过值传递。

If a variable is passed by reference, and reassigns it in the body of a procedure, then that procedure could likely be written as a Function, and actually returnthe modified value instead.

如果一个变量是通过引用传递的，并在过程体中重新分配它，那么该过程可能会被写成 a Function，而实际上返回的是修改后的值。

If a variable is passed by value, and is reassigned in the body of a procedure, then the caller isn't going to see the changes - which makes code suspicious; if a procedure needs to reassign a ByValparameter value, the intent of the code becomes clearer if it defines its own local variable and assigns thatinstead of the ByValparameter:

如果一个变量是按值传递的，并在过程体中重新分配，那么调用者将不会看到变化——这使代码变得可疑；如果一个过程需要重新分配一个ByVal参数值，如果它定义自己的局部变量并分配它而不是ByVal参数，则代码的意图变得更加清晰：

Public Sub DoSomething()
    Dim foo As Long
    foo = 42
    DoSomethingElse foo
End Sub

Private Sub DoSomethingElse(ByVal foo As Long)
    Dim bar As Long
    bar = foo
    '...
    bar = 12
    '...
End Sub

---

---

These are all actual code inspections in Rubberduck, as VBE add-in I'm heavily involved with, that can analyze your code and see these things:

这些都是在Rubberduck 中的实际代码检查，作为我大量参与的 VBE 插件，可以分析您的代码并查看以下内容：

Parameter is passed by value, but is assigned a new value/reference. Consider making a local copy instead if the caller isn't supposed to know the new value. If the caller should see the new value, the parameter should be passed ByRef instead, and you have a bug.

_{http://rubberduckvba.com/Inspections/Details/AssignedByValParameterInspection}

参数按值传递，但被分配了一个新值/引用。如果调用者不应该知道新值，请考虑制作本地副本。如果调用者应该看到新值，则应改为通过 ByRef 传递参数，并且您有一个错误。

_{http://rubberduckvba.com/Inspections/Details/AssignedByValParameterInspection}

A procedure that only has one parameter passed by reference that is assigned a new value/reference before the procedure exits, is using a ByRef parameter as a return value: consider making it a function instead.

_{http://rubberduckvba.com/Inspections/Details/ProcedureCanBeWrittenAsFunctionInspection}

一个过程只有一个通过引用传递的参数，并在过程退出之前分配了一个新值/引用，使用 ByRef 参数作为返回值：考虑将其改为函数。

_{http://rubberduckvba.com/Inspections/Details/ProcedureCanBeWrittenAsFunctionInspection}

A parameter that is passed by reference and isn't assigned a new value/reference, could be passed by value instead.

_{http://rubberduckvba.com/Inspections/Details/ParameterCanBeByValInspection}

通过引用传递且未分配新值/引用的参数可以通过值传递。

_{http://rubberduckvba.com/Inspections/Details/ParameterCanBeByValInspection}

That's the case of wsReporthere:

这是wsReport这里的情况：