I have a custom function that uses arguments from the user's worksheet to
find data in an external Access database using ADO, then return it to the
relevant cell. It works fine, but I want to be able to ensure that the custom
function leaves the cell value *untouched* (rather than returning an error
value) if the VBA cannot make the connection to the Access database (e.g. if
the user can't access the database for some reason).

I've tried using Application.Caller to get the cell's original value, but
this doesn't seem to work. Can anyone help?

Thanks,

SB

You will need to put error testing into the function. something like ... on
error goto

Then you will need to test to make sure the error is a DB error. You can
find this out buy testing the Err.number.

in the error section set the function to equal n/a (or 0 if you are
returning a number)

Put an if or case statement to test with error:

if err.numer = ???? then
FunctionName = "N/A"
else
msgbox "Unknown error:" & err.decription
end if

Good Luck,

Stewart Rogers

"StephenBrook" wrote:

I have a custom function that uses arguments from the user's worksheet to
find data in an external Access database using ADO, then return it to the
relevant cell. It works fine, but I want to be able to ensure that the custom
function leaves the cell value *untouched* (rather than returning an error
value) if the VBA cannot make the connection to the Access database (e.g. if
the user can't access the database for some reason).

I've tried using Application.Caller to get the cell's original value, but
this doesn't seem to work. Can anyone help?

Thanks,

SB

Assume the function will be used in Cell A1. Go to Tools=Options,
Calculate tab and check the iteration checkbox (this will suppress the
circular reference warning). Set Max iterations to 1.

in A1 put in the formula:
=if(iserror(function),A1,Function)

In your function put in error handling so your function returns an excell
error (CVErr(xlErrValue) as an example) if there is a problem with the
connection.

--
Regards,
Tom Ogilvy

"StephenBrook" wrote in message
...
I have a custom function that uses arguments from the user's worksheet to
find data in an external Access database using ADO, then return it to the
relevant cell. It works fine, but I want to be able to ensure that the
custom
function leaves the cell value *untouched* (rather than returning an error
value) if the VBA cannot make the connection to the Access database (e.g.
if
the user can't access the database for some reason).

I've tried using Application.Caller to get the cell's original value, but
this doesn't seem to work. Can anyone help?

Thanks,

SB

Guys,

Many thanks for the replies and your solutions - though they aren't quite
what I'm after. I have a situation where the spreadsheet will be
populated/configured by someone with access to the central database, but will
then be mailed to someone who doesn't have this access. Therefore I *don't*
want my custom function to return any error values when the database is not
accessible - I have the most likely errors trapped (e.g. wrong data type for
input arguments etc). In the specific event that the central database
connection is not available (and I know how to test the ADO connection for
this), I simply want the cells containing the custom function to return the
original value - NOT an error message!

Thanks again,

SB

"Tom Ogilvy" wrote:

Assume the function will be used in Cell A1. Go to Tools=Options,
Calculate tab and check the iteration checkbox (this will suppress the
circular reference warning). Set Max iterations to 1.

in A1 put in the formula:
=if(iserror(function),A1,Function)

In your function put in error handling so your function returns an excell
error (CVErr(xlErrValue) as an example) if there is a problem with the
connection.

--
Regards,
Tom Ogilvy

"StephenBrook" wrote in message
...
I have a custom function that uses arguments from the user's worksheet to
find data in an external Access database using ADO, then return it to the
relevant cell. It works fine, but I want to be able to ensure that the
custom
function leaves the cell value *untouched* (rather than returning an error
value) if the VBA cannot make the connection to the Access database (e.g.
if
the user can't access the database for some reason).

I've tried using Application.Caller to get the cell's original value, but
this doesn't seem to work. Can anyone help?

Thanks,

SB

Tom,

Many apologies - I hadn't read your post correctly before my first reply. I
did indeed try your suggestion with the IF(ISERROR( etc. trap, which works
perfectly....

....but do you know of any way that you can perform the same trick inside the
custom function itself? I only ask because the file containing the custom
function is to be used by an end user who may find the conditional statement
confusing. It would be more elegant if I could perform the same error
trapping within the function itself.

I assume the problem is that by the time the custom function has been
triggered by a recalculate, the previous value in the cell is already lost -
unless you use iterations, as in your example?

Thanks again,

SB

"Tom Ogilvy" wrote:

Assume the function will be used in Cell A1. Go to Tools=Options,
Calculate tab and check the iteration checkbox (this will suppress the
circular reference warning). Set Max iterations to 1.

in A1 put in the formula:
=if(iserror(function),A1,Function)

In your function put in error handling so your function returns an excell
error (CVErr(xlErrValue) as an example) if there is a problem with the
connection.

--
Regards,
Tom Ogilvy

"StephenBrook" wrote in message
...
I have a custom function that uses arguments from the user's worksheet to
find data in an external Access database using ADO, then return it to the
relevant cell. It works fine, but I want to be able to ensure that the
custom
function leaves the cell value *untouched* (rather than returning an error
value) if the VBA cannot make the connection to the Access database (e.g.
if
the user can't access the database for some reason).

I've tried using Application.Caller to get the cell's original value, but
this doesn't seem to work. Can anyone help?

Thanks,

SB