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 IfLoopYou 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
