byval vs byref when passing object-CodePudding

I want to check if a worksheet with particular name is exist, so I generate shtexist function below. However, for the second parameter in shtexist. When I pass it byref at first, shtexist(name,thisworkbook) goes well while shtexist(name,rwb) does not and it shows byref error. Then I pass it byval, problem solved. My question is why byref/byval matters in this case?

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

CodePudding user response：

http://www.cpearson.com/excel/byrefbyval.aspx explains ByRef vs ByVal when passing objects. However if you pass ThisWorkbook or rwb (as long as it's assigned to something) ByVal/ByRef shouldn't make any difference - in either case there's no assignment to wb inside shtexist so there should be no side-effects either way.

The issue is likely with your declaration of rwb (as Variant, since every variable needs a type; you don't just add the type to the last one in the line)

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

Declaring variables in VBA: https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/declaring-variables#:~:text=You can declare several variables in one statement. To specify a data type, you must include the data type for each variable.