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
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