• 1 - Introduction
• 1.1 - Generating the Test Data
• 2 - Scope
• 2.1 - FIND
• 2.2 - MATCH
• 2.3 - Array Looping
• 3 - Results
• 4 - Conclusions

1. Introduction

Locating data in a range of values is an essential early step in most data processing projects and it often consists of taking the same steps to locate a dataset. Given the repetitiveness and similarity of such tasks, there are a few approaches that automate and help kickstart the process. Often one would jump between Find, Match and Looping arrays.

1.1 Generating the Test Data

Andreas Killer came up with a nice routine for generating test data. It was slightly modified so that we are able to provide the threshold when calling the function:

We randomly generate foo and bar in column 1 and column 2 in a range of 100 000 rows, with the number of foos and bars controlled by the value of dbl_threshold.

Changing the dbl_threshold to a higher number will give few foos and bars, and changing it to a smaller value will give lots.

Since the foo and the bar are using different random numbers there will be rows with foo but no bar and bar but no foo, as well as rows with both foo and bar.

This makes it easy to test how the various methods compare with different densities of data.

2. Scope

Using the VBA function Timer - we will time how much time will it take to find every pair in the existing range.

The Timer function returns fractional portions of a second.

2.1 FIND

This Excel VBA function will loop over all of the sheets in an activated workbook and map their values to an array. It's your responsibility to activate the correct workbook prior to starting the function. The function will initialize and change the contents of an array defined by the user. The function call should be done in this way:

2.2 MATCH

This Excel VBA function will extract the headers from an active sheet into an array. It's your responsibility to activate the correct workbook and sheet prior to starting the function. The function will initialize and change the contents of the array defined by the user. The user has the possibility to pick a predefined start cell or a whole range. The whole range can be chosen manualy if bPickWholeRange is set to True. The function call should be done in this way:

2.3 Array Looping

This Excel VBA function will extract the table of data from an active sheet into an array. It's your responsibility to activate the correct workbook and sheet prior to starting the function. The function will initialize and change the contents of the array defined by the user. The user has the possibility to pick a predefined start cell or a whole range. The whole range can be chosen manualy if bPickWholeRange is set to True. The function call should be done in this way:

3. Results

This Excel VBA function will create a new sheet with a user-defined name. The user must activate the desired workbook prior to calling the function. This function doesn't check if a sheet with the same name already exists and will halt the macro if a check hasn't been performed. The function call should be done in this way:

4. Conclusions

The implementation of the following functions can greately reduce execution times while providing enough flexibility to transform and validate data.