Finding Fault

Using the FIND method in your VBA macro is a good way to locate a specific piece of data in your workbook, and can be much more efficient than looping through an array of cells. For example, say you have a massive table of data, but each record is identified with a unique key of some kind. The fastest way to get a reference to a specific record is with FIND:

Set rngSearch = sht.Range("B1:B5000")
Set rngFind = Nothing
Set rngFind = rngSearch.Find(strKey)
If Not rngFind Is Nothing Then
   Result = rngFind.Row
End If

Unfortunately, this method can let you down if you're not careful, due to it's association with the Excel FIND menu command. The problem is that the function contains a bunch of optional parameters for which the default value is not predictable. As the VBA help file states:

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you donít specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

The above code might work perfectly for years, but then the user of the spreadsheet, on some totally unrelated project, uses the Find dialog to look for a number format or something. Now the Macro stops working - key values aren't being found. Depending on the data and what you're searching for, you might not even notice the problem, and simply think that the data isn't there... Maddening!

So, to avoid surprises, be sure to explicitly define ALL of the method's parameters EVERY TIME. The above code, rephrased, should look like this:

Set rngSearch = sht.Range("B1:B5000")
Set rngFind = Nothing
Set rngFind = rngSearch.Find(What:=strKey, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not rngFind Is Nothing Then
  Result = rngFind.Row
End If

Object Non-Existence Check - Trap

When you're writing code in VBA, you should stiffen up and prepare for trouble when checking for the NON-existence of an object. Why? Because this might actually trigger an error you weren't expecting or handling.


Consider this code which operates on Excel's menus:

On Error Resume Next
If NOT MenuBars(xlWorksheet).Menus("My App") Is Nothing Then...
  ...some code runs
End If

So what happens when this code executes and the menu we're looking for doesn't exist? Simply trying to reference a non-existant object triggers an error, and the code within the IF block will still run, believe it or not. The "On Error Resume Next" line makes that happen - any error moves execution to the next line, regardless of weather the IF condition was met. A proper error handler would avoid this problem.


Sometimes we can use this situation to our advantage. Lets say we want a set of unique values from a list. I like to use VBA's Collection mechanism with it's built-in Unique Key constraint to make this easy. You'll need an object Class to act as the data container:

'Class Module called "ItemClass" with a single Property
Public Key As String

Then create a collection and try adding everything. Only the ones that are new will be allowed in.

Dim colSet As New Collection
Dim clsItem As ItemClass
Dim rngCell As Range

On Error Resume Next
For Each rngCell In Range("A1:A999")

   Set clsItem = New ItemClass
   clsItem.Key = rngCell.Text

   'this line will simply fail if the key is already in use
   colSet.Add clsItem, clsItem.Key
Next

In this case we are deliberately ignoring the error that is triggered when we try to re-use an item's value as the collection key.

To Sum Up: watch out when your IF condition triggers an error, because all bets are off. You've got to know when to handle 'em, and know when to let 'em Resume Next.