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

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

1 个解决方案

#1

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 copy of the pointer to the object.

按值传递对象变量时,您将指针的副本传递给对象。

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

所以该过程使用的是相同的对象(即调用者将看到更改的属性值),除非不允许将指针设置为其他东西 - 它可以,但它会在自己的副本上执行此操作所以来电者不会受到影响。

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 ByRef is 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 return the modified value instead.

如果变量通过引用传递,并在过程的主体中重新分配,那么该过程很可能被写为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 ByVal parameter value, the intent of the code becomes clearer if it defines its own local variable and assigns that instead of the ByVal parameter:

如果一个变量按值传递,并在一个过程的主体中重新分配,那么调用者就不会看到这些变化 - 这会使代码变得可疑;如果某个过程需要重新分配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.

参数按值传递,但会分配新值/引用。如果调用者不应该知道新值,请考虑制作本地副本。如果调用者应该看到新值,则应该通过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.

在过程退出之前,只有一个参数通过引用传递的过程被赋予一个新的值/引用,它使用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}

That's the case of wsReport here:

这是wsReport的情况:

#1