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.
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:
As Integer
to As Variant
.Index
can return an array.Other recommendations:
ActiveWorkbook
to ThisWorkbook
.X
.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