-
MsgBox used during On Error Resume Next ?
Hello all....I have simple code that is used by other users. I would like to have a custom MsgBox appear if an error occurs that would help the user to let me know where the error occured but then continue on without stopping the code.
I know this is not preferred in most cases but in my situation, not running the code causing the errors is better than stopping the entire process.
What I would like is code something like this but I am not sure if this will work and not sure how to test error code realtime. I also would like to be able to customize the msgbox to show a different message at different points in the code.
[VBA]
On Error Resume Next
MsgBox "Error when trying to _________"
'continue on with code at point error occurred
[/VBA]
So...what is the correct format for this example and can I repeat it with differing messages later in the code ?
Thanks in advance.
-
Using that structure, you would customize as you go
[VBA]'...some code
On Error Resume Next
'... potentially erroring code
If Err then MsgBox Error & " occured at the first point"
Err.Clear
'... more potentilly erroring code
If Err then MsgBox Error & " occured at the second code"
Err.Clear
'... more code
On Error Goto 0[/VBA]
-
Add a blank userform to your workbook. This should detail the errors without the need to click the messageboxes. You may need to fix sizes etc. to suit your display.
[VBA]Option Explicit
Dim spacing As Long
Sub DoTest()
On Error Resume Next
Test1:
Error 48
If Err Then ListErrors ("Test1 - " & Err.Description)
Err.Clear
Test2:
Error 49
If Err Then ListErrors ("Test2 - " & Err.Description)
Err.Clear
Test3:
Error 50
If Err Then ListErrors ("Test3 - " & Err.Description)
Err.Clear
End Sub
Sub ListErrors(errmsg As String)
Dim lbl As Control
If Not UserForm1.Visible Then
spacing = 0
With UserForm1
.Show False
.Top = 100
.Left = 100
End With
End If
spacing = spacing + 20
Set lbl = UserForm1.Controls.Add(bstrprogID:="Forms.Label.1", Name:="label" & spacing, Visible:=True)
With lbl
.Left = 20
.Top = spacing
.Width = 300
.Caption = "Error at " & errmsg
End With
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
I'd suggest using the Err Object and catching -- and optionally handling ---system errors, and you can create and .Raise your own error conditions, again handling them if you want
[vba]
Option Explicit
Sub TestErrors()
Dim x As Long, y As Long
Dim bError As Boolean
On Error GoTo ErrHandler
'force an error
bError = False
x = 0
y = 1 / x
If bError Then Err.Raise 10000
'select non-existant sheet
Worksheets("Sheet99999").Select
If bError Then Err.Raise 10000
'is today Friday
'you can create your own errors (I never have a problem with that)
If Application.WorksheetFunction.Weekday(Now) <> 6 Then
Call Err.Raise(20000, "Test Errors Program", "Today is not Friday")
If bError Then Err.Raise 10000
End If
Exit Sub
ErrHandler:
'good list of errors
'http://support.microsoft.com/kb/146864
'Error code Error message
'---------- -------------
'3 Return without GoSub
'5 Invalid procedure call
'6 Overflow
'7 Out of memory
'9 Subscript out of range
'11 Division by zero
'13 Type mismatch
bError = False
Select Case Err.Number
Case 11
If MsgBox(Err.Description & vbCrLf & vbCrLf & "Do you want to continue?", vbQuestion + vbYesNo, "Error") = vbYes Then
x = 1
Err.Clear
Resume ' = try the error causing statement again
Else
bError = True
Err.Clear
Resume Next ' = go to the next statment after the error causing statement
End If
Case 9
Call MsgBox(Err.Description & vbCrLf & vbCrLf & "No such worksheet", vbCritical + vbOKOnly, "Error")
Err.Clear
Resume Next
Case 10000
Call MsgBox("Really bad error and I'm going to quit", vbCritical + vbOKOnly, "Error")
Exit Sub
Case 20000
Call MsgBox(Err.Description & vbCrLf & vbCrLf & "Continuing anyway ... ", vbInformation + vbOKOnly, "Error")
Err.Clear
Resume Next
Case Else ' catch all Err
Call MsgBox(Err.Description & vbCrLf & vbCrLf & "There was an error, but I'm ignoring it", vbInformation + vbOKOnly, "Error")
Err.Clear
Resume Next
End Select
End Sub
[/vba]
Paul
-
Resume Next in the error handler will do the work.
[VBA]
Sub TestMethod()
10 On Error GoTo TestMethod_Error
Dim objClass As Object
20 Set objClass = CreateObject("TestClass.Class1")
30 MsgBox "Hello world"
40 On Error GoTo 0
50 Exit Sub
TestMethod_Error:
60 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure TestMethod of Module Module1 at" & Erl, vbCritical, MSG_TITLE
70 Resume Next
End Sub
[/VBA]
-
thanks guys....I appreciate it :-)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules