Consulting

Results 1 to 6 of 6

Thread: MsgBox used during On Error Resume Next ?

  1. #1
    VBAX Contributor
    Joined
    Jul 2009
    Posts
    157
    Location

    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.

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,766
    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]

  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,488
    Location
    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'

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,861
    Location
    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

  5. #5
    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]

  6. #6
    VBAX Contributor
    Joined
    Jul 2009
    Posts
    157
    Location
    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
  •