Prev: how can i append data to a sheet or book from another one
Next: Build an Array of Different Values From Column
From: Phil Hibbs on 26 Jan 2010 04:52
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.
From: joel on 26 Jan 2010 06:45

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

with sheets("Sheet1")


Usually you either havve a typo error or you are looking in the wrong
workbook if you have multiple workbooks. Again you could have the same
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 of
the merge group. So if your code tries to get any of the other merged
cells you could get this error.

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

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

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

set MySelection = selection


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=173406

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

From: Phil Hibbs on 26 Jan 2010 07:24
joel wrote:
> 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

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.
From: joel on 26 Jan 2010 07:39

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.CHM


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=173406

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

From: Phil Hibbs on 26 Jan 2010 08:14
joel wrote:
> the error methos has a number of properties

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

Phil Hibbs.
 |  Next  |  Last
Pages: 1 2
Prev: how can i append data to a sheet or book from another one
Next: Build an Array of Different Values From Column