Microsoft Office 2000/Visual Basic Programmer's Guide

Finding and Replacing Text Within a String

VBA provides another new function, the Replace function, which makes it easy to find and replace all occurrences of a substring within a string. The Replace function takes 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;