MS Excel VBA - Bilinear interpolation function

Bilinear interpolation (interpolating within a 2-dimensional table) can be done with regular MS Excel functions. But it will become a pretty long formula, that is hard to read and/or copy across. You can also implement a user defined function interpolateXY. The VBA code of this function is found at the end of this page.

Download demo file (17kB - downloaded 1530 times)

Calculation explanation of above example

Visualisation of the data table above

Calculation
X = 451 lies between X = 400 and X = 500. X-fraction = (451 - 400)/(500 - 400) = 0.51
Y = 75 lies between Y = 60 and Y = 80. Y-fraction = (75 - 60)/(80 - 60) = 0.75

The 4 data point values to interpolate between are (X,Y):
     1. (400,60) = 149
     2. (500,60) = 163
     3. (400,80) = 169
     4. (500,80) = 187

Interpolation X:
     1.&2. → 149 + 0.51 × (163 - 149) = 156.14
     3.&4. → 169 + 0.51 × (187 - 169) = 178.18

Interpolation Y:
     156.14 + 0.75 × (178.18 - 156.14) = 172.67 which is the final result.

VBA code to be put in a module

In MS Excel, press Alt+F11 to open the Visual Basic Editor (Windows). Via top menu: Insert → Module. Copy the code below into the module.

Public Function InterpolateXY(ByRef X As Range, ByRef Y As Range, ByRef XRange As Range, ByRef YRange As Range, ByRef ValueTable As Range) As Double
    Dim iCol As Long
    Dim iRow As Long
    Dim C1 As Double
    Dim C2 As Double
    Dim C3 As Double
    Dim C4 As Double
    Dim F1 As Double
    Dim F2 As Double
    Dim C5 As Double
    Dim C6 As Double
    
    'search X
    iCol = 1
    Do While X >= XRange(1, iCol).Value And (iCol < XRange.Columns.Count)
        iCol = iCol + 1
    Loop
    
    'search Y
    iRow = 1
    Do While Y >= YRange(iRow, 1).Value And (iRow < YRange.Rows.Count)
        iRow = iRow + 1
    Loop

'if X or Y fall outside the table, the function result is an error value
    If (X < XRange(1, 1).Value) Or (X > XRange(1, iCol).Value) Or (Y < YRange(1, 1).Value) Or (Y > YRange(iRow, 1).Value) Then
        InterpolateXY = (1 / 0)
        Exit Function
    End If
    
    '4 data table points 'around' X and Y
    C1 = ValueTable.Cells(iRow - 1, iCol - 1).Value
    C2 = ValueTable.Cells(iRow - 1, iCol).Value
    C3 = ValueTable.Cells(iRow, iCol - 1).Value
    C4 = ValueTable.Cells(iRow, iCol).Value

'X and Y fractions
    F1 = (X - XRange.Cells(1, iCol - 1).Value) / (XRange.Cells(1, iCol).Value - XRange.Cells(1, iCol - 1).Value)
    F2 = (Y - YRange.Cells(iRow - 1, 1).Value) / (YRange.Cells(iRow, 1).Value - YRange.Cells(iRow - 1, 1).Value)

'2 intermediate results after interpolation on X
    C5 = C1 + F1 * (C2 - C1)
    C6 = C3 + F1 * (C4 - C3)
    
    'final result after interpolation on Y
    InterpolateXY = C5 + F2 * (C6 - C5)
    
End Function