传递对象时的byval与byref

Question

我想检查是否存在具有特定名称的工作表，因此我在下面生成shtexist函数。但是，对于shtexist中的第二个参数。当我第一次传递它时，shtexist(名称，此工作簿)进行得很好，而shtexist(name，rwb)则没有，它显示了byref错误。然后我把它传过去，问题解决了。我的问题是，为什么在这件事上有什么关系？

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就不会有任何区别--在这两种情况下，shtexist中都没有分配给wb的任务，因此应该没有副作用--不管是哪种方式。

这个问题可能与您的rwb声明有关(因为每个变量都需要一个类型；您不只是将该类型添加到行中的最后一个变量中)。

Dim rwb As Workbook, swb As Workbook, iwb As Workbook

在VBA：https://learn.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中声明变量。