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.
4
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 IntegertoAs Variant. - Specify the column number, since
Indexcan return an array.
Other recommendations:
- Change
ActiveWorkbooktoThisWorkbook. - Specify the type for
X. - Declare your variables.
- Add
Application.Volatileto force recalculation of the UDF, e.g. when a cell inB4:B6980is 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
6
This is a range.
Application.ActiveWorkbook.Sheets("K12").Range("B4:B6980")
Also, do not need “Application” above
Therefore you should set it like
Set K12_Values = ActiveWorkbook.Sheets("K12").Range("B4:B6980")
Then call the function.
Example:
Sub TestMacro()
Debug.Print GetK12(2)
End Sub
This code is tested.
0