Faster Searches on Arrays, List Boxes, and Combo Boxes

This method is handy when the user enters data that you need to validate against a lookup table. Validating the value by querying the database is out of the question because of the relatively slow response time. An alternative is to keep the lookup table locally in a sorted array, or in a list-box or a combo-box control whose Sorted property is set to True. Lookup tables are static, so you don’t need to worry about the local copy being out of sync with the database. For large sorted arrays of strings or for list-box or combo-box controls with large sorted lists (in the range of 10,000 entries), you will find the binary search to be 10 to 20 times faster than calling the API, and hundreds of times faster than a sequential search. The difference becomes dramatic if the operation needs to be done multiple times. If you want to use an array instead of a combo-box or a list-box control, there is no API call you can use. Use this code for an array:

 'In:'Array to searchDim rasArray() As String'String to search forDim vsName As String	'Out:'Index in the array of the string if foundDim rlIndex As Long'Local variables:'Index in the arrayDim lnIdx As Long'Lower bound of the search intervalDim lnMin As Long'Upper bound of the search intervalDim lnMax As Long'Return an invalid index, if string is not found rlIndex = LBound(rasArray) - 1lnMax = UBound(rasArray)lnMin = LBound(rasArray)'lookup vsName in rasArray()Do While lnMin <= lnMax	lnIdx = (lnMax + lnMin)  2	If vsName = rasArray(lnIdx) Then		rlIndex = lnIdx		Exit Do	ElseIf vsName < rasArray(lnIdx) Then		lnMax = lnIdx - 1	Else		lnMin = lnIdx + 1	End IfLoop

You can easily modify this code for a combo-box control to use with list-box controls as well:

 'In:'Combo to search' (change into As ListBox for listbox controls' or use As Controls to use with both types ' of controls)Dim rcboCombo As ComboBox'String to search forDim vsName As String'Out:'Index in the combo if the string is foundDim rlIndex As Long'Local variables'Index in the arrayDim lnIdx As Long'Lower bound of the search intervalDim lnMin As Long'Upper bound of the search intervalDim lnMax As Long'Return an invalid index, if string is not foundrlIndex = -1  lnMin = 0lnMax = rcboCombo.ListCount - 1lnIdx = lnMax  2'lookup name in the comboDo While rlIndex = -1 And lnMin <= lnMax	If vsName = rcboCombo.List(lnIdx) Then		rlIndex = lnIdx	ElseIf vsName < rcboCombo.List(lnIdx) Then		lnMax = lnIdx - 1		lnIdx = (lnMax + lnMin)  2	Else		lnMin = lnIdx + 1		lnIdx = (lnMax + lnMin)  2	End IfLoop
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: