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