Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error handling in VBA
Hi guys,
I'm not so used to the On Error statement, ssince I come from a Fortran background where all error handling must be performed using If Then Else constructs. Example: in this interpolation subroutine 'Returns an interpolated value of x 'doing a lookup of xarr-yarr Public Function Interp1(xArr() As Double, yArr() As Double, X As Double) As Double Dim I As Long If ((X < xArr(LBound(xArr))) Or (X xArr(UBound(xArr)))) Then MsgBox "Interp1: x is out of bound" Stop Exit Function End If If xArr(LBound(xArr)) = X Then Interp1 = yArr(LBound(yArr)) Exit Function End If 'For i = LBound(xArr) To UBound(xArr) ' If xArr(i) = X Then ' Interp1 = yArr(i - 1) + (X - xArr(i - 1)) / (xArr(i) - xArr(i - 1)) * (yArr(i) - yArr(i - 1)) ' Exit Function ' End If 'Next i I = Locate(xArr, X) + 1 Interp1 = yArr(I - 1) + (X - xArr(I - 1)) / (xArr(I) - xArr(I - 1)) * (yArr(I) - yArr(I - 1)) End Function woudl you advise substituting the If...MsgBox combination with an On Error statement? In general, how can I understand if it's better to use On Error or to test for errors with If? Finally, a purely programming style question: would you suggest to include the error handling in the interpolation subroutine, or to move it in the caller subroutine? Thanks, Best Regards deltaquattro |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error handling in VBA
I wouldn't, I would leave the message box.
I use error trapping for unanticipated errors, that situation that you have is perfectly predictable. My code would look something like On Error GoTo errHandler 'the real code exitHandler: 'general tear-down code Exit Sub/Function errHandler: MsgBox "Unanticipated error:" & vbNewLine & _ vbTab & "Err #: " & Err.Number & vbNewLine & _ vbTab & "Description :" & Err.Description Resume exitHandler HTH Bob "deltaquattro" wrote in message ... Hi guys, I'm not so used to the On Error statement, ssince I come from a Fortran background where all error handling must be performed using If Then Else constructs. Example: in this interpolation subroutine 'Returns an interpolated value of x 'doing a lookup of xarr-yarr Public Function Interp1(xArr() As Double, yArr() As Double, X As Double) As Double Dim I As Long If ((X < xArr(LBound(xArr))) Or (X xArr(UBound(xArr)))) Then MsgBox "Interp1: x is out of bound" Stop Exit Function End If If xArr(LBound(xArr)) = X Then Interp1 = yArr(LBound(yArr)) Exit Function End If 'For i = LBound(xArr) To UBound(xArr) ' If xArr(i) = X Then ' Interp1 = yArr(i - 1) + (X - xArr(i - 1)) / (xArr(i) - xArr(i - 1)) * (yArr(i) - yArr(i - 1)) ' Exit Function ' End If 'Next i I = Locate(xArr, X) + 1 Interp1 = yArr(I - 1) + (X - xArr(I - 1)) / (xArr(I) - xArr(I - 1)) * (yArr(I) - yArr(I - 1)) End Function woudl you advise substituting the If...MsgBox combination with an On Error statement? In general, how can I understand if it's better to use On Error or to test for errors with If? Finally, a purely programming style question: would you suggest to include the error handling in the interpolation subroutine, or to move it in the caller subroutine? Thanks, Best Regards deltaquattro |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error handling in VBA
In your routine you don't really need an error handler as you are testing
for the anticipated causes of an error in a way that doesn't generate an error. That said in a production enviroment everything should run under an error handler, although not necessarily in the same routine. Sometimes you might want to deliverately raise an error, I've amended your interpolate routine to demonstrate, albeit a little artificially (some other changes too in passing) Public Function Interp2(xArr() As Double, yArr() As Double, _ x As Double) As Double Dim i As Long On Error GoTo errH If ((x < xArr(LBound(xArr))) Or (x xArr(UBound(xArr)))) Then 'MsgBox "Interp2: x is out of bound" Err.Raise 12345, , "X = " & x Else For i = LBound(xArr) To UBound(xArr) If xArr(i) = x Then Interp2 = yArr(i) Exit For ElseIf xArr(i) = x Then Interp2 = yArr(i - 1) + (x - xArr(i - 1)) / _ (xArr(i) - xArr(i - 1)) * (yArr(i) - yArr(i - 1)) Exit For End If Next i End If ' i = i / 0 '' < test an error Exit Function errH: If Err.Number = 12345 Then MsgBox "Interp2: x is out of bound" & vbCr & Err.Description Else MsgBox Err.Description End If End Function Be careful to ensure you don't accidently trigger an error after 'errH', unless you deliberately want to raise another error to be handled in the calling routine. Regards, Peter T "deltaquattro" wrote in message ... Hi guys, I'm not so used to the On Error statement, ssince I come from a Fortran background where all error handling must be performed using If Then Else constructs. Example: in this interpolation subroutine 'Returns an interpolated value of x 'doing a lookup of xarr-yarr Public Function Interp1(xArr() As Double, yArr() As Double, X As Double) As Double Dim I As Long If ((X < xArr(LBound(xArr))) Or (X xArr(UBound(xArr)))) Then MsgBox "Interp1: x is out of bound" Stop Exit Function End If If xArr(LBound(xArr)) = X Then Interp1 = yArr(LBound(yArr)) Exit Function End If 'For i = LBound(xArr) To UBound(xArr) ' If xArr(i) = X Then ' Interp1 = yArr(i - 1) + (X - xArr(i - 1)) / (xArr(i) - xArr(i - 1)) * (yArr(i) - yArr(i - 1)) ' Exit Function ' End If 'Next i I = Locate(xArr, X) + 1 Interp1 = yArr(I - 1) + (X - xArr(I - 1)) / (xArr(I) - xArr(I - 1)) * (yArr(I) - yArr(I - 1)) End Function woudl you advise substituting the If...MsgBox combination with an On Error statement? In general, how can I understand if it's better to use On Error or to test for errors with If? Finally, a purely programming style question: would you suggest to include the error handling in the interpolation subroutine, or to move it in the caller subroutine? Thanks, Best Regards deltaquattro |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error handling in VBA
Hi, Bob,
that's a good general advice, thanks. BTW, do you know if there are free profilers and/or dependency tree graphers for VBA? I have some excellent open source codes for Fortran, but I haven't been able to find something similar for VBA. He http://www.bmsltd.co.uk/Excel/Default.htm there's a CallTree code, but it doesn't work on my multiworkbook project. Best Regards Sergio On 4 Feb, 13:17, "Bob Phillips" wrote: I wouldn't, I would leave the message box. I use error trapping for unanticipated errors, that situation that you have is perfectly predictable. My code would look something like * * On Error GoTo errHandler * * 'the real code exitHandler: * * 'general tear-down code * * Exit Sub/Function errHandler: * * MsgBox "Unanticipated error:" & vbNewLine & _ * * * * * *vbTab & "Err #: " & Err.Number & vbNewLine & _ * * * * * *vbTab & "Description :" & Err.Description * * * * * *Resume exitHandler HTH Bob "deltaquattro" wrote in message ... Hi guys, I'm not so used to the On Error statement, ssince I come from a Fortran background where all error handling must be performed using If Then Else constructs. Example: in this interpolation subroutine 'Returns an interpolated value of x 'doing a lookup of xarr-yarr Public Function Interp1(xArr() As Double, yArr() As Double, X As Double) As Double Dim I As Long If ((X < xArr(LBound(xArr))) Or (X xArr(UBound(xArr)))) Then * MsgBox "Interp1: x is out of bound" * Stop * Exit Function End If If xArr(LBound(xArr)) = X Then *Interp1 = yArr(LBound(yArr)) *Exit Function End If 'For i = LBound(xArr) To UBound(xArr) ' *If xArr(i) = X Then ' * *Interp1 = yArr(i - 1) + (X - xArr(i - 1)) / (xArr(i) - xArr(i - 1)) * (yArr(i) - yArr(i - 1)) ' * *Exit Function ' *End If 'Next i I = Locate(xArr, X) + 1 Interp1 = yArr(I - 1) + (X - xArr(I - 1)) / (xArr(I) - xArr(I - 1)) * (yArr(I) - yArr(I - 1)) End Function woudl you advise substituting the If...MsgBox combination with an On Error statement? In general, how can I understand if it's better to use On Error or to test for errors with If? *Finally, a purely programming style question: would you suggest to include the error handling in the interpolation subroutine, or to move it in the caller subroutine? Thanks, Best Regards deltaquattro |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error handling in VBA
Hi, Peter,
thanks for the suggestion. About the changes you added, you restored linear search instead than calling a locate subroutine based on bisection, and substituted the Exit Function with Exit For. Also, you test for xArr(i) being equal to x. Did you get back to linear search just for the sake of testing the code, since I didn't include the Locate subroutine? Or do you think it's a better approach? Also, would please have a look at my questions to Bob and let me know if you know of freeware which helps writing VBA code? I would be most grateful to anybody who can provide suggestions on the issue. Thanks again, Best Regards, Sergio On 4 Feb, 13:31, "Peter T" <peter_t@discussions wrote: In your routine you don't really need an error handler as you are testing for the anticipated causes of an error in a way that doesn't generate an error. That said in a production enviroment everything should run under an error handler, although not necessarily in the same routine. Sometimes you might want to deliverately raise an error, I've amended your interpolate routine to demonstrate, albeit a little artificially (some other changes too in passing) Public Function Interp2(xArr() As Double, yArr() As Double, _ * * * * * * * * * * * * * * x As Double) As Double Dim i As Long * * On Error GoTo errH * * If ((x < xArr(LBound(xArr))) Or (x xArr(UBound(xArr)))) Then * * * * 'MsgBox "Interp2: x is out of bound" * * * * Err.Raise 12345, , "X = " & x * * Else * * * * For i = LBound(xArr) To UBound(xArr) * * * * * * If xArr(i) = x Then * * * * * * * * Interp2 = yArr(i) * * * * * * * * Exit For * * * * * * ElseIf xArr(i) = x Then * * * * * * * * Interp2 = yArr(i - 1) + (x - xArr(i - 1)) / _ * * * * * * * * * * * * * (xArr(i) - xArr(i - 1)) * (yArr(i) - yArr(i - 1)) * * * * * * * * Exit For * * * * * * End If * * * * Next i * * End If * *' i = i / 0 '' < test an error * * Exit Function errH: * * If Err.Number = 12345 Then * * * * MsgBox "Interp2: x is out of bound" & vbCr & Err.Description * * Else * * * * MsgBox Err.Description * * End If End Function Be careful to ensure you don't accidently trigger an error after 'errH', unless you deliberately want to raise another error to be handled in the calling routine. Regards, Peter T |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error handling in VBA
I assumed your Locate function (not posted) simply located the appropriate
array element based on X, along the lines of what you had commented out. Indeed the function I posted interpolates linearly, difficult to see with the details passed to your Locate function that ultimately your routine would not also calculate linearly. Exit For vs Exit Function is a matter of style, but in context more efficient. If linear interpolation is not appropriate Excel provides many functions which might be useful, particularly with logarithmic / polynomial type data, eg Linest. Only you know if linear is appropriate; typically it depends on a combination of the 'closeness' of data, accuracy required, and not least if the source data is already relatively linear in both directions. I'm not quite sure what you mean by profilers and dependency tree graphers, unless you mean something that will document your code along the lines of a flow-chart (I'm not aware of anything that does that). Maybe you might find 'MZ Tools' useful, which has a tool to document procedure callers, albeit on an individual basis. Regards, Peter T "deltaquattro" wrote in message ... Hi, Peter, thanks for the suggestion. About the changes you added, you restored linear search instead than calling a locate subroutine based on bisection, and substituted the Exit Function with Exit For. Also, you test for xArr(i) being equal to x. Did you get back to linear search just for the sake of testing the code, since I didn't include the Locate subroutine? Or do you think it's a better approach? Also, would please have a look at my questions to Bob and let me know if you know of freeware which helps writing VBA code? I would be most grateful to anybody who can provide suggestions on the issue. Thanks again, Best Regards, Sergio On 4 Feb, 13:31, "Peter T" <peter_t@discussions wrote: In your routine you don't really need an error handler as you are testing for the anticipated causes of an error in a way that doesn't generate an error. That said in a production enviroment everything should run under an error handler, although not necessarily in the same routine. Sometimes you might want to deliverately raise an error, I've amended your interpolate routine to demonstrate, albeit a little artificially (some other changes too in passing) Public Function Interp2(xArr() As Double, yArr() As Double, _ x As Double) As Double Dim i As Long On Error GoTo errH If ((x < xArr(LBound(xArr))) Or (x xArr(UBound(xArr)))) Then 'MsgBox "Interp2: x is out of bound" Err.Raise 12345, , "X = " & x Else For i = LBound(xArr) To UBound(xArr) If xArr(i) = x Then Interp2 = yArr(i) Exit For ElseIf xArr(i) = x Then Interp2 = yArr(i - 1) + (x - xArr(i - 1)) / _ (xArr(i) - xArr(i - 1)) * (yArr(i) - yArr(i - 1)) Exit For End If Next i End If ' i = i / 0 '' < test an error Exit Function errH: If Err.Number = 12345 Then MsgBox "Interp2: x is out of bound" & vbCr & Err.Description Else MsgBox Err.Description End If End Function Be careful to ensure you don't accidently trigger an error after 'errH', unless you deliberately want to raise another error to be handled in the calling routine. Regards, Peter T |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error handling in VBA
On 4 Feb, 18:44, "Peter T" <peter_t@discussions wrote:
I assumed your Locate function (not posted) simply located the appropriate array element based on X, along the lines of what you had commented out. [..] Hi, Peter, sorry for the misunderstanding he sure, I do linear interpolation. What I was trying to say is that the code you restored performs linear search, which is a confusing name for an algorithm for searching ordered tables (so it's not directly related to linear interpolation). It just means that, if I want to find the position of x in xArr where xArr is an ordered array of N elements, I start from the first element of xArr and compare each element with x in a loop. For increasing N, this can be shown to be on average slower than a bisection search, which is the one implemented in Locate. Obviously you couldn't know that because I didn't include any details about Locate. My bad. I'm not quite sure what you mean by profilers and dependency tree graphers, unless you mean something that will document your code along the lines of a flow-chart (I'm not aware of anything that does that). Profiler = a code which tells me how much time the code spends in each subroutine. Useful to find computation time bottlenecks. Dependency (or call) tree grapher = a code which draws a tree, or (even better) create an HTML file with hyperlinks, which shows who calls who among the various subroutines, and maybe writes which are the arguments for each procedure. Maybe you might find 'MZ Tools' useful, which has a tool to document procedure callers, albeit on an individual basis. That's excellent, but unluckily it works for one sub at a time as you correctly point out. Regards, Peter T Best Regards Sergio |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error handling in VBA
I'm in a bit of a rush at the moment but just this bit
What I was trying to say is that the code you restored performs linear search, That wasn't the intention, it was supposed to interpolate linearly between to known X points and return the relative Y. Maybe I did something wrong, will look again tomorrow. Regards, Peter T "deltaquattro" wrote in message ... On 4 Feb, 18:44, "Peter T" <peter_t@discussions wrote: I assumed your Locate function (not posted) simply located the appropriate array element based on X, along the lines of what you had commented out. [..] Hi, Peter, sorry for the misunderstanding he sure, I do linear interpolation. What I was trying to say is that the code you restored performs linear search, which is a confusing name for an algorithm for searching ordered tables (so it's not directly related to linear interpolation). It just means that, if I want to find the position of x in xArr where xArr is an ordered array of N elements, I start from the first element of xArr and compare each element with x in a loop. For increasing N, this can be shown to be on average slower than a bisection search, which is the one implemented in Locate. Obviously you couldn't know that because I didn't include any details about Locate. My bad. I'm not quite sure what you mean by profilers and dependency tree graphers, unless you mean something that will document your code along the lines of a flow-chart (I'm not aware of anything that does that). Profiler = a code which tells me how much time the code spends in each subroutine. Useful to find computation time bottlenecks. Dependency (or call) tree grapher = a code which draws a tree, or (even better) create an HTML file with hyperlinks, which shows who calls who among the various subroutines, and maybe writes which are the arguments for each procedure. Maybe you might find 'MZ Tools' useful, which has a tool to document procedure callers, albeit on an individual basis. That's excellent, but unluckily it works for one sub at a time as you correctly point out. Regards, Peter T Best Regards Sergio |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error handling in VBA
Hi, Peter,
yes, we're saying the same thing, your code performs linear interpolation. The code is perfectly ok, I was trying to say another thing but it's not important. Thanks for help, ciao Andrea On 4 Feb, 20:05, "Peter T" <peter_t@discussions wrote: I'm in a bit of a rush at the moment but just this bit What I was trying to say is that the code you restored performs linear search, That wasn't the intention, it was supposed to interpolate linearly between to known X points and return the relative Y. Maybe I did something wrong, will look again tomorrow. Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
First Error works but Subsequent Error Handling Does Not | Excel Programming | |||
Error handling error # 1004 Run-time error | Excel Programming | |||
Set Error handling INSIDE error-trap | Excel Programming | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Error handling with a handling routine | Excel Programming |