How is the replace function used in VBA?

2 years ago
Jackson Davis
1 minute

The Replace function in VBA is used to replace a specified substring within a string. Its syntax is as follows:

Replace(expression, find, replace[, start[, count[, compare]]])

Parameter explanation:

  1. String to be replaced.
  2. Find: the substring to be searched for and replaced.
  3. replacement: the new string used for substitution.
  4. Start: an optional parameter that specifies the position to begin searching from, with a default of 1.
  5. count: an optional parameter that specifies the number of replacements to be made, with a default value of -1 indicating that all matches should be replaced.
  6. Comparison: an optional parameter that specifies the method of comparison, defaulting to vbBinaryCompare.

Can you lend me some money?
Would you be able to loan me some money?

Dim originalString As String
originalString = "apple, orange, banana, apple"
Dim newString As String
newString = Replace(originalString, "apple", "pear")
MsgBox newString

The code above will display a pop-up window showing “pear, orange, banana, pear”.

bannerAds