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.
Replace function parameter explained
For this purpose, VBA has the Replace function, which has the following parameters and return values.
Expression
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.
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 SubOutput/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 SubOutput/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 SubOutput/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 SubOutput/Result: Give grandpa opium, opium kills grandma!
