传递对象时的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
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
http://wwwwww.cpearson.com/excel/excel/excel/byrefbyval.aspx > byref vs
byval传递对象时。但是，如果您通过thisworkbook或rwb（只要它分配给某物）有任何区别 - 无论哪种情况，wb内部shtexist都没有分配分配。您的声明可能是
rwb（作为变体，因为每个变量都需要类型；您不仅将类型添加到行中的最后一个）dim dim rwb作为工作簿，SWB作为工作簿，IWB作为Workbook在VBA中声明变量： https://learn.microsoft.com/en-us/office/office/vba/vba/langueage/concepts/getting-started/declaring-declaring-variables-variables#:~: ％20DECLARE％20个差异％20Variables％20in％20个％20statement。％20TO％20 specify％20A％20A％20DATA％20 type％20 type％2C％20you％20-20must％20must％20 include％20％20DATA％20DATA％20DATA％20 type％20 type％20 for％20 eht each％20vArable >。
http://www.cpearson.com/excel/byrefbyval.aspx explains
ByRefvsByValwhen passing objects. However if you passThisWorkbookorrwb(as long as it's assigned to something)ByVal/ByRefshouldn't make any difference - in either case there's no assignment towbinsideshtexistso 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 WorkbookDeclaring variables in 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.