Error handling in VBA

[deltaquattro]

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

 

[Bob Phillips]

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

 

[Peter T]

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]

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. Here:

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

 

[deltaquattro]

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

 

[Peter T]

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


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

 

[deltaquattro]

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 here: 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

 

[Peter T]

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

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 here: 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

 

[deltaquattro]

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