Linear interpolation - does anyone have any faster code than this?

[Mightyginger]

This code seems very slow when I'm using lots of them. Does anyone have any faster code please?

Function LinearInterp(T As Variant, Trange As Variant, LRange As Variant) As Double
Dim i As Integer, max As Integer, j As Integer

On Error GoTo ErrorHandler

max = 500

For i = 1 To max
If T <= Trange(i) Then Exit For
Next i

If i = 1 Then
LinearInterp = LRange(i)
ElseIf i = max Then
LinearInterp = LRange(max)
Else
LinearInterp = ((((Trange(i) - T) * (LRange(i - 1))) + ((T - Trange(i - 1)) * (LRange(i)))) / (Trange(i) - Trange(i - 1)))
End If
Exit Function

ErrorHandler:
LinearInterp = LRange(i - 1)

End Function

 

[Zathras]

If you can store the data tables in worksheet columns and refer to them as ranges, then here is a routine that is approximately 5 times as fast. The speed increase comes from not using variants and not coding your own lookup loop in code. This routine is also slightly more accurate for input values slightly outside of the range of table entries as it will extrapolate using the first two or last two entries as appropriate.

Function VLinearInterpolation(T As Double, TRange As Range, _
                LRange As Range) As Double
Dim nRow As Integer
Dim TLow As Double
Dim THigh As Double
Dim LLow As Double
Dim LHigh As Double
  [green]
  'If at top or bottom, use two points at the end to extrapolate[/green]
  If T < TRange.Cells(1, 1) Then
    nRow = 1
  Else
    nRow = WorksheetFunction.Match(T, TRange)
    If nRow = TRange.Rows.Count Then
      nRow = nRow - 1
    End If
  End If
  
  TLow = TRange.Cells(nRow, 1)
  THigh = TRange.Cells(nRow + 1, 1)
  LLow = LRange.Cells(nRow, 1)
  LHigh = LRange.Cells(nRow + 1, 1)
  VLinearInterpolation = (T - TLow) * (LHigh - LLow) / (THigh - TLow) + LLow

End Function

If there are fewer than 256 entries in the data table, someone may wish to store them horizontally in which case they can create a companion function HLinearInterpolation and make the switch between rows and columns in the cell references.