First, your arguments are being passed ByRef by default. Try to pass them ByVal if possible -
Public Function getDataPoint(ByVal rowItem As String, ByVal searchCol As Long, ByVal colItem As String, ByVal searchRow As Long, ByVal shtName As String) As Variant
You'll also notice I changed the interger items to long. Integers - integers are obsoleteintegers are obsolete. According to msdn VBA silently converts all integers to long. I'd do the same for the other variables.
Speaking of variables - Variable names - give your variables meaningful names. Right now it's difficult to differentiate searchCol and colNum and colItem. Why not searchColumn, foundColumn and findColumnString or something along those lines so you don't need to go back to reference which is which.
Public Function getDataPoint(ByVal searchRowString As String, ByVal searchColumn As Long, ByVal searchColumnString As String, ByVal searchRow As Long, ByVal sheetName As String) As Variant
Dim foundRow As Long
Dim foundColumn As Long
Dim foundRowRange As Range
Dim foundColumnRange As Range
First, no need to handle the worksheet as a goto error when you can just use something like this -
If Not Evaluate("ISREF('" & shtName & "'!A1)") Then
Debug.Print "Sheet not found"
GoTo NotFound
End If
NotFound:
getDataPoint = "NOT FOUND"
End Sub
Now you can send all errors to one label and just debug.print when the error occurs. Or as suggested above - carry the debug.print as a string, which is clever.
To speed up search I'd take a look at this answer on stackoverflowthis answer on stackoverflow. Also, a good way to speed up your macro is to use Application.Screenupdating = False and Application.Calculation = xlManual and Application.EnableEvents = False. Just be sure to return them to True and xlAutomatic and True before exiting the sub.
One other idea I had was to use a vlookup and hlookup to get the row and column numbers to avoid a find. Then put them together for the data point.
