'name the tests and determine where the value to check is in the arrayĪPlace(1, 1) = 'Value Early:': aPlace(1, 2) = 1ĪPlace(2, 1) = 'Value Middle:': aPlace(2, 2) = 50000ĪPlace(3, 1) = 'Value Late:': aPlace(3, 2) = 99999 Public Declare Function timeGetTime Lib 'winmm.dll' () As LongĪnd finally, the sub to test loops through the early, middle, and late values-to-check and times them. I used the same API timer that JP uses when he does speed tests. Sub FillArray(ByRef vArr As Variant, ByVal lPlace As Long, ByRef sValue As String)

Then I pick one of the values (first, middle, and last) as the value I want to check. The code to fill the array converts Rnd to a string and puts it in the array. If InStr(1, vArr(i), sValueToCheck) > 0 Then The non-optimized method simply loops through the array and checks for values.įunction IsInArrayLoop(vArr As Variant, sValueToCheck As String, _ To test, I filled an array with 100,000 random strings, picked one of the strings to find, then timed JP’s funciton, my function, and the non-optimized method. SWordList = sDELIM & Join(vArr, sDELIM) & sDELIMīReturn = InStr(1, sWordList, sDELIM & sValueToCheck & sDELIM) > 0 'If exact match not needed, the line above provides the return value 'See if it's a match even if only a substringīReturn = UBound(Filter(vArr, sValueToCheck)) > -1 Optional bMatch As Boolean = True) As Boolean Here’s my code:įunction IsInArrayDK(vArr As Variant, sValueToCheck As String, _ I Join the array with delimiters around all the values, then use Instr to see if it’s there. JP has a good post about finding exact matches in arrays.

Vba find exact match