- Concept and How it Works
- Additional Reading
- Other Notes
- How Did it Work for You?
Today’s post introduces a Google-like search, with search-as-you-type suggestions. While it’s possible to run basic searches with VLOOKUP and other techniques, this VBA approach can provide immediate search suggestions or recommendations for the user.
Sites like Google, Yahoo, and Amazon have implemented a number of features to assist users during searches. Most notable—over the past 15 years—is the incorporation of asynchronous updates in searches. This permits search suggestions to be returned as a user types a search query. Prior to this feature, searching Google involved entering a query into a search bar, hitting the submit button, then waiting for results to load.
We can thank AJAX for popularizing this feature in the mid-2000s. AJAX provided asynchronous updating of web pages, permitting data to be received from the web server without loading a new page. Prior to AJAX, the internet was largely made up of static pages that relied on full-page refreshes to display dynamic content.
I did an experiment last week to see if I could mimic this feature in Excel. The goal was to see if Excel could search a set of data simultaneously (or at least seemingly so) as I typed the data into a search bar, narrowing the search results as I came closer to the full spelling. I created a fairly fast and efficient solution. This has been built to be easily to implemented in your own projects by creating a collection class that can be imported. You will only need to create two small VBA subroutines to support the collection class. I’ll guide you in the Implementation section blow. First, download the solution here and learn more about how it works.
Macros must be enabled upon opening.
Concept and How it Works
Here’s a quick video demonstrating the Google-like search in action — a search query through a list of the world’s 3,000 largest cities.
Central to this process is an event linked to the ActiveX Textbox control. I’ve posted previously about using worksheet events to trigger VBA procedures. The common worksheet events are:
- Selecting a cell
- Changing a cell’s value
- Selecting a worksheet
Worksheet events will only trigger once after the user performs a single action. The user must select a cell, worksheet, or workbook for the event to work. For this project, I need an event that triggers continuously after each keystroke. You may think that a
Worksheet_Change event may accomplish this, but that event is only triggered after a user updates a cell. We’ll need to get a bit more creative if we want an event triggered as a user inputs data. This is where events associated with ActiveX controls will come in handy.
The ActiveX Text Box
ActiveX controls have a lot of detractors in the Excel/VBA community. I will agree that ActiveX controls host more glitches than Windows Vista and sometimes have a mind of their own, but there are some valuable features that make them work very well with VBA. One feature of the ActiveX textbox that’s essential to this project is the
TextBox_Change event. Unlike the
Worksheet_Change event (which will only trigger after a user has updated data and left the cell), the
TextBox_Change event will fire whenever a user adds or removes a character from the textbox — effectively every keystroke. This feature permits us to run the search procedures seemingly while a user enter data into the text box.
The VBA Procedures
Now that we’ve figured out how to leverage the ActiveX textbox to trigger an event for every keystroke, we can call a script to handle the search procedures. In its simplistic form, there are two primary actions that occur in the search procedures:
- If this is the first time the script is running since workbook open: to make the search function operates efficiently, all data is loaded into a global collection class. The data is pulled from a spreadsheet as if it were being pulled from a database. In the example file, the two sheets containing data are “Country Data” and “City Data.” This approach utilizes a technique discussed in my post about saving (persisting) custom object data after workbook close. This operation will only occur during the first use of the search box. Given this initial class loading, you will notice a slight delay—depending on how much data exists—after entering the first character.
- For all subsequent times the textbox is used: A procedure within the collection class will loop through the entire collection and match the search criteria using the
InStrfunction, which will return the location of a search string if it’s found within the accompanying query string value.
I’ve set this project up to be easily transferable to any macro enabled workbook. There are only 5 steps required for implementation, 3 of which do not require any coding.
Step 1: Import Classes
TermList classes to your workbook. These classes will not require any modification to work as presented in the example file. I’ve included the exported class modules as downloadable files here, or they can be copied/pasted from the example file.
Step 2: ActiveX TextBox
Add an ActiveX TextBox to the sheet where you want search results to appear. This can be done through the Developer tab.
Step 3: Insert Searchable Data
Include the searchable dataset in a separate spreadsheet in your workbook. The data must start in cell A2 (including header labels is fine). The class is set up to handle any size dataset, unlimited for both rows and columns, but extremely large datasets may compromise speed.
With some modifications to the class, you could have a dataset pulled from external sources such as an Access database or the web. This will not be discussed in this post.
Step 4: Create ActiveX TextBox Change Event
This is the first VBA step. We must add a
TextBox_Change event for every search textbox in your workbook. The event procedure must be located in the VBA worksheet module which contains the associated textbox. This easiest way to add this is by double clicking on the textbox control while in Design Mode. This will create a properly named empty subroutine within the correct workbook module. In the example file, the
TextBox_Change event for the country search is as follows:
Private Sub Country_Search_Box_Change()
Application.ScreenUpdating = False
If countryList Is Nothing Then ' Update global object name to your custom object.
Application.ScreenUpdating = True
The lines which require modification are highlighted.
- Line 4: change object name countryList to the name of your custom object (the name will be created in step 5, next).
- Line 5: change the
Country_Search_Focus_Opsto a custom name you create (also will be created in step 5, next).
- Line 7: change object name countryList to the name of your custom object, and change the the argument
Country_Search_Box.Valueto pull the value from your search textbox. Note: you can change the name of your textbox in the name field while the textbox is active in design mode. This is done similarly to how you would change a named range, but the textbox must be the selected object.
Step 5: Customize Search Functionality
This is the second (and final!) VBA step. This step permits you to customize the search functionality to your specific needs. I explain the behavior of each custom feature below.
Set .sourceSheet = ThisWorkbook.Sheets("Country Data")
Set .destSheet = ThisWorkbook.Sheets("Country Interface")
Set .anchorRange = Range("Country_Dest_Range")
.maxResults = 15
.smartSort = True
.alphaSort = True
- Line 1: Change name of global public object countryList to a name you desire. Create as the collection class data type,
- Line 3: Name the subroutine as you desire. A workbook can contain multiple search datasets, in which case you should use a name unique to this specific search operation.
- Line 5: Instantiate the global public object. Change countryList to the name you picked for line 1.
- Line 7: Change countryList to the name you picked for line 1.
- Lines 8-14: Update class properties to customize search behavior. Properties and their impact are as follow:
- sourceSheet: A worksheet object which identifies where the source (searchable) data is located.
- destSheet: A worksheet object which identifies where the search results should be displayed.
- anchorRange: A cell address or single-cell named range in the destination sheet (destSheet) identifying where to anchor the top left of the search results.
- maxResults: Enter the maximum number of search results to display. For no limit to search results, enter 0 (zero). Default value is 0.
- smartSort: True will prioritize search results where first characters match. False will display results in dataset order, regardless of where the query string sits within the search string. Default value is True.
- alphaSort: True will sort the left-most values in the dataset as it’s loaded into the collection class. False will not sort the data and results will be displayed in the order they exist within the dataset. Warning: this will result in a significant lag if set to True for large datasets. As an alternative, you can simply sort the dataset to your requirements on the worksheet. Default value is False.
- clearOnEmpty: True will display no data if the search textbox is empty. False will display the complete dataset if the search textbox is empty. Default value is True.
I won’t be doing a detailed VBA analysis on the class scripts, but here’s a quick overview with links to relevant posts for additional material.
- To permit the class to collect as many columns of data that are available, I include a property within each class to store an array. To learn more, read my post about storing arrays to class properties.
- While collection classes are known to have standard member subroutines to manage the collection (Add, Item, Count, Remove), additional member subroutines can be added to support complex operations. To learn more, read my post on building subroutines within collection classes.
- The collection class can sort items within the collection in alphabetical order. To learn more, read my post on using bubble sort within a collection class.
- In addition to the advanced topics and techniques previously mentioned, this project utilizes basic VBA concepts such as multidimensional arrays, For loops, Do loops, If statements, and collection classes.
- A single workbook can contain any number of search boxes. As you can see from the example file, the single workbook contains two searches. Each search is instantiated as its own object. You will also need a unique ActiveX textbox, event procedure, and focus procedure for each search.
- In the sections above, I mention that these procedures will “seemingly” run as users enter data. While it may seem simultaneous, the procedures are actually running after each keystroke. So it’s not exactly asynchronous, even though it may seem so. If too much data is included in the dataset, there will be a noticeable pause between each keystroke.
How Did it Work for You?
Let me know in the comments. Did you incorporate this into one of your VBA projects? Does it work efficiently with your data? Did you make any modifications for your specific needs?