与 VBA 中的消息框一起出现错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25739736/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
On error along with message box in VBA
提问by user2786962
In one of my code if I get an error i need to pop the file name with error and then resume next.Below is the code snippet i am trying to use but it is giving me error. Can anyone help me
在我的一个代码中，如果出现错误，我需要弹出带有错误的文件名，然后继续下一步。下面是我尝试使用的代码片段，但它给了我错误。谁能帮我
sourcefilename = File_list.Cells(i + 1, 1)
Set Baccha_Wbk = Wbk.Workbooks.Open(sourcefilename)
On Error GoTo ErrMsg
ErrMsg: MsgBox ("Error in file" & sourcefilename ),On Error Resume Next
回答by Siddharth Rout
Another way to do it...
另一种方法来做到这一点...
Sub Sample()
Dim sFile As String
'~~> If you are doing this in Excel. then you don't need Wbk
Dim Wbk As Excel.Application
Dim Baccha_Wbk As Workbook
Dim i As Long
On Error GoTo Whoa
'
'~~> Rest of the Code
'
sFile = File_list.Cells(i + 1, 1)
If FileFolderExists(sFile) Then
'~~> If you are doing this in Excel. then you don't need Wbk
Set Baccha_Wbk = Wbk.Workbooks.Open(sFile)
Else
MsgBox "File Doesn't exists"
End If
Exit Sub
Whoa:
MsgBox Err.Description
End Sub
Public Function FileFolderExists(strFullPath As String) As Boolean
On Error GoTo EarlyExit
If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
EarlyExit:
On Error GoTo 0
End Function
回答by Jean-Fran?ois Corbett
You're almost there. This is how you could do it:
您快到了。你可以这样做：
sourcefilename = File_list.Cells(i + 1, 1)
On Error Resume Next 'Errors get swallowed without warning. Use sparingly.
Set Baccha_Wbk = Wbk.Workbooks.Open(sourcefilename)
If Err.Number <> 0 Then MsgBox ("Error in file" & sourcefilename )
On Error Goto 0 'Back to normal: errors get thrown as usual
A more complete error handler could look like this:
更完整的错误处理程序可能如下所示：
Sub abcd()
On Error GoTo ErrorHandler
'[code...]
sourcefilename = File_list.Cells(i + 1, 1)
Set Baccha_Wbk = Wbk.Workbooks.Open(sourcefilename)
'[more code...]
ExitProcedure:
On Error Resume Next
'Clean-up code goes here
Exit Sub
ErrorHandler:
Select Case Err.Number
'Deal with each of the expected errors
Case 53 ' File not found
MsgBox "File not found: " & sourcefilename
Case 70 ' Permission denied
MsgBox "Permission denied. Maybe you don't have permission to access this drive? " & sourcefilename
'etc. etc.
'Deal with unexpected errors
Case Else
UnexpectedError Err.Number, Err.Source & ", Procedure abcd of Module Module1", Err.Description, Err.HelpFile, Err.HelpContext
End Select
Resume ExitProcedure
Resume
End Sub
(Why the double Resumeat the end?)
along with this helper sub — customize as you like.
连同这个辅助子 - 随心所欲地定制。
Public Sub UnexpectedError(ByVal lngNumber As Long, _
ByVal strSource As String, ByVal strDescription As String, _
ByVal strHelpfile As String, ByVal lngHelpContext As Long)
On Error Resume Next
MsgBox "[" & strSource & "]" & vbCrLf & "Run-time error '" _
& CStr(lngNumber) & "':" _
& vbCrLf & vbCrLf & strDescription, vbExclamation, Application.Name, _
strHelpfile, lngHelpContext
Application.EnableEvents = True
Debug.Print "Case " & CStr(lngNumber) & " '" & strDescription
'Debug.Assert False 'uncomment while developing
End Sub
