传递对象时的byval vs byref

胜利者

我想检查是否存在具有特定名称的工作表,所以我在下面生成 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
蒂姆·威廉姆斯

http://www.cpearson.com/excel/byrefbyval.aspx解释ByRefvsByVal传递对象时。但是,如果您通过ThisWorkbookrwb(只要它被分配给某物)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

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章