Jan 212014

Recently I encountered a little trap with the VB Replace() function. Beware that if you only use the required parameters, like…

strString = Replace(strString,"{find}","{replacewith}")

…it does a binary only comparison. This can muck up instances where you intend to replace a known string, but some instances may come up with mixed case.

W3 Schools has an excellent reference page on replace() here.

Binary only is case-sensitive. The solution is to use textual, which is case-insensitive:

strString = Replace(strString,"{find}","{replacewith}",1,-1,vbTextCompare)

The parameters 1,-1,vbTextCompare is as follows:

  • 1 means “start at position 1”
  • -1 means “find all instances”
  • vbTextCompare is a constant (literally 1), that tells the function this is a text comparison. It’s alternate is vbBinaryCompare.

This can save a headache later on when you’re dealing with an¬†scenario where case in strings are questionable, I would assume it’s always questionable unless you are looking explicitly for a binary match.

But I didn’t make VB. Oh well.