Search Item in Collection – VBA

In the previous article, we learned about the Collections in VBA. Today, we would implement the searching by checking if a particular item exists in a collection or not. We can also find the index for the searched item. The article is simple and easy to understand

So let us start learning.

How to Search Items in a Collection?

Grouping similar items make a Collection. Each item has an index that starts from 1, the second item has an index of 2, and the last item has an index of n, where n is the total number of items in the collection.

When we are searching for an item then we need to compare it with all the existing items in the collection.

You can download the practice workbook to follow along with the article.

Example – Creating and Adding Items to the Collection

We would start by creating a Module level collection and then adding items to the collection using a procedure as follows.

Run the additems Sub Procedure to add items to the Cars Collection. This Cars Collection has six items named Skoda, Porsche, Creta, Mercedes, Audi, and Honda City. Let us learn to access the items of this collection and print each item.

When you run the Sub Procedure named printitems, we see the names of items on the Immediate Window. Press Ctrl G to open the Immediate Window.

Searching an Item in the Collection

Now, we would write a function that returns a TRUE or FALSE depending on whether a particular item exists in the Cars Collection.

Function search(checkItem As String) As Boolean
'accessing the items of collection
For i = 1 To Cars.Count
    If checkItem = Cars.item(i) Then
        search = True      'if the item exists
        Exit For
    Else
        search = False
    End If
Next i
End Function

Now we call the search function using an example Sub Procedure.

Sub example()
Dim searchitem As String
searchitem = InputBox("Enter the car you are looking for:= ")
'calling the search function
MsgBox "Search Result is: " & search(searchitem)
End Sub

You need to run the example procedure. It works as follows.

If the search does not work properly, then use the printtems procedure to see if the items are added to the Cars collection. Incase,nohting prints on Immediate Window, then Run the additems to add the items to the Cars Collection.

This function would return True and False if the searched car is present in the collection or not.

Function – Search Item in Collection Passed As Function Parameter

If the Collection in which we are looking for the item is private, then we can pass it to the Search function as one of its parameters.

Use this search function when the Collection is to be passed to the Search Function.

Function search(mycoll As Collection, checkItem As String) As Boolean
'search the item in any collection
search = False
For i = 1 To mycoll.Count
    If checkItem = mycoll.item(i) Then
        search = True      'if the item exists
        Exit For
    Else
        search = False
    End If
Next i
End Function

We need to pass the collection as well as the item that we want to look for in that collection when we call the search function.

This function would work for any collection of VBA project

Getting the Index of Searched Items in the Collection

We can print the Index for the searched item in the Collection. If the searched item did not match any of the existing items in the Collection then the search index is zero.

This function returns the index for the searched item in the collection as follows.

Function search_index(mycoll As Collection, checkItem As String) As Integer
'get the index of searched item in a collection
search_index = 0
For i = 1 To mycoll.Count
    If checkItem = mycoll.Item(i) Then
        search_index = i     'if the item exists, then return the index for item
        Exit For
    End If
Next i
End Function

When we call this function, we need to pass the Collection as well as the searched item. The function returns the index of searched item.

Sub example()
'create a collection and add items
Dim books As New Collection
books.Add "It ends with us"
books.Add "It starts with us"
'input search item
Dim search_book As String
search_book = InputBox("Enter book name you wish to search:= ")
'calling search_index function
MsgBox "Search Index:= " & search_index(books, search_book)
End Sub

We would run example() to get the index for the searched book.

This brings us to an end.

Thank you for reading.