Handling different Error 1004 errors

P

Phil Hibbs

If I'm performing actions through an Excel.Application object, the
only error code I get is 1004, which appears to be a generic ODBC
error code. How should I check for more detail on the kind of error
that occurred? All I appear to have is the Err.Description, which I
can check for certain key phrases such as "The macro 'whatever' cannot
be found", or "Check the spelling of the file name", but is there any
other way?

Phil Hibbs.
 
J

joel

You are trying to access an object that doesn't exist. for example i
you are looking for sheet 1 and your workbook doesn't have a sheet 1

with sheets("Sheet1")


Usually you either havve a typo error or you are looking in the wron
workbook if you have multiple workbooks. Again you could have the sam
error occuring on a Range object and you have the wrong worksheet open.
If you are using merged cells you can only get the upper left cell o
the merge group. So if your code tries to get any of the other merge
cells you could get this error.

I alway avoid using the select cell method becauwse this is prone t
this type error. To debug this error I often add all the variable to
watch window then check the the following objects

1) Name - Use this for workbook and sheets to make sure you are o
the correct sheet
2) Range objects - Check Row and column
3) Parent.Name - Use this on worksheets to make surre you are i
the correct workbook

If you are using a selection. The set an object to the select propert
and then add the watch to the new variable and then treat the selectio
like a range object.

set MySelection = selectio
 
P

Phil Hibbs

joel said:
You are trying to access an object that doesn't exist.  for example if
you are looking for sheet 1 and your workbook doesn't have a sheet 1
Click to expand...

I'm not asking for help with a specific error - there are a whole
spectrum of problems that all trigger Error 1004, and I'm after
general advice on how to identify if a specific one occurs. For
example, in one instance I am calling a macro called FormatSheet in
another workbook, and I know that if that workbook does not have that
macro, it will generate a 1004 error with a Message of "The macro
'FormatSheet' cannot be found." In the event of this error, I want to
just Resume Next, because I don't care that the workbook does not
implement the macro.

My question is, is there another way to detect (for example) a "macro
not found" error other than matching on the Description? That
Description might be very different if, for example, the spreadsheet
is opened on a French copy of Windows and Office.

Phil Hibbs.
 
J

joel

the error methos has a number of properties

Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.Helpfile, Err.HelpContext


It turns out the help file is a file that can be veiwed menually

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\1033\VBLR6.CH
 
P

Phil Hibbs

joel said:
the error methos has a number of properties
Click to expand...

....and the Description property is the only one that contains more
info about the nature of the error.

Phil Hibbs.
 
T

Tom Ogilvy

Phil,

the short answer is no.

but in your own example, you know when you get the error at that line, that
the file was not found. So you can anticipate for each particular line of
code what error could occur and you adjust your error handling at each point
to respond accordingly.

Perhaps not what you wanted to hear, but it is pretty much common practice
to proceed the way I have described. I don't think I have ever seen code
where the error description string is parsed to determine the nature of the
error.
 
Top