How to Check If a Collection Contains an Object

Anyone who tells you they didn’t love this song is lying

How do you determine whether an object is a member of a Collection?

Folks with experience in other programming languages would probably assume that this functionality is built into VBA… And unfortunately, those folks would be wrong.

If you need a BULLETPROOF way to see if your Collection contains something (like a String, a Range, a Worksheet, etc.) look no further! The following Contains function:

1. Attempts to find the object using a Key
2. If no Key is provided, it loops through the Collection, checking each Item
3. If no Key or Item is provided, it defaults to False

Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.

Let’s dive in using our 4-step VBA process:

Step 1 – Setup
Step 2 – Exploration
Step 3 – Execution
Step 4 – Cleanup

The Step 1 – Setup in this function is minimal, and takes place entirely on lines 9 and 10 with the declaration of our variables: strKey and var. More on them later!

Step 2 – Exploration comes up in a hurry. Much like the set up, our Exploration phase is really short — in fact, it is essentially a single If / ElseIf / Else statement!

This statement starts on line 13, where we check to see if a Key was passed-in to the function. The ElseIf statement starts on line 39, where we check to see if an Item was passed-in to the function. Finally, on line 50, we default the Contains function to False if no Key or Item was passed-in. Easy cheesy!

So far so good!

Step 3 – Execution is where things start to get interesting. As we said earlier, there are three branches our code can take:

1. If the user passed-in a Key, we’ll use that to check the Collection
2. If the user passed-in an Item, we’ll loop through the Collection, checking each contained item to see if it matches the passed-in Item
3. If neither a Key or an Item is passed in, we return False by default

Let’s start with the easiest one first and work our way up!

3. If neither a Key or an Item is passed-in

This one’s a cinch. If the user calls Contains without passing in a Key or Item, we return False — lines 52 and 53 take care of that. Done!

2. If the user passed-in an Item

If the Collection in question does not make use of the (admittedly optional) Key parameter, then we will need to loop through the Collection and check each of its items against the passed in Item.

We tackle this on lines 41-49.

We start on line 41 by assuming that the passed-in Item will not be found, meaning Contains will return False. Don’t worry — we’ll set Contains to True if we do find the passed-in Item!

Collections support the For Each…Next loop construct with a Variant-type iterator, which we kick off on line 44. (That’s the var variable in this case.)

On line 45, we are checking each item (var) in the Collection to see if it matches the passed-in Item. As soon as a match is found, we set the Contains function to return True (on line 46) and exit (on line 47)!

1. If the user passed-in a Key

Jackpot — this one is absolutely the most fun of all, and it executes between lines 15 and 36.

The first thing we do is convert Key, which is technically a Variant, into a String on line 15. (Collections only allow for String-type keys, but we declare the optional input Key as Variant to allow the user to skip it if need be.)

Take a deep breath y’all — we’re about to enter Error Handling country…

On line 18, we use On Error Resume Next to ensure that VBA does not stop executing the code if an Error occurs. Why though?

Why are we using On Error Resume Next?

Fortunately for us, the assignment on line 20 will (potentially) generate very predictable errors.

We start off this section by assuming the Key will be found, which is why we set Contains to True on line 19.

On line 20, as mentioned a moment ago, we attempt an assignment that might generate error.

On line 21, we check to see if Err.Number is 91 (which means an error occurred and the error number is 91). If so, the assignment itself failed! VBA throws this error when you attempt to assign an object without using the Set keyword — for example, if you wrote wks = ThisWorkbook.Sheets(1) instead of Set wks = ThisWorkbook.Sheets(1).

If the assignment failed, we jump to line 26 (CheckForObject), where we use the IsObject function on line 27 to properly check if an object (like a Worksheet or Workbook, for example) is stored in the Collection. If so, we can set Contains to True and exit!

What if we did not get error 91 though, and instead got error 5? (The error 5 case is what we are checking on line 22.)

VBA will throw a 5 error when the specified Key is not found in the Collection. In that case, we jump to line 33, where we set Contains to False and exit. Nice!

What if neither error 5 nor error 91 occurred? Well, in that case, we reset the error handling with On Error GoTo 0 on line 23 and exit the function with Contains still set to True, the way it was on line 19.

Phew! For such a small task, there were many edge cases to consider — fortunately, covered them all here and can use this function with confidence!

If you’d like to do some of your own checking on the Contains function, you can use my comprehensive test file to be 100% sure about how Contains works:

https://gist.github.com/danwagnerco/d1cf423c7a23b95949b97fe39cf03163

In the 9-minute video below, I do exactly that — walk through each test case!

Checking collections with confidence? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.