'---------------------------------------------------------------------------------------
'
' P U R P O S E
'
' The function lin_xyz uses linear interpolation to find the value
' z(x=x_value,y=y_value) from a table (x, y, z).
' The table is arranged so that the 1st column and the 1st row contain x and y values,
' respectively.  The z values are at the intersection of its x and y values.
' The function will extrapolate beyond the table limit if the optional argument extrapolate is defined.
' The program assumes x and y in Table are in ascending order.
'
' I N P U T
'
' Table     = a range of cells in the worksheet that defines the table.
' x_value   = the x value where the z value is to be determined.
' y_value   = the y value where the z value is to be determined.
' extrapolate
'           = if omitted, will return #N/A if x_value or y_value is out of range.
'           = "bd", will return table bounded value if x_value or y_value is out of range.
'           = "et", will linearly extrapolate even x_value or y_value is out of range.
'
' O U T P U T
'
' lin_xyz   = the z value at (x=x_value, y=y_value).
'             For multiple x_values or y_values, the first pair will be returned.
'             If x_value or y_value is out of bounds, and argument extrapolate is omitted,
'                #N/A will be returned.
'             Otherwise, lin_xyz will return values based on the argument extrapolate.
'
'---------------------------------------------------------------------------------------
Function lin_xyz(Table As Object, x_value, y_value, Optional extrapolate)

Dim i, j As Integer             ' counter.
Dim ii, jj As Integer           ' <>0, if (x_value, y_value) found in table.
Dim z1, z2 As Single            ' temparary storage for z.
Dim extr, x_v, y_v

If IsMissing(extrapolate) Then
   extr = ""
Else
   extr = extrapolate
End If

ii = 0
For i = 2 To Table.Rows.count - 1
    If ((Table(i, 1) - x_value) * _
        (Table(i + 1, 1) - x_value) <= 0!) Then
        ii = i
        Exit For
    End If
Next i

jj = 0
For j = 2 To Table.Columns.count - 1
    If ((Table(1, j) - y_value) * _
        (Table(1, j + 1) - y_value) <= 0!) Then
        jj = j
        Exit For
    End If
Next j

If ii = 0 Or jj = 0 Then
   Select Case extr
   Case "bd"
      x_v = x_value
      y_v = y_value
      If ii = 0 Then
         If x_value < Table(2, 1) Then
            ii = 2
            x_v = Table(2, 1)
         ElseIf x_value > Table(Table.Rows.count, 1) Then
            ii = Table.Rows.count
            x_v = Table(Table.Rows.count, 1)
         End If
      End If
      If jj = 0 Then
         If y_value < Table(1, 2) Then
            jj = 2
            y_v = Table(1, 2)
         ElseIf y_value > Table(1, Table.Columns.count) Then
            jj = Table.Columns.count
            y_v = Table(1, Table.Columns.count)
         End If
      End If
      
      z1 = lin_2xy(Table(ii, 1), Table(ii, jj), _
                   Table(ii + 1, 1), Table(ii + 1, jj), x_v)
      z2 = lin_2xy(Table(ii, 1), Table(ii, jj + 1), _
                   Table(ii + 1, 1), Table(ii + 1, jj + 1), x_v)
      lin_xyz = lin_2xy(Table(1, jj), z1, _
                        Table(1, jj + 1), z2, y_v)
      
   Case "et"
      If ii = 0 Then
         If x_value < Table(2, 1) Then
            ii = 2
         ElseIf x_value > Table(Table.Rows.count, 1) Then
            ii = Table.Rows.count - 1
         End If
      End If
      If jj = 0 Then
         If y_value < Table(1, 2) Then
            jj = 2
         ElseIf y_value > Table(1, Table.Columns.count) Then
            jj = Table.Columns.count - 1
         End If
      End If
      
      z1 = lin_2xy(Table(ii, 1), Table(ii, jj), _
                   Table(ii + 1, 1), Table(ii + 1, jj), x_value)
      z2 = lin_2xy(Table(ii, 1), Table(ii, jj + 1), _
                   Table(ii + 1, 1), Table(ii + 1, jj + 1), x_value)
      lin_xyz = lin_2xy(Table(1, jj), z1, _
                        Table(1, jj + 1), z2, y_value)

   Case Else
      lin_xyz = [#N/A]
   End Select
Else
   z1 = lin_2xy(Table(ii, 1), Table(ii, jj), _
               Table(ii + 1, 1), Table(ii + 1, jj), x_value)
   z2 = lin_2xy(Table(ii, 1), Table(ii, jj + 1), _
               Table(ii + 1, 1), Table(ii + 1, jj + 1), x_value)
   lin_xyz = lin_2xy(Table(1, jj), z1, _
                     Table(1, jj + 1), z2, y_value)
End If

End Function