Note sure if this posted properly the first time so I'm repeating it
again:

Hi all

I have written a function to interpolate either linearly or
exponentially but when using it keeps throwing up a #VALUE! error and
the debugger is no help. Ultimately I want to be able to extend this
to include cubic spline and constrained cubic spline interpolation
(aside: if anyone has any code to help there then I would appreciate
it).

I was hoping that someone might be able to see what stupid error I have
made

Thanks a lot for your help in advance

Lloyd

The code is he

Option Explicit
Option Base 1


' Interpolator
Function ZCinterp2(x As Date, vX() As Date, vY() As Double, lInterpType
As Long) As Double
Dim i As Long
Dim vLogY() As Double

If lInterpType = 0 Then
' Linear interpolation
If (x < vX(LBound(vX))) Then
' x less than lowest?
ZCinterp2 = vY(LBound(vY)) + (x - vX(LBound(vX))) *
(vY(LBound(vY) + 1) - vY(LBound(vY))) / (vX(LBound(vX) + 1) -
vX(LBound(vX)))
ElseIf (x vX(UBound(vX))) Then
' x is more than the highest!
ZCinterp2 = vY(UBound(vY)) + (x - vX(UBound(vX))) *
(vY(UBound(vY) - 1) - vY(UBound(vY))) / (vX(UBound(vX) - 1) -
vX(UBound(vX)))
Else
' x is between two.
For i = LBound(vX) To UBound(vX)
If vX(i) x Then Exit For
If vX(i) = x Then
ZCinterp2 = vY(i)
Exit Function
End If
Next
ZCinterp2 = vY(i - 1) + (x - vX(i - 1)) * (vY(i) - vY(i -
1)) / (vX(i) - vX(i - 1))
End If
ElseIf lInterpType = 1 Then
' Exponential interpolation
ReDim vLogY(LBound(vY) To UBound(vY))
For i = LBound(vY) To UBound(vY)
If vY(i) = 0 Then vLogY(i) = 100000000000# Else vLogY(i) =
Log(vY(i))
ZCinterp2 = Exp(ZCinterp2(x, vX, vLogY, 0))
Next
Else
' Unknown interpolation code
ErrorMessage "Unknown interpolation type."
End If
End Function

See answer to previous post.

--
Regards,
Tom Ogilvy


"pinkfloydfan" wrote:

Note sure if this posted properly the first time so I'm repeating it
again:

Hi all

I have written a function to interpolate either linearly or
exponentially but when using it keeps throwing up a #VALUE! error and
the debugger is no help. Ultimately I want to be able to extend this
to include cubic spline and constrained cubic spline interpolation
(aside: if anyone has any code to help there then I would appreciate
it).

I was hoping that someone might be able to see what stupid error I have
made

Thanks a lot for your help in advance

Lloyd

The code is he

Option Explicit
Option Base 1


' Interpolator
Function ZCinterp2(x As Date, vX() As Date, vY() As Double, lInterpType
As Long) As Double
Dim i As Long
Dim vLogY() As Double

If lInterpType = 0 Then
' Linear interpolation
If (x < vX(LBound(vX))) Then
' x less than lowest?
ZCinterp2 = vY(LBound(vY)) + (x - vX(LBound(vX))) *
(vY(LBound(vY) + 1) - vY(LBound(vY))) / (vX(LBound(vX) + 1) -
vX(LBound(vX)))
ElseIf (x vX(UBound(vX))) Then
' x is more than the highest!
ZCinterp2 = vY(UBound(vY)) + (x - vX(UBound(vX))) *
(vY(UBound(vY) - 1) - vY(UBound(vY))) / (vX(UBound(vX) - 1) -
vX(UBound(vX)))
Else
' x is between two.
For i = LBound(vX) To UBound(vX)
If vX(i) x Then Exit For
If vX(i) = x Then
ZCinterp2 = vY(i)
Exit Function
End If
Next
ZCinterp2 = vY(i - 1) + (x - vX(i - 1)) * (vY(i) - vY(i -
1)) / (vX(i) - vX(i - 1))
End If
ElseIf lInterpType = 1 Then
' Exponential interpolation
ReDim vLogY(LBound(vY) To UBound(vY))
For i = LBound(vY) To UBound(vY)
If vY(i) = 0 Then vLogY(i) = 100000000000# Else vLogY(i) =
Log(vY(i))
ZCinterp2 = Exp(ZCinterp2(x, vX, vLogY, 0))
Next
Else
' Unknown interpolation code
ErrorMessage "Unknown interpolation type."
End If
End Function

Thanks Tom for your response.

I think changing it to ranges will work for a worksheet function but
then if I want to call it from within another Function that passes
arrays I will need to use the original version...I'll test it out on
Friday and let you know.

Appreciate the help

Lloyd