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.