Replacing Text Within a String

Microsoft® Visual Basic® for Applications (VBA) provides another function, the Replace function, which makes it easy to find and replace all occurrences of a substring within a string. The Replace function takes up to six arguments: the string to be searched, the text to find within the string, the replacement text, what character to start at, how many occurrences to replace, and a constant indicating the string-comparison method. You don't even have to write a loop to use the Replace function — it automatically replaces all the appropriate text for you with one call.

For example, suppose you want to change the criteria for an SQL statement based on some condition in your application. Rather than re-creating the SQL statement, you can use the Replace function to replace just the criteria portion of the string, as in the following code fragment:

strSQL = "SELECT * FROM Products WHERE ProductName Like 'M*' ORDER BY ProductName;"
strFind = "'M*'"
strReplace = "'T*'"
   
Debug.Print Replace(strSQL, strFind, strReplace)

Running this code fragment prints this string to the Immediate window:

SELECT * FROM Products WHERE ProductName Like 'T*' ORDER BY ProductName;

See Also

Getting the Most Out of Visual Basic for Applications | Working with Strings | Comparing Strings | Calculating String Length | Searching a String | Returning Portions of a String | Working with Strings as Arrays | Converting Strings | Working with String Variables