excel - 传递对象时的byval vs byref

Question

我想检查是否存在具有特定名称的工作表，所以我在下面生成 shtexist 函数。
但是，对于 shtexist 中的第二个参数。当我首先通过 byref 时， shtexist(name,thisworkbook) 运行良好，而 shtexist(name,rwb) 没有，它显示 byref 错误。
然后我通过它byval，问题解决了。
我的问题是为什么在这种情况下 byref/byval 很重要？

Sub update_Click()
Dim updatelist
Dim relname, salname, insname, sname As String
Dim rwb, swb, iwb As Workbook
Dim year, month As Integer
updatelist = ThisWorkbook.Sheets("FrontPage").Range("u2", Range("u2").End(xlDown))
relname = Dir(ThisWorkbook.Path & "/" & "*关系表*.xls?")
Set rwb = Workbooks.Open(ThisWorkbook.Path & "/" & relname)
MsgBox (VarType(ThisWorkbook))
For Each i In updatelist
    sname = CStr(i)
    year = CInt(Left(sname, InStr(sname, ".") - 1))
    month = CInt(Mid(sname, InStr(sname, ".") + 1, 2))
    MsgBox (year & " " & month)
    If shtexist(sname, rwb) Then
        MsgBox ("yes")
    Else
        MsgBox ("no")
    End If
Next

End Sub

Function shtexist(name As String, Optional ByVal wb As Workbook) As Boolean
Dim sht As Worksheet
If wb Is Nothing Then
    Set wb = ThisWorkbook
End If
On Error Resume Next
    Set sht = wb.Sheets(name)
On Error GoTo 0
If sht Is Nothing Then
    shtexist = False
Else
    shtexist = True
End If
End Function

Answer 1

http://www.cpearson.com/excel/byrefbyval.aspx解释 ByRef对比 ByVal传递物体时。但是，如果您通过 ThisWorkbook或 rwb (只要它被分配给某物)ByVal/ByRef不应该有任何区别 - 在任何一种情况下都没有分配给 wb里面 shtexist所以无论哪种方式都应该没有副作用。
问题可能与您声明的 rwb 相关。 (作为 Variant，因为每个变量都需要一个类型；您不只是将类型添加到该行的最后一个)Dim rwb As Workbook, swb As Workbook, iwb As Workbook在 VBA 中声明变量:https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/declaring-variables#:~:text=You%20can%20declare%20several%20variables%20in%20one%20statement.%20To%20specify%20a%20data%20type%2C%20you%20must%20include%20the%20data%20type%20for%20each%20variable .