Hello!

I have a user defined function to write data back to a database in my Excel
sheet. I return a smal text to show the user if the function was successful
or not. When I set the return value of the function in regular code, it works
fine. But, when something goes wrong, the execution goes to an error handler,
the function does not return the string I set anymore.

I have the code:


Public Function WriteData()
on error goto ErrorHandler

..
..
calculations
..
..

WriteData = "OK" 'This is returned correct to the
cell

goto End

ErrorHandler:

WriteData = "Not OK" 'This returns #VALUE to the cell

End:

End Function


Can someone tell me the reason of this behavior, and/or what I am doing wrong?

Regards DagL

A UDF cannot change any cell other than the one it is called from.


"DagL" wrote:

Hello!

I have a user defined function to write data back to a database in my Excel
sheet. I return a smal text to show the user if the function was successful
or not. When I set the return value of the function in regular code, it works
fine. But, when something goes wrong, the execution goes to an error handler,
the function does not return the string I set anymore.

I have the code:


Public Function WriteData()
on error goto ErrorHandler

.
.
calculations
.
.

WriteData = "OK" 'This is returned correct to the
cell

goto End

ErrorHandler:

WriteData = "Not OK" 'This returns #VALUE to the cell

End:

End Function


Can someone tell me the reason of this behavior, and/or what I am doing wrong?

Regards DagL

This is the cell where the function is called from. Sometimes the string is
returned correct, but when the code has been in an error handler, the cell
displays "#VALUE!"


"Mike H" wrote:

A UDF cannot change any cell other than the one it is called from.


"DagL" wrote:

Hello!

I have a user defined function to write data back to a database in my Excel
sheet. I return a smal text to show the user if the function was successful
or not. When I set the return value of the function in regular code, it works
fine. But, when something goes wrong, the execution goes to an error handler,
the function does not return the string I set anymore.

I have the code:


Public Function WriteData()
on error goto ErrorHandler

.
.
calculations
.
.

WriteData = "OK" 'This is returned correct to the
cell

goto End

ErrorHandler:

WriteData = "Not OK" 'This returns #VALUE to the cell

End:

End Function


Can someone tell me the reason of this behavior, and/or what I am doing wrong?

Regards DagL

Hi

Because the error handler is called from the UDF it is subject to the same
limitations as the UDF so if there's an error in the called routine you
aren't notified and simply get the VALUE error in the cell. Check out the
error handler it sounds very much like the error is within that.

Mike

"DagL" wrote:

This is the cell where the function is called from. Sometimes the string is
returned correct, but when the code has been in an error handler, the cell
displays "#VALUE!"


"Mike H" wrote:

A UDF cannot change any cell other than the one it is called from.


"DagL" wrote:

Hello!

I have a user defined function to write data back to a database in my Excel
sheet. I return a smal text to show the user if the function was successful
or not. When I set the return value of the function in regular code, it works
fine. But, when something goes wrong, the execution goes to an error handler,
the function does not return the string I set anymore.

I have the code:


Public Function WriteData()
on error goto ErrorHandler

.
.
calculations
.
.

WriteData = "OK" 'This is returned correct to the
cell

goto End

ErrorHandler:

WriteData = "Not OK" 'This returns #VALUE to the cell

End:

End Function


Can someone tell me the reason of this behavior, and/or what I am doing wrong?

Regards DagL

I have looked into this, but does not see any difference. The error is not
happening in an called routine, it is inside the UDF. I have an erorr handler
paragraph that calls an central error handler function. The return string
from the central error handler function is set to bee the return value of the
UDF. I tried not to call the central error handler function, and have a
simple error handler paragraph within the UDF, but the same result is
happening.

I have the error handler inside the UDF, and as soon as the execution goes
into this, the return from the UDF is wrong. When the code runs without
error, the return string is correct. See the code example I wrote in my first
post. My problem is actually as simple as this show. If the execution enters
the error handler paragraph, the UDF does not return what I set it to do.

Regards DagL

"Mike H" wrote:

Hi

Because the error handler is called from the UDF it is subject to the same
limitations as the UDF so if there's an error in the called routine you
aren't notified and simply get the VALUE error in the cell. Check out the
error handler it sounds very much like the error is within that.

Mike

"DagL" wrote:

This is the cell where the function is called from. Sometimes the string is
returned correct, but when the code has been in an error handler, the cell
displays "#VALUE!"


"Mike H" wrote:

A UDF cannot change any cell other than the one it is called from.


"DagL" wrote:

Hello!

I have a user defined function to write data back to a database in my Excel
sheet. I return a smal text to show the user if the function was successful
or not. When I set the return value of the function in regular code, it works
fine. But, when something goes wrong, the execution goes to an error handler,
the function does not return the string I set anymore.

I have the code:


Public Function WriteData()
on error goto ErrorHandler

.
.
calculations
.
.

WriteData = "OK" 'This is returned correct to the
cell

goto End

ErrorHandler:

WriteData = "Not OK" 'This returns #VALUE to the cell

