# Handling errors with minimal repetition when using Find function

I have a function that essentially searches out a value in a table by looking through the column headers and row headers to locate a data point. The function takes the sheet name, the column header name, and row header name as arguments, and I have an error handler set up if any one of the methods performed with those variables fails. However, each handler is very similar, so there is much repetition. Is there a more clear way to have all errors handled uniquely but not to have to re-write Exit Function and other such things?

Function getDataPoint(rowItem As String, searchCol As Integer, colItem As String, searchRow As Integer, shtName As String) As Variant

Dim rowNum As Integer, colNum As Integer
Dim rowNumRng As Range, colNumRng As Range

'Find worksheet to search
On Error GoTo shtNotFound:
With ThisWorkbook.Sheets(shtName)

'Find rowNum where rowItem appears in searchCol
On Error GoTo rowNotFound:
Set rowNumRng = .Columns(searchCol).Find(What:=rowItem, _
After:=.Cells(1, searchCol), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)

rowNum = rowNumRng.Row

'Find colNum where colItem appears in searchRow
On Error GoTo colNotFound:
Set colNumRng = .Rows(searchRow).Find(What:=colItem, _
After:=.Cells(searchRow, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)

colNum = colNumRng.Column

getDataPoint = .Cells(rowNum, colNum).Value

End With
On Error GoTo 0
Exit Function

shtNotFound:
Exit Function
rowNotFound:
Exit Function
colNotFound:
Exit Function

End Function


If you absolutely need to use your Find approach, or if you'd just like to see a centralized error handler, as per you question, then you can use a variable to store the custom error message, and then refer to that variable from the error handler:

Also, in you On Error statement, you don't need the trailing ":" as that indicates that the line will have another statement following the ":"

Function getDataPoint(rowItem As String, searchCol As Integer, colItem As String, searchRow As Integer, shtName As String) As Variant

Dim rowNum As Integer, colNum As Integer
Dim rowNumRng As Range, colNumRng As Range
Dim sError As String

'Find worksheet to search
On Error GoTo itmNotFound
With ThisWorkbook.Sheets(shtName)
'Find rowNum where rowItem appears in searchCol
Set rowNumRng = .Columns(searchCol).Find(What:=rowItem, _ After:=.Cells(1, searchCol), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=True)
rowNum = rowNumRng.Row
'Find colNum where colItem appears in searchRow
Set colNumRng = .Rows(searchRow).Find(What:=colItem, _ After:=.Cells(searchRow, 1), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True)
colNum = colNumRng.Column
getDataPoint = .Cells(rowNum, colNum).Value
End With
On Error GoTo 0
Exit Function

itmNotFound:
Debug.Print sError

End Function

• This won't ever set sError if I'm reading this correctly. – RubberDuck Mar 17 '16 at 9:14
• hmm, other than Markdown mangling the line breaks in the Find methods, the code is good, and sError is set and printed when the row, column or sheet is not found. sError is a prescriptive string, so it's being set before the error occurs. If no error occurs, sError is never set, and never debug.printed. – ThunderFrame Mar 17 '16 at 10:14
• Ahh ha! I see now. My bad. – RubberDuck Mar 17 '16 at 10:21

Find is a very slow method for doing what you need.

You will achieve much greater performance, and avoid the need for complex error handling, by reading the search range into an array, and looping over the first dimension to find the row, then looping over the second to find the column.

Edit: Depending upon the number of rows and columns that you expect, or perhaps even after inspecting the size of the search array, you could squeeze out some extra performance by checking to see if there are fewer columns than rows and then check if the column exists, before checking if the row exists. And if there are fewer rows than columns, then check to see if the row exists before checking if the column exists.

Edit2: if you'll be calling this function multiple times, with similar sheet names, row values or column values, you could optimize further by keeping Scripting.Dictionarys of column names and indexes, and row names and indexes. That would be the fastest approach.

• Thanks for the tips. Also, what would be the advantage to using Scripting.Dictionary rather than a collection? – teepee Mar 16 '16 at 21:41
• Scripting.dictionary has an Exists method and uses a hash table behind the scenes, so it will be faster at determining whether a column or row exists, before you try to index it. – ThunderFrame Mar 16 '16 at 21:46
• OK that's good to know, I'll give that a shot. But I'm wondering still about the error handling aspects, any thoughts? – teepee Mar 16 '16 at 21:48
• See my other answer – ThunderFrame Mar 16 '16 at 21:56
• So I have posted an answer based on your advice about creating an array to search. This appears to be noticeably faster than using find (7e-3 sec. rather than 1.5e-2 sec.). Is this what you had in mind? – teepee Mar 17 '16 at 21:51

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 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
GoTo NotFound
End If

NotFound:

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 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.