Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Help with VBA Error Handling

  1. #1
    Board Regular jo15765's Avatar
    Join Date
    Sep 2011
    Posts
    566
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with VBA Error Handling

    I have never done any error handling, I would always just let the code crash, and pick up the pieces after the fact. Well this time I am going to try to add error handling, and what my thoughts are is to have the code say on error jump to the next and then show a log at the end of what failed. For example..
    Code:
    Workbooks.Open Filename:="C:\JJones\Daily\Daily.xls"
    With Sheets(Array("Today", "Yesterday")).Select
    And of course some of the array's are more complex and that is just a snippet. I was thinking something along the lines of if Today doesn't exist or a problem opening that, then proceed to Yesterday and then show me a log at the end of what the error(s) are.

    Is that a feasible mindset or am I way out in left field? Also, how difficult would that be to code?

  2. #2
    Board Regular bertie's Avatar
    Join Date
    Jun 2009
    Location
    Coatbridge, Scotland
    Posts
    1,869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with VBA Error Handling

    First of all set up workbook and worksheet variables.

    When opening a file you need to cater for the file or directory not existing. The simplest way to do this is to use an "On Error Resume Next" statement.

    Code:
    '=============================================
      'open a file, cater for the file not existing
      '=============================================
      On Error Resume Next
        Set wb = Workbooks.Open("C:\JJones\Daily\Daily.xls")
        
        'check if the workbook exists
        If wb Is Nothing Then
          '
          'log code goes here
          '
          MsgBox "File does not exist!", vbCritical
          Exit Sub
        End If
    If the workbook is sucesfully opened we need to reset Error handling to report as normal.

    Code:
      'the file exists so report errors as normal
      On Error GoTo 0
    I am not quite sure what you are trying to do with your array, but you can use the worksheet variable to loop through the sheets in the opened file. Here is some sample code.

    Code:
      '==================
      'handle worksheets
      '==================
      For Each ws In wb.Worksheets
        
        'sample code to print a list of sheet names in the opened workbook
        msg = msg & ws.Name & vbCrLf
      Next ws
    
      'ouput sheets names
      MsgBox "Worksheets Found:" & vbCrLf & msg
    Here is all the above code in a test procedure for you to play about with.
    Code:
    Sub test()
      Dim wb As Workbook
      Dim ws As Worksheet
      Dim msg As String
      
      '=============================================
      'open a file, cater for the file not existing
      '=============================================
      On Error Resume Next
        Set wb = Workbooks.Open("C:\JJones\Daily\Daily.xls")
        
        'check if the workbook exists
        If wb Is Nothing Then
          '
          'log code goes here
          '
          MsgBox "File does not exist!", vbCritical
          Exit Sub
        End If
      
      
      'the file exists so report errors as normal
      On Error GoTo 0
      
      
      
      '==================
      'handle worksheets
      '==================
      For Each ws In wb.Worksheets
        
        'sample code to print a list of sheet names in the opened workbook
        msg = msg & ws.Name & vbCrLf
      Next ws
    
      'ouput sheets names
      MsgBox "Worksheets Found:" & vbCrLf & msg
      
    End Sub
    Hope this helps,
    Bertie
    MrExcel - Add in - Excel To Html - copy snapshots of your spreadsheet to posts.
    http://www.mrexcel.com/forum/board-a...ml#post2545970

    MrExcel - Add in - VBA to HTML - copy your code to posts
    http://www.mrexcel.com/vbaddin.shtml

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,009
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Help with VBA Error Handling

    If one of the errors you are trying to deal with is if the file exists you can check that by using Dir.

    Code:
    strFileName = "C:\JJones\Daily\Daily.xls"
    If Len(Dir(strFileName)) = 0 Then
         Msgbox "File not found"
          ' code to add log entry
    Else
       ' open workbook
       Set wb = Workbooks.Open(strFileName)
      
       ' code to check for worksheets in workbook
     
    End If
    If posting code please use code tags.

  4. #4
    Board Regular
    Join Date
    Sep 2009
    Posts
    194
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with VBA Error Handling

    If you're looking for a general trap to isolate an error, you can always try this:

    I've found it very useful as of late to debug my routines.

    Code:
    On Error GoTo ErrorCatch:
     
    'Code
    '
    '
     
    ErrorCatch:
        MsgBox "Error: " & Err.Description
    Hope that helps!

  5. #5
    Board Regular jo15765's Avatar
    Join Date
    Sep 2011
    Posts
    566
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with VBA Error Handling

    What I am looking for is the "if worksheet/workbook exists" I am running code that will open anywhere from between 15 - 20 different workbooks, run some refresh on the queries and then save them. Ideally what I want to happen is if either 1) I wrote the code wrong, or 2) if the workbook/worksheet doesn't exist that instead of the whole module crashing, the missing workbook name would be logged and the code would continue to execute.

    I have alarms turned off when the procedure is running, is there a way to "hold" the alert that the worksheet doesn't exist until the procedure has completely finished running, or even better, write it to a error log .txt file that shows what worksheets (or pieces of code) were skipped?

  6. #6
    MrExcel MVP
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    6,154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with VBA Error Handling

    Quote Originally Posted by jo15765 View Post
    Ideally what I want to happen is if either 1) I wrote the code wrong, or 2) if the workbook/worksheet doesn't exist...
    Hi there,

    I am afraid those are two quite different things to cater for. Now I'm not sure exactly what you mean by 'write the code wrong', but no error handling is going to handle compile errors effectively. As Bertie shows, you can handle a file not existing, or you can check to see if the file exists as Norie shows.

    As to having the code wait to report missing workbooks until the end, you could build a string and add onto it ea time a file is missing - then just report what files were missing at the end. Is that the sort of thing you are hoping to do?

  7. #7
    Board Regular jo15765's Avatar
    Join Date
    Sep 2011
    Posts
    566
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with VBA Error Handling

    Yes, the string you are referencing is the type of idea I was looking for.

    I didn't realize that my 1) and 2) you would have to handle completely different. In my mind an error is an error, but I guess it makes sense when I Think about it.

  8. #8
    MrExcel MVP
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    6,154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with VBA Error Handling

    Quote Originally Posted by jo15765 View Post
    ...I am running code that will open anywhere from between 15 - 20 different workbooks, run some refresh on the queries and then save them...

    I have alarms turned off when the procedure is running,...
    I take it that the workbooks are in different folders and/or we are not opening all workbooks in a given folder? I would suggest showing us the current code for opening/shutting ea workbook.

    I would comment that if you are actually using On Error Resume Next for the majority of the code, I would be confident that this can be improved. In short, ...Resume Next masks errors and makes it harder to track down where things are falling apart.

  9. #9
    Board Regular
    Join Date
    Jan 2010
    Location
    UK, Shropshire, Shrewsbury
    Posts
    469
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with VBA Error Handling

    Error1: The code does not compile becuase you have a syntax error - found in development
    Error2: There is an environmental situation you are aware of and have coded around (e.g. file not found, database server being offline, user not having correct access rights etc) - should be part of your testing and sign-off
    Error3: there is an environmental situation you are not aware of that causes the application to crash (e.g. a find statement fails because the source value is not in the target range) these can be trapped with an on error goto Error_hander: and at least the application does not stop running
    Error4: The code runs to completion but does not give the expected results - good luck!!!!

    Two things missing from VBA: Try-Catch type error checking where you can divert if the code fails and the ability to handle the error and return to the part of the code that caused the issue (or even tell you which line it was)

  10. #10
    Board Regular jo15765's Avatar
    Join Date
    Sep 2011
    Posts
    566
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with VBA Error Handling

    GTO ---

    Here is a snippet of my current code. I probably could have used an array, but each time I attempted to write the code as an array, it would throw numerous debug errors, so I did it like this...
    Code:
    Dim wb As Workbook
    Set wb = Workbooks.Open(Filename:="C:\Richard\Daily\Friday\Friday_Reports.xls"
    With ActiveWorkbook
    .SaveAs Filename:="C:\Master_Reports\Friday\" & ActiveWorkbook.Name & "_" & VBA.Format(Date, "mmddyyyy") & ".xls"
    Run "PrintToPDF_MultiSheetToOne_Early"
    .Close& ".xls"
    End With
    Set wb = Workbooks.Open(Filename:="C:\Mark\Daily\Friday\Friday_Reports.xls")
    Run "RefreshOnOpen"
    With ActiveWorkbook
    .SaveAs Filename:="C:\Master_Reports\Friday\" & ActiveWorkbook.Name & VBA.Format(Date, "mmddyyyy") & ".xls"
    Run "PrintToPDF_MultiSheetToOne_Early"
    .Close& ".xls"
    End With
    And I currently don't have error handling in my procedure but am definitely interested in what suggestions can be offered from this site.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •