传递对象时 byval vs byref

Question

我想检查具有特定名称的工作表是否存在，因此我在下面生成shtexist函数。然而，对于shexists中的第二个参数。当我首先通过它时，shtexist(name,thisworkbook)运行良好，而shtexist(name,rwb)没有，它显示byref错误。然后我通过它，问题就解决了。我的问题是为什么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解释了当传递对象时ByRefvsByVal。然而，如果你传递ThisWorkbook或rwb(只要它被分配给某些东西)ByVal/ByRef不应该有任何区别-在任何情况下，shtexist内部都没有分配给wb，所以无论哪种方式都应该没有副作用。

问题可能与您的rwb声明有关(作为Variant，因为每个变量都需要一个类型;你不需要把类型加到最后一行

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.