| Microsoft Office 2000/Visual Basic Programmer's Guide | |
The Filter function works well for searching string arrays, but it's inefficient for numeric arrays. To use the Filter function for a numeric array, you have to convert all of the numeric elements to strings, an extra step that impairs performance. Then you must perform string-comparison operations, when numeric comparisons are much faster.
Although it's more involved, the binary-search algorithm performs efficient searching on a sorted array—whether numeric or string. The binary-search algorithm divides a set of values in half, and determines whether the value being sought lies in the first half or the second half. Whichever half contains the value is kept, and the other half is discarded. The remaining half is then again divided in half, and the process repeats until it either arrives at the sought value, or determines that it's not in the set. Note that the array must be sorted in order for this algorithm to work.
A detailed discussion of the binary-search algorithm is beyond the scope of this book. However, an example appears in the BinarySearch procedure in the modArrays module in VBA.mdb in the ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM. For an in-depth discussion of the binary-search algorithm, see the Visual Basic Language Developer's Handbook by Ken Getz and Mike Gilbert (Sybex, 1999).