InStr Performance for VBA

I solved a mysterious bottleneck last night while working with large string values in MS Access. My VBA code was reading a file to a string variable, checking several values near the beginning of the string, and then manipulating and saving the string to the database.

Oddly, one of the slowest parts of my code was the several InStr calls that were only checking the first few hundred bytes of the string. I could alleviate part of the problem by copying the beginning of my file to a shorter string value, but in so doing I also noticed unexpected results from the InStr return value.

Cause #1 : Bad IDE Info

Any time the Parameter Info tooltip is displayed for the InStr function, this is what it shows…

InStr([Start], [String1], [String2], [Compare As VbCompareMethod = vbBinaryCompare])

This is wrong! According to the online documentation for the InStr function, the Compare parameter defaults to the setting specified by Option Compare.

Cause #2 : Option Compare

Any time a new VBA module is inserted by the MS Access IDE, it adds this one line of code at the top of the new module.

Option Compare Database

In the many years I’ve used MS Access and VBA, I never paid much attention to this. My testing last night showed that the performance of InStr depends on the length of both input strings rather than the position of the match, even if a match can be found as expected near the beginning of the subject string. This performance penalty depends on Compare Database which is not even the appropriate setting in this situation. It overrides the default argument to InStr and essentially performs a case-insensitive string search. The solution is simple.

Option Compare Binary

By changing that setting at the top of the module, I saw the runtime reduced by several seconds just to process the largest file I was working with. Overall performance roughly doubled.