Excel VBA Compile Error: Invalid Qualifier

Question

I am working with excel vba 2013. I am trying to develop a 2D interpolation function. The error I get is: "Compiler error: Invalid Qualifier" with the Function line highlighted in yellow.

I believe my problem has to do with "knownZ" and how I reference it later in the code. Please note that it is enough to use the first 3 lines and part I from the code to get the error. The rest of the code is listed for those interested.

Here is my code:

Option Explicit

Function LinIntrpAsc2D(KnownX As Range, KnownY As Range, KnownZ As Range, X2 As Double, Y2 As Double) As Variant


Dim Y0 As Double, Y1 As Double, X0 As Double, X1 As Double, i As Long, j As Long, Z() As Variant, Z1 As Double, Z2 As Double, Xfound As Boolean, Yfound As Boolean


'------------------------------------------------------------------------------
'I. Check Preliminary Error
'------------------------------------------------------------------------------
If WorksheetFunction.Or(KnownX.Column.Count <> KnownZ.Column.Count, KnownY.Row.Count <> KnownZ.Row.Count) Then
    LinIntrpAsc2D = "Number of rows or columns for given range does not match."
    Exit Function
End If

'------------------------------------------------------------------------------
'II. Interpolation
'------------------------------------------------------------------------------
Xfound = False
Yfound = False

For i = 1 To KnownX.Column.Count
    If X2 = KnownX.Cells(i) Then
        Xfound = True
        GoTo 1
    ElseIf KnownX.Cells(i) < X2 Then
        X0 = KnownX.Cells(i)
    ElseIf KnownX.Cells(i) > X2 Then
        X1 = KnownX.Cells(i)
        GoTo 1
    End If
Next i
1:
For j = 1 To KnownY.Row.Count
    If Y2 = KnownY.Cells(j) Then
        Yfound = True
        GoTo 2
    ElseIf KnownY.Cells(j) < Y2 Then
        Y0 = KnownY.Cells(j)
    ElseIf KnownY.Cells(j) > Y2 Then
        Y1 = KnownY.Cells(j)
        GoTo 2
    End If
Next j
2:

If WorksheetFunction.And(Xfound = False, Yfound = False) Then
    Z(1, 1) = KnownZ(j - 1, i - 1)
    Z(1, 2) = KnownZ(j - 1, i)
    Z(2, 1) = KnownZ(j, i - 1)
    Z(2, 2) = KnownZ(j, i)

    Z1 = (X2 - X0) * (Z(1, 2) - Z(1, 1)) / (X1 - X0) + Z(1, 1)
    Z2 = (X2 - X0) * (Z(2, 2) - Z(2, 1)) / (X1 - X0) + Z(2, 1)
    LinIntrpAsc2D = (Y2 - Y0) * (Z2 - Z1) / (Y1 - Y0) + Z1
    Exit Function

ElseIf WorksheetFunction.And(Xfound = False, Yfound = True) Then
    Z(1, 1) = KnownZ(j, i - 1)
    Z(1, 2) = KnownZ(j, i)
    LinIntrpAsc2D = (X2 - X0) * (Z(1, 2) - Z(1, 1)) / (X1 - X0) + Z(1, 1)
    Exit Function

ElseIf WorksheetFunction.And(Xfound = True, Yfound = False) Then
    Z(1, 1) = KnownZ(j - 1, i)
    Z(2, 1) = KnownZ(j, i)
    LinIntrpAsc2D = (Y2 - Y0) * (Z(2, 1) - Z(1, 1)) / (Y1 - Y0) + Z(1, 1)
    Exit Function

ElseIf WorksheetFunction.And(Xfound = True, Yfound = True) Then
    LinIntrpAsc2D = KnownZ(j, i)
    Exit Function
End If
End Function

The code I use inside the excel worksheet is =LinIntrpAsc2D(C19:P19,B20:B32,C20:P32,1250,3.5) Please note that "KnownX" is a 1D horizontal list of cells while "KnownY" is the same as "KnownX" but vertical. "KnownZ" is a 2D range of cells.

Please note that when I remove all "KnownZ" references the error is no longer generated.

I have tried searching for information on ranges and arrays but could not find the problem with my code! Any help at all would be greatly appreciated, even if it is a link to a specific page I should read. Thank you for your time!

Answer 1

When the error message first pops up (and before any line is highlighted) you should see this:

Note that .Column is highlighted. The problem is that .Column returns a long (the index of the first column in the spreadsheet). If you want the collection of all columns you would need to use .Columns: KnownX.Columns.Count (with similar fixes elsewhere in the code).

It is a bit misleading how after you dismiss the error message, the first line of the function is the highlighted line (when you are calling the function as a UDF).