Error Handling

[Kevin]

Hi

Is there an easy way to tell ms vb that if an action produces an error
simply to skip several lines and continue. I say because I expect errors in
certain instances (in this case where a Find function cannot find a value
then I just want to the macro to continue (but must skip a few lines on code
which become irrelevant)

 

[Jim Thomlinson]

The best way to deal with an error is to avoid it in the first place. When
doing a find there is no need to generate the error

Dim rngFound As Range

set rngfound = Sheets("Sheet1").Range("A1:A100").Find(What:="this", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry... Not Found"
Else
rngfound.select
'do your stuff
end if

 

[Kevin]

thanks - works

but what if for instance i want to go to a sheet and ensure it has no
filters (as I want to apply filters from scratch

eg below works only if a filter is applied in the first place
ActiveSheet.ShowAllData
otherwise i get an error

 

[Jim Thomlinson]

In that case you can use

on error resume next
activesheet.showalldata
on error goto 0 'resume normal error handler

The one issue I have with the way that the error handler is used is that it
is not there to cover up poor code. It is there it handle errors that are
either unanticipated or to avoid errors that we can anticipate but will
handle in a different way.

For example if you code needs to access a file from a network drive then
your error handler needs to cover what happens if the network is down. That
is an error that you can not predict ahead of time, but you have to plan on
it happening at some point in your life.

With your find example it is very possible that we will not find a match
which could cause an error. We can avoid the error with the set statement so
that is the best course of action.

If we wanted to know if a workbook was open then we could use
dim wbk as workbook
on error resume next
set wbk = workbooks("MyBook.xls")
on error goto 0
if wbk is nothing then set wbk = workbooks.open("C:\MyBook.xls")
if wbk is nothing then msgbox "Can't find file"

In your final example if the statement fails then we don't care becuase that
just means that there were no filters in place...

 

[Dave Peterson]

Activesheet.autofiltermode = false
will remove the arrows (and show all the data)

To show all the data, but keep the arrows.

with activesheet
If .FilterMode Then
.ShowAllData
End If
End if