Error Handling

R

rsphorler

Always have problems with the error handlers in my macros.

The macro below opens a file then does some other stuff to it but the
error handler does not seem to work and i still get an error if the
file is missing (error 1004 to be precise)

'opens the adjustments file
filepath = ThisWorkbook.Path & "\adjustments.xls"
On Error GoTo nofile1 'specifc error
handler
Workbooks.Open Filename:=filepath 'cause of potential error
On Error GoTo 0 'reset default
error handler
GoTo contadj2

'missing adjustments.xls error handler
nofile1:
On Error GoTo 0
adjust = MsgBox("The Adjustments file is missing, Do you wish to
continue without incorporating any adjustments?", vbYesNo)
If adjust = 6 Then
GoTo contadj2
Else
GoTo abortend
End If

contadj2:
REST OF MACRO

abortend:
end sub

Any ideas why it does not work, or many of the other handlers which
are similar in nature?

Thanks in advance

Richard
 
B

Barb Reinhardt

How about doing something like this

Dim oWB as workbook

Set oWB = nothing
on error resume next
Set oWB = Workbooks.Open Filename:=filepath
if not oWB is nothing then
'Put the code here you want to run if the workbook opens successfully.
end if

HTH,
Barb Reinhardt
 
C

Chip Pearson

When dealing with error handling, you must understand that VBA code operates
in two "modes". "Regular" mode is the normal mode. When an error occurs in
"regular" mode and any error handling other than On Error Resume Next or On
Error Goto 0 is in effect, VBA starts operating in "error" mode. In this
mode, no other error handling can take place. On Error statements will NOT
handle errors when VBA is already in "error" mode. You MUST revert to
"regular" mode after an error occurs by calling either the Resume or Resume
Next statement or exiting the procedure. Anything else will keep the code
running in "error mode" and no further error handling can take place.

See http://www.cpearson.com/excel/ErrorHandling.htm for a detail discussion
of this.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)
 
D

Dave Peterson

I like this way, too.

Just a typo:
Set oWB = Workbooks.Open Filename:=filepath
should be
Set oWB = Workbooks.Open(Filename:=filepath)
(with ()'s)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top