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

Try your function like this using the same arguments.

Function ZCinterp2(x As Date, vX() As Date, vY() As Double, lInterpType
As Long) As Double

ZCinterp2 = 100.25
End Function

does it work that way.

If not, then you have a disconnect with the way your arguments are declared.

I tried it that way using
=ZCinterp2(E1,A1:A21,B1:B20,0)

E1 was a date
A1:A21 contained dates
B1:B20 contained numbers

It didn't work for me. (#Value)

Try changing your 2nd and 3rd arguments to Ranges and make the adjustment in
the internal workings in your macro.

--
Regards,
Tom Ogilvy




"pinkfloydfan" wrote:

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

Just to expand, this worked

Function ZCinterp2(x As Date, vX As Range, vY As Range, lInterpType As Long)
As Double

ZCinterp2 = 100.25
End Function


Now you could adapt your fuction to work with those arguments.

If you want to continue to deal with arrays, then it would be

Function ZCinterp2(x As Date, vX As Variant, vY As Variant, lInterpType As
Long) As Double

ZCinterp2 = 100.25
End Function

However, your arrays would be 2 dimensional. If the 2nd argument was
A1:A10, the then array would by vX(1 to 10, 1 to 1) as an example. The
lower bound in each dimension would be 1 regardless of the option base
setting.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote:

Try your function like this using the same arguments.

Function ZCinterp2(x As Date, vX() As Date, vY() As Double, lInterpType
As Long) As Double

ZCinterp2 = 100.25
End Function

does it work that way.

If not, then you have a disconnect with the way your arguments are declared.

I tried it that way using
=ZCinterp2(E1,A1:A21,B1:B20,0)

E1 was a date
A1:A21 contained dates
B1:B20 contained numbers

It didn't work for me. (#Value)

Try changing your 2nd and 3rd arguments to Ranges and make the adjustment in
the internal workings in your macro.

--
Regards,
Tom Ogilvy




"pinkfloydfan" wrote:

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