Announcement

Collapse
No announcement yet.

Error Handling: Error 1004 -- Excel seems to ignores 'On Error Goto ErrHandler"

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Error Handling: Error 1004 -- Excel seems to ignores 'On Error Goto ErrHandler"



    I am using this bit of code provided--by Aaron Blood, was it--on these forums. I have used error handling successfully in the past, but this time I can't get Excel to cooperate.

    If this code breaks--when a printer connection doesn't exist--it will produce a debug error alert rather than executing the error handling code.

    Code:
     
    Option Explicit
    Sub GeneratePrintOut()
        Dim msg As String
        Dim sm  As Worksheet
     
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
        End With
     
        Set sm = ThisWorkbook.Sheets("Summary Matrix")
     
        On Error GoTo ErrHand
        With sm.PageSetup
            '.LeftHeader = ""
            '.CenterHeader = ""
            '.RightHeader = ""
            '.LeftFooter = ""
            '.CenterFooter = ""
            '.RightFooter = ""
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .TopMargin = Application.InchesToPoints(0.25)
            .BottomMargin = Application.InchesToPoints(0.25)
            .HeaderMargin = Application.InchesToPoints(0.25)
            .FooterMargin = Application.InchesToPoints(0.25)
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            .PrintQuality = 1200
            '.CenterHorizontally = False
            '.CenterVertically = False
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaperLegal
            .FirstPageNumber = xlAutomatic
            .Order = xlDownThenOver
            .BlackAndWhite = False
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            '.PrintErrors = xlPrintErrorsDisplayed
        End With
        With sm
            .PageSetup.PrintArea = .Range("B1:W77").Address
     
            .Range("A4:A75").EntireRow.Hidden = False
            .Range("A38:A75").EntireRow.Hidden = True
            .Range("N77").Value = "Printed on " & WeekdayName(Weekday(Date)) & ", " & Format(Date, "dd-mmm-yyyy") & " at " & Time & "   |   Page 1"
            .PrintOut Copies:=1
     
            .Range("A4:A75").EntireRow.Hidden = False
            .Range("A4:A37").EntireRow.Hidden = True
            .Range("N77").Value = "Printed on " & WeekdayName(Weekday(Date)) & ", " & Format(Date, "dd-mmm-yyyy") & " at " & Time & "   |   Page 2"
     
            On Error GoTo ErrHand
            .PrintOut Copies:=1
    Continue:
            .Range("A4:A75").EntireRow.Hidden = False
            .Range("N77").Value = ""
        End With
     
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = xlCalculationAutomatic
        End With
    Exit Sub
     
    'Following code developed by Aaron Blood
    ErrHand:
        Select Case Err.Number
            Case 1004
                msg = "Error: " & Err.Number & vbCrLf & Err.Description
                MsgBox msg, vbOKOnly, "Unit Data Tool | Trapped Error"
                Err.Clear
                GoTo Continue '??? vs. line below
                Resume Next
            Case 400, 350
                msg = "Error: " & Err.Number & vbCrLf & Err.Description
                MsgBox msg, vbOKOnly, "Unit Data Tool | Trapped Error"
                Err.Clear
                Resume Next
            Case Else
                msg = "Error: " & Err.Number & vbCrLf & Err.Description
                MsgBox msg, vbOKOnly, "Unit Data Tool | Untrapped Error"
                Err.Clear
                Stop
                Resume
        End Select
    End Sub
    Your expedient replies would be most appreciated!

    V/r,
    Mac

  • #2
    Re: Error Handling: Error 1004 -- Excel seems to ignores 'On Error Goto ErrHandler"

    Might the error not be one of those listed? Try removing the On Error line and see what error is being generated.

    Comment


    • #3
      Re: Error Handling: Error 1004 -- Excel seems to ignores 'On Error Goto ErrHandler"

      Good point--so I verified. It is indeed "Error 1004". If the On Error Goto code would work, even if it were not 1004 it would run the following and I would be able to distinctly see the error and its description:

      Code:
      Case Else
      msg = "Error: " & Err.Number & vbCrLf & Err.Description
      MsgBox msg, vbOKOnly, "Unit Data Tool | Untrapped Error"
      Err.Clear
      Stop
      Resume

      Comment


      • #4
        Re: Error Handling: Error 1004 -- Excel seems to ignores 'On Error Goto ErrHandler"

        Its odd that the handler isn't being triggered. What line throws the error?

        Comment


        • #5
          Re: Error Handling: Error 1004 -- Excel seems to ignores 'On Error Goto ErrHandler"

          Code:
                  On Error GoTo ErrHand
                  .PrintOut Copies:=1

          Comment


          • #6
            Re: Error Handling: Error 1004 -- Excel seems to ignores 'On Error Goto ErrHandler"

            Remove that second on error statement, you already have already used it at the top.
            Reafidy

            Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

            Comment


            • #7
              Re: Error Handling: Error 1004 -- Excel seems to ignores 'On Error Goto ErrHandler"

              Question: If the "On Error" code resides at the top of the subroutine, will it effect the entire subroutine, or just a single subsequent line of code?

              Comment


              • #8
                Re: Error Handling: Error 1004 -- Excel seems to ignores 'On Error Goto ErrHandler"

                It will affect the entire sub routine, unless you use cancel it with:
                Code:
                 On Error Goto 0
                Reafidy

                Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                Comment


                • #9
                  Re: Error Handling: Error 1004 -- Excel seems to ignores 'On Error Goto ErrHandler"

                  I see. Thank you. I was under the impression that an 'On Error' command only affected the subsequent line of code. Appreciate the help.

                  Follow-up question: Would it be considered appropriate coding practice to use a method such as this?
                  Code:
                  Sub ...()
                  Vars...
                  On Error Goto ErrHandler
                   
                  ...code...
                  someotherplaceincode
                  ...more code...
                   
                  ErrHandler:
                  select
                  case 1004
                  ...code...
                  goto someotherplaceincode
                  case xxx
                  ...
                  else
                  ...
                  end select
                  end sub

                  Comment


                  • #10
                    Re: Error Handling: Error 1004 -- Excel seems to ignores 'On Error Goto ErrHandler"

                    No. Use ...
                    Code:
                       Resume SomeOtherPlace
                    If you use Goto the error condition will not be cleared by VBA.

                    Comment


                    • #11
                      Re: Error Handling: Error 1004 -- Excel seems to ignores 'On Error Goto ErrHandler"

                      Ahh so If I were to use the following modified code, would this be the logical sequence of execution?
                      1. Run the error handler code instead of Line 101, which produces the error;
                      2. Run 'Line 102', and proceed through the rest of the subroutine.

                      Code:
                      Sub ...()
                      ...define vars...
                      On Error Goto ErrHandler
                       
                      ...code...
                      line 101: code produces error
                      line 102: some line of code
                      ...more code...
                      Exit Sub '<-- added
                       
                      ErrHandler:
                      Select
                      Case 1004
                      ...code...
                      Resume Next
                      Case xxx
                      ...
                      Resume Next
                      Else
                      ...
                      Resume Next
                      End Select
                      End Sub
                      I was definitely not using proper error handling techniques prior to this. Much appreciated!

                      Comment


                      • #12
                        Re: Error Handling: Error 1004 -- Excel seems to ignores 'On Error Goto ErrHandler&quot;

                        You're still not using correct error handling techniques.

                        Error handlers, by definition, handle errors. To 'run' the error handler, you (or the code) needs to raise an error. If you jump to an error handler *without* an error being raised, the Resume statement will raise an error....!

                        I'm thoroughly confused about what you're trying to do, but you need to code around the possibilities of errors, and handle them with the error handler if something slips through...

                        Comment


                        • #13
                          Re: Error Handling: Error 1004 -- Excel seems to ignores 'On Error Goto ErrHandler&quot;

                          Have an example, link or advice?

                          Situation is: there's a line of code may create an error (print command).

                          Comment


                          • #14
                            Re: Error Handling: Error 1004 -- Excel seems to ignores 'On Error Goto ErrHandler&quot;

                            I am using this programming practice as defined below; please, explain if you would how my sample code differs. I am not understanding the difference.

                            http://www.cpearson.com/excel/errorhandling.htm
                            Code:
                            On Error Goto ErrHandler:
                            N = 1 / 0    ' cause an error
                            '
                            ' more code
                            '
                            Exit Sub 
                            ErrHandler:
                            ' error handling code
                            Resume Next
                            End Sub
                            http://www.xlpert.com/partG.htm
                            Code:
                            Sub ErrorExample_4()
                            On Error GoTo errorHandler
                            Dim total As Integer
                            Dim intA As Integer
                            Dim intB As Integer
                            Dim intC As Integer
                            intA = Range("A1").Value
                            intB = Range("A2").Value
                            intC = Range("A3").Value
                            total = intA + intB + intC
                            MsgBox ("Total is " & total)
                            Exit Sub
                            errorHandler:
                            Select Case Err.Number
                            Case 13
                            MsgBox ("Make sure an integer is entered")
                            Case Else
                            Resume Next
                            End Select
                            End Sub
                            End Sub

                            Comment


                            • #15


                              Re: Error Handling: Error 1004 -- Excel seems to ignores 'On Error Goto ErrHandler&quot;

                              Similar to the one posted by Aaron Blood on XL-Logic, even ref'd in other forums:

                              http://www.mrexcel.com/forum/showthread.php?t=52848
                              Code:
                              Private Sub Workbook_Open()
                              Dim x As Long
                               
                              On Error GoTo ErrorHandler
                               
                              One:
                              Open "c:\Counter.txt" For Input As #1
                              Input #1, x
                              Close #1
                              x = x + 1
                               
                              Two:
                              Sheets(1).Range("A1").Value = x
                              Open "c:\Counter.txt" For Output As #1
                              Write #1, x
                              Close #1
                               
                              Exit Sub
                              ErrorHandler:
                                  Select Case Err.Number
                                      Case 53  'If Counter file does not exist...
                                          x = InputBox("Enter Number to Begin Counting With", "Create 'Counter.txt' File")
                                          Resume Two
                                      Case Else
                                          Resume Next
                                  End Select
                              End Sub

                              Comment

                              Working...
                              X