index function returning #Value in UDF

Question

Public Function GetK12(X) As Integer
    
    K12_Values = Application.ActiveWorkbook.Sheets("K12").Range("B4:B6980")
    GetK12 = Application.Index(K12_Values, X)
End Function

Calling this from my worksheet returns a #VALUE error. I have validated the variables. An INSERT-MATCH works on this variable elsewhere. What am I doing wrong here? [this is a snippet I tested. NOT the complete function]

SOLVED: Thank you all. Yeah I had to fiddle with the variable types.

Answer 1

To debug this, call it from a Sub:

Sub tester()
    Debug.Print GetK12(1)
End Sub

You'll likely get a Type Mismatch error, and your likely fix is multi-part:

• Change As Integer to As Variant.
• Specify the column number, since Index can return an array.

Other recommendations:

• Change ActiveWorkbook to ThisWorkbook.
• Specify the type for X.
• Declare your variables.
• Add Application.Volatile to force recalculation of the UDF, e.g. when a cell in B4:B6980 is changed.

Public Function GetK12(ByVal X As Long) As Variant
    Application.Volatile

    Dim K12_Values As Variant    
    K12_Values = ThisWorkbook.Worksheets("K12").Range("B4:B6980").Value
    GetK12 = Application.Index(K12_Values, X, 1)
End Function