VBA Replace String, Text (Replace Function)

VBA is wonderful for automating various things in the Microsoft Office world and creating some nice tools for working more effectively.

A typical case that occurs not only in VBA, but also in many other programming languages, is the replacement of a text by another text. The replacement can be complete, or only a substring (partial text) in the original string is to be replaced. For example, this could be a file extension in a file name that is to be substituted.

Table of content

Replace function parameter explained

For this purpose, VBA has the Replace function, which has the following parameters and return values.

Parameters of the Replace function

Expression

This is the original string/text which is to be searched for another string.

Find

This is the text or text fragment (substring) to be found in the original text.

Replace

This is the text with which the searched text is to be replaced.

Bestseller No. 1

VBA lernen: Das Aufgaben- und Lernbuch zur...

Martin, René (Author)

start

This is an optional parameter. Here, you can specify at which point/position in the original text the search and replace should start. It is a numerical value. The first letter starts at 1. This is a bit unusual, as in other programming languages it always starts at zero. The default value is 1.

Count

Also, an optional parameter. If the substring you are looking for occurs several times in the original text, you can specify here how often the substring should be replaced. This is practical if you only want to replace the first occurrence of a string.

Compare

This numerical parameter, which determines the comparison method, is rarely needed. The default value is sufficient here. Otherwise, there is

[-1] vbUseCompareOption

When using this parameter, an option-compare statement must precede the respective procedure.

[0] vbBinaryCompare

It compares the binary representation of searched string and searched string.

[1] vbTextCompare

A text comparison is made between the searched string and the searched string.

[2] vbDatabaseCompare

Used only in Microsoft Access for comparisons within databases.

Return value

The return value is the new string with the replaced parameters.

Application examples Replace function in VBA

Simple example without optional parameters

Here, all substrings found are substituted with the text to be replaced.

Private Sub ReplaceExample1()
  Dim strOriginal As String.
  Dim strSearch As String
  Dim strReplace As String.
  Dim strNew As String

  strOrignal = "My grandma rides a motorbike in the chicken coop!"
  strSearch = "Motorcycle"
  strReplace = "Tricycle"

  strNew = Replace(strOrignal, strSearch, strReplace)
  'Output to direct window
  Debug.Print strNew
End Sub

Output/Result: My grandma rides a tricycle in the chicken coop!

Replace only certain number in string

In this example, only one occurrence of the searched text is replaced because the optional parameter Count has been defined.

Private Sub ReplaceExample2()
Dim strOriginal As String.
Dim strSearch As String
Dim strReplace As String.
Dim strNew As String

strOrignal = "If you give opium to Opi, opium will kill Opi!"
strSearch = "Opi"
strReplace = "Omi"

strNew = Replace(strOrignal, strSearch, strReplace, Count:=1)

Debug.Print strNew
End Sub

Output/Result: Give Omi opium, opium kills opi!

Search string from certain position

This example shows how a string is searched from a certain positione (20) and the occurrences of the searched string are replaced. Only the string from the start position is then returned.

Private Sub ReplaceExample3()
Dim strOriginal As String.
Dim strSearch As String.
Dim strReplace As String
Dim strNew As String

strOrignal = "If you give opium to opi, opium will kill opi!"
strSearch = "Opium"
strReplace = "Omi"

strNew = Replace(strOrignal, strSearch, strReplace, Start:=20)

Debug.Print strNew
End Sub

Output/Result: kills grandma grandpa!

Replace last occurrence of a string

The optional parameters Count and Start are not particularly good for replacing the last occurrence of a character or string, as you never know how long the orignal string is. To replace the last occurrence of a string in a string, one must use the strReverse function. This simply reverses the string as if you were reading it backwards. Our “Opi” then becomes “ipO”. This is how you can make a substitution from the back.

Private Sub ReplaceExample4()
Dim strOriginal As String.
Dim strSearch As String.
Dim strReplace As String.
Dim strNew As String

strOrignal = "If you give opium to Opi, opium will kill Opi!"
strSearch = "Opi"
strReplace = "Omi"

strNew = StrReverse(Replace(StrReverse(strOrignal), StrReverse(strSearch), StrReverse(strReplace), Count:=1))

Debug.Print strNew
End Sub

Output/Result: Give grandpa opium, opium kills grandma!