Faster Searches on Arrays, List Boxes, and Combo Boxes

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:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as