excel - 为什么子程序的第一个参数必须通过 ByVal

Question

我有以下代码:

Sub AddValidation()
   Dim ws, wsDefinitions As Worksheet
   Set ws = ThisWorkbook.Worksheets("TData")
   Set wsDefinitions = ThisWorkbook.Worksheets("Definitions")

   Call AddValidator(ws, wsDefinitions, "FaultType", FaultTypeColumn)
End Sub

和

Sub AddValidator(targetWs As Worksheet, definitionsWs As Worksheet, definitionTableName As String, targetColumnNumber)

    Dim definitionsRange As Range, targetRange As Range

    Set definitionsRange = definitionsWs.ListObjects(definitionTableName).ListColumns(1).DataBodyRange
    Set targetRange = targetWs.ListObjects("Table1").ListColumns(targetColumnNumber).DataBodyRange

    With targetRange.Validation
        .Delete 'delete previous validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Formula1:="='" & definitionsWs.Name & "'!" & definitionsRange.Address
    End With

End Sub

在编译期间，我得到 AddValidator 的第一个参数 - “ws”的“ByRef 参数类型不匹配”。
当我将 ByVal 添加到定义中时:

Sub AddValidator(ByVal targetWs As Worksheet, definitionsWs As Worksheet, definitionTableName As String, targetColumnNumber)

一切都编译得很好。 “AddValidator”宏的两个第一个参数是工作表类型，但只有第一个参数需要通过值传递。

有人能告诉我为什么吗？

Answer 1

问题在于变量的声明。

Dim ws, wsDefinitions As Worksheet

这里 ws被声明为 variant而不是 sheet对象，因此 Excel 会提示您更新参数。如果您解决此问题，Excel 将不会引发错误。以下子将正常工作。

Sub Test()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    mySub ws1, ws2
End Sub
Sub mySub(targetWS As Worksheet, sourceWS As Worksheet)
    MsgBox targetWS.Name
    MsgBox sourceWS.Name
End Sub