[RESOLVED] "On Error" not catching all errors
I have an Excel macro where I am selecting a worksheet in a workbook, and if the worksheet does not exist, it is supposed to go to the next one. I am doing this by using the On Error statement.
I have this statement in the begining of the sub.
Code:
On Error GoTo OverHere
Then I have the following further down in the code.
It seems to only work for the first error, but doesn't catch any after that. The code stops running and a debug message box pops up.
Does anyone know why it is only catching the first instance of the error?
Re: "On Error" not catching all errors
It's because you aren't exiting the error handler properly - you need to have some kind of Resume.
Show us your code, and we'll explain how to do it for your situation.
Re: "On Error" not catching all errors
Here is my code....
Code:
Sub DistribAnalysis()
Dim FsSearch As Office.FileSearch
Dim vaFileName As Variant
Dim xBook As String
Dim NewBook As String
Dim xSheet As String
Dim xRow As Integer
Dim oRow As Integer
Dim iRow As Integer
Dim xTemp(7) As String
Dim xProp As String
Dim xCol As Integer
NewBook = Workbooks.Add.Name
Columns("A:A").Select
Selection.NumberFormat = "@"
Rows("9:9").Select
Selection.NumberFormat = "@"
Workbooks.Open ThisWorkbook.Path + "/Indemnification.xls", False, True
Application.DisplayAlerts = False
Set FsSearch = Application.FileSearch
xRow = 12
xCol = 3
With FsSearch
.NewSearch
.LookIn = ThisWorkbook.Path + "\odl input\"
.Filename = "*.xls"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
.LastModified = msoLastModifiedAnyTime
.Execute 'Count of how many records are found
For Each vaFileName In .FoundFiles
Workbooks.Open vaFileName, False, True
xBook = ActiveWorkbook.Name
oRow = 12
Do While Cells(oRow, 1) <> ""
xTemp(0) = Cells(oRow, 1)
xTemp(1) = Cells(oRow, 2)
xTemp(2) = Cells(oRow, 5)
Workbooks("indemnification.xls").Activate
On Error GoTo OverHere
Sheets(xTemp(1)).Select
iRow = 9
Do While Cells(iRow, 1) <> ""
xTemp(3) = Cells(iRow, 1)
xTemp(4) = Cells(iRow, 2)
'indemnification percentage
xTemp(5) = Cells(iRow, Left(Sheet1.cmbMonth, 2) + 3)
'xTemp(6) = Cells(iRow, Left(Sheet1.cmbMonth, 2) + 3)
Workbooks(NewBook).Activate
xRow = 12
Do While Cells(xRow, 1) <> ""
If Cells(xRow, 1) = xTemp(3) Then
Exit Do
End If
If Cells(xRow, 1) > xTemp(3) Then
Rows(xRow).Insert xlShiftDown
Exit Do
End If
xRow = xRow + 1
Loop
Cells(9, xCol) = xTemp(1)
Cells(10, xCol) = xTemp(0)
Cells(8, xCol) = xTemp(2)
Cells(xRow, 1) = xTemp(3)
Cells(xRow, 2) = xTemp(4)
Cells(xRow, xCol) = "= " + Format(xTemp(5)) + " * R[-" + Format(xRow - 8) + "]C"
Workbooks("indemnification.xls").Activate
iRow = iRow + 1
Loop
xCol = xCol + 1
OverHere:
Workbooks(xBook).Activate
oRow = oRow + 1
Loop
Workbooks(xBook).Close False
Next vaFileName
End With
End Sub
Re: "On Error" not catching all errors
Ah.. you haven't actually got an error handler at all, and to be honest there isn't a good reason for using one for what you are doing - you should just check if there is a sheet with that name, eg:
Code:
Workbooks("indemnification.xls").Activate
Dim intCount As Integer, booFound As Boolean
booFound = False
For intCount = 1 To Workbooks("indemnification.xls").Sheets.Count
If Sheets(intCount).Name = xTemp(1) Then
booFound = True
Exit For
End If
Next intCount
If booFound Then
Sheets(xTemp(1)).Select
iRow = 9
...
Loop
xCol = xCol + 1
End If
Workbooks(xBook).Activate
Error handling is a good idea (especially for dealing with unexpected errors), but it needs to be used appropriately. For an explanation & examples, see the article about it in our Classic VB FAQs (link in my signature).
Re: "On Error" not catching all errors
I checked your link, and I still don't understand why it only works for the first error and not the rest of them. Can you briefly explain?
The workbook I am checking has over 100 tabs in it and would have be checked over 100 times. It wouldn't be very efficient to check the whole workbook every time. I tried to find a way to see if the tab exists in the workbook without checking the whole book every time. This was the most effiecient way I could think of.
Re: "On Error" not catching all errors
It because you are not using an error handler - you are just using "On Error" to jump to another part of the code, which is not appropriate.
As soon as an error occurs, error handling stops, and it does not start again until you exit the error handler properly. An error handler needs to somehow deal with the error (eg: by showing a message, or by doing some kind of "correction"), and then return to the code - which is the part that turns error handling back on.
If you look at the examples in the FAQ, all of them "leave" the code to go to a separate error handler, which does something and then returns to the code - you cannot simply jump to another part of your routine.
You may think that using my suggestion isn't very efficient, but it isn't much less efficient that using an error handler (which is "bad practice", and makes your code harder to read & maintain).
As the file "indemnification.xls" is open throughout the whole routine, you you could make the checking more efficient by storing the names of all sheets to a string (separated by a delimiter) before the loop starts, and then using Instr to check if the name you are looking for (with the delimiter on either side) exists in the string.
Re: "On Error" not catching all errors
Ah.....I get it now... Thank you.
Thanks for the good idea. I will save all the tab names in a string and search the string to see if the tab exits. Thanks!