End:

End Function


Can someone tell me the reason of this behavior, and/or what I am doing wrong?

Regards DagL

Check in VBE: Tools / Options / General / Break on Unhandled Errors


DagL wrote:
I have looked into this, but does not see any difference. The error is not
happening in an called routine, it is inside the UDF. I have an erorr handler
paragraph that calls an central error handler function. The return string
from the central error handler function is set to bee the return value of the
UDF. I tried not to call the central error handler function, and have a
simple error handler paragraph within the UDF, but the same result is
happening.

I have the error handler inside the UDF, and as soon as the execution goes
into this, the return from the UDF is wrong. When the code runs without
error, the return string is correct. See the code example I wrote in my first
post. My problem is actually as simple as this show. If the execution enters
the error handler paragraph, the UDF does not return what I set it to do.

Regards DagL

"Mike H" wrote:

Hi

Because the error handler is called from the UDF it is subject to the same
limitations as the UDF so if there's an error in the called routine you
aren't notified and simply get the VALUE error in the cell. Check out the
error handler it sounds very much like the error is within that.

Mike

"DagL" wrote:

This is the cell where the function is called from. Sometimes the string is
returned correct, but when the code has been in an error handler, the cell
displays "#VALUE!"


"Mike H" wrote:

A UDF cannot change any cell other than the one it is called from.


"DagL" wrote:

Hello!

I have a user defined function to write data back to a database in my Excel
sheet. I return a smal text to show the user if the function was successful
or not. When I set the return value of the function in regular code, it works
fine. But, when something goes wrong, the execution goes to an error handler,
the function does not return the string I set anymore.

I have the code:


Public Function WriteData()
on error goto ErrorHandler

.
.
calculations
.
.

WriteData = "OK" 'This is returned correct to the
cell

goto End

ErrorHandler:

WriteData = "Not OK" 'This returns #VALUE to the cell

End:

End Function


Can someone tell me the reason of this behavior, and/or what I am doing wrong?

Regards DagL

VBA won't allow me to use the statement goto End, which doesn't surprise me.
If I change goto End to goto EndIt and change End: to EndIt: then the
function seems to work fine. James

"Mike H" wrote in message
...
Hi

Because the error handler is called from the UDF it is subject to the same
limitations as the UDF so if there's an error in the called routine you
aren't notified and simply get the VALUE error in the cell. Check out the
error handler it sounds very much like the error is within that.

Mike

"DagL" wrote:

This is the cell where the function is called from. Sometimes the string
is
returned correct, but when the code has been in an error handler, the
cell
displays "#VALUE!"


"Mike H" wrote:

A UDF cannot change any cell other than the one it is called from.


"DagL" wrote:

Hello!

I have a user defined function to write data back to a database in my
Excel
sheet. I return a smal text to show the user if the function was
successful
or not. When I set the return value of the function in regular code,
it works
fine. But, when something goes wrong, the execution goes to an error
handler,
the function does not return the string I set anymore.

I have the code:


Public Function WriteData()
on error goto ErrorHandler

.
.
calculations
.
.

WriteData = "OK" 'This is returned correct
to the
cell

goto End

ErrorHandler:

WriteData = "Not OK" 'This returns #VALUE to the
cell

End:

End Function


Can someone tell me the reason of this behavior, and/or what I am
doing wrong?

Regards DagL

Actually, the paragraph that I named "End:" is in my original program
"Slutt:". This is because I wrote the example code in english terms, since
this is an english spoken community.

I have also tried not to use the line "Goto Slutt" in my error handler
paragraph, since the execution goes into this paragraph anyway. This has no
effect on my problem

Regards DagL

"Zone" wrote:

VBA won't allow me to use the statement goto End, which doesn't surprise me.
If I change goto End to goto EndIt and change End: to EndIt: then the
function seems to work fine. James

"Mike H" wrote in message
...
Hi

Because the error handler is called from the UDF it is subject to the same
limitations as the UDF so if there's an error in the called routine you
aren't notified and simply get the VALUE error in the cell. Check out the
error handler it sounds very much like the error is within that.

Mike

"DagL" wrote:

This is the cell where the function is called from. Sometimes the string
is
returned correct, but when the code has been in an error handler, the
cell
displays "#VALUE!"


"Mike H" wrote:

A UDF cannot change any cell other than the one it is called from.


"DagL" wrote:

Hello!

I have a user defined function to write data back to a database in my
Excel
sheet. I return a smal text to show the user if the function was
successful
or not. When I set the return value of the function in regular code,
it works
fine. But, when something goes wrong, the execution goes to an error
handler,
the function does not return the string I set anymore.

I have the code:


Public Function WriteData()
on error goto ErrorHandler

.
.
calculations
.
.

WriteData = "OK" 'This is returned correct
to the
cell

goto End

ErrorHandler:

WriteData = "Not OK" 'This returns #VALUE to the
cell

End:

End Function


Can someone tell me the reason of this behavior, and/or what I am
doing wrong?

Regards DagL