Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
I have looked at this, the "Break on Unhandled Errors " is the chosen
radiobutton Regards DagL "Luca Brasi" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
If you insert a line like
Debug.print "Error handler has run..." right at the end of your error handler (just before the line containing the "Slutt:" label) and then recalculate the sheet, does the message show up in the intermediate window in VBE? DagL wrote: I have looked at this, the "Break on Unhandled Errors " is the chosen radiobutton Regards DagL "Luca Brasi" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
I have now inserted the line you mentioned, and I can report that it is
written to the immediate window. Regards DagL "Luca Brasi" wrote: If you insert a line like Debug.print "Error handler has run..." right at the end of your error handler (just before the line containing the "Slutt:" label) and then recalculate the sheet, does the message show up in the intermediate window in VBE? DagL wrote: I have looked at this, the "Break on Unhandled Errors " is the chosen radiobutton Regards DagL "Luca Brasi" wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
If you copy following code into a module of a new workbook with just one
worksheet and insert =Test() into a cell... does that give the VALUE error as well? Public Function Test() As String On Error GoTo ERR_HANDLER Test = ThisWorkbook.Sheets(2).Name GoTo UDF_END ERR_HANDLER: Test = "#GRMPF#" UDF_END: End Function In your code, after the "Slutt:" label, there's nothing more than the "End Function" statement, right? DagL wrote: I have now inserted the line you mentioned, and I can report that it is written to the immediate window. Regards DagL "Luca Brasi" wrote: If you insert a line like Debug.print "Error handler has run..." right at the end of your error handler (just before the line containing the "Slutt:" label) and then recalculate the sheet, does the message show up in the intermediate window in VBE? DagL wrote: I have looked at this, the "Break on Unhandled Errors " is the chosen radiobutton Regards DagL "Luca Brasi" wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
Your code in a new workbook does not give the VALUE error. The return value of this is what was expected, #GRMPF# Actually, my Slutt: paragraph does have code. The code is to close down a recordset that I have used in the code. I did not think this was of interest, since both error situations and correct situations went through the code. My Slutt: paragraph looks like this: Slutt: ' clean up If rs.State = adStateOpen Then rs.Close End If I now tried to remove the code in the Slutt: paragraph, and then it worked! But, does this mean that I cannot close my recordset after an error? Regards DagL "Luca Brasi" wrote: If you copy following code into a module of a new workbook with just one worksheet and insert =Test() into a cell... does that give the VALUE error as well? Public Function Test() As String On Error GoTo ERR_HANDLER Test = ThisWorkbook.Sheets(2).Name GoTo UDF_END ERR_HANDLER: Test = "#GRMPF#" UDF_END: End Function In your code, after the "Slutt:" label, there's nothing more than the "End Function" statement, right? DagL wrote: I have now inserted the line you mentioned, and I can report that it is written to the immediate window. Regards DagL "Luca Brasi" wrote: If you insert a line like Debug.print "Error handler has run..." right at the end of your error handler (just before the line containing the "Slutt:" label) and then recalculate the sheet, does the message show up in the intermediate window in VBE? DagL wrote: I have looked at this, the "Break on Unhandled Errors " is the chosen radiobutton Regards DagL "Luca Brasi" wrote: 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
As far as I know the error handling section should always be the last
part of a procedure. Once a runtime error has occurred and the the code jumped to an error handler label, no more errors are raised! It looks like the code would work. I think you'll have to restructure your code to achieve what you want. DagL wrote: Your code in a new workbook does not give the VALUE error. The return value of this is what was expected, #GRMPF# Actually, my Slutt: paragraph does have code. The code is to close down a recordset that I have used in the code. I did not think this was of interest, since both error situations and correct situations went through the code. My Slutt: paragraph looks like this: Slutt: ' clean up If rs.State = adStateOpen Then rs.Close End If I now tried to remove the code in the Slutt: paragraph, and then it worked! But, does this mean that I cannot close my recordset after an error? Regards DagL "Luca Brasi" wrote: If you copy following code into a module of a new workbook with just one worksheet and insert =Test() into a cell... does that give the VALUE error as well? Public Function Test() As String On Error GoTo ERR_HANDLER Test = ThisWorkbook.Sheets(2).Name GoTo UDF_END ERR_HANDLER: Test = "#GRMPF#" UDF_END: End Function In your code, after the "Slutt:" label, there's nothing more than the "End Function" statement, right? DagL wrote: I have now inserted the line you mentioned, and I can report that it is written to the immediate window. Regards DagL "Luca Brasi" wrote: If you insert a line like Debug.print "Error handler has run..." right at the end of your error handler (just before the line containing the "Slutt:" label) and then recalculate the sheet, does the message show up in the intermediate window in VBE? DagL wrote: I have looked at this, the "Break on Unhandled Errors " is the chosen radiobutton Regards DagL "Luca Brasi" wrote: 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
Check in VBE: Tools / Options / General / Break on Unhandled Errors
As a general rule, you should choose "Break In Class Module", not "Break On Unhandled Errors". This difference becomes clear when you have an error in an object module. Suppose you have a UserForm with code like: Private Sub UserForm_Initialize() Me.Label1.Caption = CStr(1 / 0) ' Error End Sub an a code module with code like Sub ShowTheForm() UserForm1.Show End Sub If you have the error trapping set to "Break On Unhandled Errors", you 'll set a run time error on the line of code UserForm1.Show But there really isn't an error with the Show method. However, if you set error trapping to "Break In Class Module", you'll get an error on the correct line of code: Me.Label1.Caption = CStr(1 / 0) If you didn't know the difference in error handling modes, you could spend a lot a valuable time trying to figure out what is wrong with the Show method. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Luca Brasi" wrote in message ... 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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
Chip, That's fantastic. Wish I had know about this years ago. Sure would
have made learning userforms easier. Would it be reasonable, after fixing any errors in the userforms (so that Userform1.Show doesn't error out), to set it back to "Break on Unhandled Errors"? James "Chip Pearson" wrote in message ... Check in VBE: Tools / Options / General / Break on Unhandled Errors As a general rule, you should choose "Break In Class Module", not "Break On Unhandled Errors". This difference becomes clear when you have an error in an object module. Suppose you have a UserForm with code like: Private Sub UserForm_Initialize() Me.Label1.Caption = CStr(1 / 0) ' Error End Sub an a code module with code like Sub ShowTheForm() UserForm1.Show End Sub If you have the error trapping set to "Break On Unhandled Errors", you 'll set a run time error on the line of code UserForm1.Show But there really isn't an error with the Show method. However, if you set error trapping to "Break In Class Module", you'll get an error on the correct line of code: Me.Label1.Caption = CStr(1 / 0) If you didn't know the difference in error handling modes, you could spend a lot a valuable time trying to figure out what is wrong with the Show method. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Luca Brasi" wrote in message ... 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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
Would it be reasonable, after fixing any errors in the userforms (so that Userform1.Show doesn't error out), to set it back to "Break on Unhandled Errors"? I wouldn't. No matter how fully one tests code, any non-trivial code can blow up under some unforeseen (and untested) circumstance, no matter how remote. I would leave error handling at "break in class" for easy debugging down the road. There is no reason to change it back to "unhandled errors". -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Zone" wrote in message ... Chip, That's fantastic. Wish I had know about this years ago. Sure would have made learning userforms easier. Would it be reasonable, after fixing any errors in the userforms (so that Userform1.Show doesn't error out), to set it back to "Break on Unhandled Errors"? James "Chip Pearson" wrote in message ... Check in VBE: Tools / Options / General / Break on Unhandled Errors As a general rule, you should choose "Break In Class Module", not "Break On Unhandled Errors". This difference becomes clear when you have an error in an object module. Suppose you have a UserForm with code like: Private Sub UserForm_Initialize() Me.Label1.Caption = CStr(1 / 0) ' Error End Sub an a code module with code like Sub ShowTheForm() UserForm1.Show End Sub If you have the error trapping set to "Break On Unhandled Errors", you 'll set a run time error on the line of code UserForm1.Show But there really isn't an error with the Show method. However, if you set error trapping to "Break In Class Module", you'll get an error on the correct line of code: Me.Label1.Caption = CStr(1 / 0) If you didn't know the difference in error handling modes, you could spend a lot a valuable time trying to figure out what is wrong with the Show method. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Luca Brasi" wrote in message ... 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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning text from Userdefined Functions
Okay. Thank you, Chip.
"Chip Pearson" wrote in message ... Would it be reasonable, after fixing any errors in the userforms (so that Userform1.Show doesn't error out), to set it back to "Break on Unhandled Errors"? I wouldn't. No matter how fully one tests code, any non-trivial code can blow up under some unforeseen (and untested) circumstance, no matter how remote. I would leave error handling at "break in class" for easy debugging down the road. There is no reason to change it back to "unhandled errors". -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Zone" wrote in message ... Chip, That's fantastic. Wish I had know about this years ago. Sure would have made learning userforms easier. Would it be reasonable, after fixing any errors in the userforms (so that Userform1.Show doesn't error out), to set it back to "Break on Unhandled Errors"? James "Chip Pearson" wrote in message ... Check in VBE: Tools / Options / General / Break on Unhandled Errors As a general rule, you should choose "Break In Class Module", not "Break On Unhandled Errors". This difference becomes clear when you have an error in an object module. Suppose you have a UserForm with code like: Private Sub UserForm_Initialize() Me.Label1.Caption = CStr(1 / 0) ' Error End Sub an a code module with code like Sub ShowTheForm() UserForm1.Show End Sub If you have the error trapping set to "Break On Unhandled Errors", you 'll set a run time error on the line of code UserForm1.Show But there really isn't an error with the Show method. However, if you set error trapping to "Break In Class Module", you'll get an error on the correct line of code: Me.Label1.Caption = CStr(1 / 0) If you didn't know the difference in error handling modes, you could spend a lot a valuable time trying to figure out what is wrong with the Show method. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Luca Brasi" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UserDefined Functions | Excel Programming | |||
Returning range object from custom functions | Excel Programming | |||
Functions returning multiple answers | Excel Programming | |||
Returning Error from Functions | Excel Programming | |||
Passing Parameters to Userdefined Functions | Excel Programming |