Help with VBA Error Handling

jo15765

Well-known Member
Joined
Sep 23, 2011
Messages
591
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?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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:
[COLOR=green]'=============================================[/COLOR]
  [COLOR=green]'open a file, cater for the file not existing[/COLOR]
  [COLOR=green]'=============================================[/COLOR]
  [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open("C:\JJones\Daily\Daily.xls")
    
    [COLOR=green]'check if the workbook exists[/COLOR]
    [COLOR=darkblue]If[/COLOR] wb [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
      [COLOR=green]'[/COLOR]
      'log code goes here
      [COLOR=green]'[/COLOR]
      MsgBox "File does not exist!", vbCritical
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
If the workbook is sucesfully opened we need to reset Error handling to report as normal.

Code:
  [COLOR=green]'the file exists so report errors as normal[/COLOR]
  [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 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:
  [COLOR=green]'==================[/COLOR]
  [COLOR=green]'handle worksheets[/COLOR]
  [COLOR=green]'==================[/COLOR]
  [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] wb.Worksheets
    
    [COLOR=green]'sample code to print a list of sheet names in the opened workbook[/COLOR]
    msg = msg & ws.Name & vbCrLf
  [COLOR=darkblue]Next[/COLOR] ws

  [COLOR=green]'ouput sheets names[/COLOR]
  MsgBox "Worksheets Found:" & vbCrLf & msg
Here is all the above code in a test procedure for you to play about with.
Code:
[COLOR=darkblue]Sub[/COLOR] test()
  [COLOR=darkblue]Dim[/COLOR] wb [COLOR=darkblue]As[/COLOR] Workbook
  [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
  [COLOR=darkblue]Dim[/COLOR] msg [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
  
  [COLOR=green]'=============================================[/COLOR]
  [COLOR=green]'open a file, cater for the file not existing[/COLOR]
  [COLOR=green]'=============================================[/COLOR]
  [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open("C:\JJones\Daily\Daily.xls")
    
    [COLOR=green]'check if the workbook exists[/COLOR]
    [COLOR=darkblue]If[/COLOR] wb [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
      [COLOR=green]'[/COLOR]
      'log code goes here
      [COLOR=green]'[/COLOR]
      MsgBox "File does not exist!", vbCritical
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
  
  
  [COLOR=green]'the file exists so report errors as normal[/COLOR]
  [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
  
  
  
  [COLOR=green]'==================[/COLOR]
  [COLOR=green]'handle worksheets[/COLOR]
  [COLOR=green]'==================[/COLOR]
  [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] wb.Worksheets
    
    [COLOR=green]'sample code to print a list of sheet names in the opened workbook[/COLOR]
    msg = msg & ws.Name & vbCrLf
  [COLOR=darkblue]Next[/COLOR] ws

  [COLOR=green]'ouput sheets names[/COLOR]
  MsgBox "Worksheets Found:" & vbCrLf & msg
  
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps,
Bertie
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
...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.
 
Upvote 0
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!!!!:LOL:

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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top