我需要将工作表作为ByRef或ByVal传递吗? [英] Do I need to pass a worksheet as ByRef or ByVal?

问题描述

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

我没有为工作表分配任何新值，但是我正在对该工作表的分页符设置进行更改.我需要将其作为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

解决方案

任何一种都可以，但是对于语义上正确的代码，更喜欢按值(ByVal)传递它.

按值传递对象变量时，就是将指针的副本传递给对象.

所以该过程在上起作用是相同的对象(即，调用者将看到更改后的属性值 )，但不允许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

另一方面...

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

通常，参数应按值传递.不幸的是，ByRef是默认语言(VB.NET已修复该问题).

非对象变量也是如此:

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

然后...

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

如果变量是通过引用传递的，但从未在过程主体中重新分配，则可以按值传递.

如果变量通过引用传递，并在过程主体中重新分配，则该过程可能写为Function，而实际上返回修改后的值. /p>

如果变量按值传递，并在过程主体中重新分配，则调用者将看不到更改-这使代码变得可疑；如果某个过程需要重新分配一个ByVal参数值，则它定义自己的局部变量并分配 that 而不是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

---

这些都是 Rubberduck 中的所有实际代码检查，因为我经常参与VBE插件，可以分析您的代码并查看以下内容:

参数按值传递，但分配了新的值/参考.如果不应该让调用者知道新值，请考虑制作本地副本.如果调用方应看到新值，则应改为通过ByRef传递参数，这样您就会遇到错误.

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

一个过程只有一个通过引用传递的参数，该参数在过程退出之前被分配了一个新值/引用，该过程使用ByRef参数作为返回值:请考虑使其成为一个函数.

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

通过引用传递但未分配新值/引用的参数可以改为通过值传递.

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

这里是wsReport的情况:

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?

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

解决方案

Either will work, but for semantically correct code, prefer passing it by value (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).

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.

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:

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:

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}

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}

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:

这篇关于我需要将工作表作为ByRef或ByVal传递吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！