Error Handling in Macros
 

You should always post your code but try inserting
on errror resume next
early in your code
--
Don Guillett
SalesAid Software

"Randy" wrote in message
...
I have a macro that was written long ago by someone no longer with the
company. The macro has workbook names hard coded in it that it opens from
a
share and reads one at a time. Everything runs great until the workbook
no
longer exists or the name has been changed. I'm a macro novice and am
wondering if there is a simple error routine that can be added which would
prompt me with the error and give me the option to continue on.
Currently,
it just errors out and I'm unable to generate critical reports until I
figure
out what's wrong.

Thanks in advance!

Randy

Error Handling in Macros
 

"Don Guillett" wrote in message
You should always post your code but try inserting
on errror resume next
early in your code

That is perhaps the worst possible solution for the user's problem. A far
better solution would be first to download MZTools (www.mztools.com) or a
similar add-in that will automatically add line numbers to code. Then use On
Error Goto <label to display the error. E.g.,

Sub AAA()
Dim WS As Worksheet
On Error GoTo ErrH:
10 Set WS = Worksheets("AAA")
20 Debug.Print "Error line skipped"
30 Exit Sub
ErrH:
40 If MsgBox("Error on line: " & Erl & vbCrLf & _
"Error: " & CStr(Err.Number) & ": " & Err.Description & vbCrLf & _
"Do you want to continue?", vbYesNo) = vbYes Then
50 Resume Next ' optional. do you really want to
60 End If
End Sub

This will tell you exactly where the error occurred and you can change the
code to prompt for a sheet name or workbook name or whatever is necessary to
remedy the problem. You have the option of continuing the code execution if
desired.


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





"Don Guillett" wrote in message
...

You should always post your code but try inserting
on errror resume next
early in your code
--
Don Guillett
SalesAid Software

"Randy" wrote in message
...
I have a macro that was written long ago by someone no longer with the
company. The macro has workbook names hard coded in it that it opens
from a
share and reads one at a time. Everything runs great until the workbook
no
longer exists or the name has been changed. I'm a macro novice and am
wondering if there is a simple error routine that can be added which
would
prompt me with the error and give me the option to continue on.
Currently,
it just errors out and I'm unable to generate critical reports until I
figure
out what's wrong.

Thanks in advance!

Randy

Error Handling in Macros
 

I would try something like

For i = LBound(projectfiles) To UBound(projectfiles)
On Error Resume Next
Err.Clear
Workbooks.Open P & projectfiles(i)
If Err.Number = 0 Then
Windows(projectfiles(i)).Activate
Else
MsgBox "Error on index: " & CStr(i) & vbCrlf & _
"Err: " & CStr(Err.Number) & " " & Err.Description
End If
On Error Goto 0


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

"Randy" wrote in message
...
Chip,

I have not been able to modify your example to work the way I need it to.
Here is the portion of my macro that I need the error routine for:

' IMPORT PROJECT STATUS REPORTS

' Loop through "projectfiles" array to insert new project status
reports.
For i = LBound(projectfiles) To UBound(projectfiles)

' First open the project status report file.
Workbooks.Open P & projectfiles(i)
Windows(projectfiles(i)).Activate

When the macro cannot find the status report named in "projectfiles", I
need
a message box to display stating there is a problem with the file it is
trying to open (list the file name) and then do I want to skip this file
and
continue. I can send my whole macro if that would help.

Thanks again for the help.

Randy


"Chip Pearson" wrote:

"Don Guillett" wrote in message
You should always post your code but try inserting
on errror resume next
early in your code

That is perhaps the worst possible solution for the user's problem. A
far
better solution would be first to download MZTools (www.mztools.com) or
a
similar add-in that will automatically add line numbers to code. Then use
On
Error Goto <label to display the error. E.g.,

Sub AAA()
Dim WS As Worksheet
On Error GoTo ErrH:
10 Set WS = Worksheets("AAA")
20 Debug.Print "Error line skipped"
30 Exit Sub
ErrH:
40 If MsgBox("Error on line: " & Erl & vbCrLf & _
"Error: " & CStr(Err.Number) & ": " & Err.Description & vbCrLf &
_
"Do you want to continue?", vbYesNo) = vbYes Then
50 Resume Next ' optional. do you really want to
60 End If
End Sub

This will tell you exactly where the error occurred and you can change
the
code to prompt for a sheet name or workbook name or whatever is necessary
to
remedy the problem. You have the option of continuing the code execution
if
desired.


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





"Don Guillett" wrote in message
...

You should always post your code but try inserting
on errror resume next
early in your code
--
Don Guillett
SalesAid Software

"Randy" wrote in message
...
I have a macro that was written long ago by someone no longer with the
company. The macro has workbook names hard coded in it that it opens
from a
share and reads one at a time. Everything runs great until the
workbook
no
longer exists or the name has been changed. I'm a macro novice and am
wondering if there is a simple error routine that can be added which
would
prompt me with the error and give me the option to continue on.
Currently,
it just errors out and I'm unable to generate critical reports until I
figure
out what's wrong.

Thanks in advance!

Randy