How to use Replace Function in Excel VBA Easily (2 Type)

Welcome to Excel Avon

Replace Function in Excel VBA?

DOWNLOAD USED EXCEL FILE FROM HERE>>

So, as you know that Excel has Replace function in the same way VBA has Replace function today’s post How to use Replace Function in Excel VBA Replace Function in VBA is a very useful function. Replace function can find and replace any word or character or sentence with any letter. But by that process we can change only one type of sentence or letter at a time.
With the help of VBA replace function, we can replace multiple words or sentences at once. This saves a lot of time doing the same activity multiple times.

To understand for Replace Function in Excel VBA, we have to go to VBE Like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

Replace Function Syntax

Replace (Expression as string, Find as String, Replace as String, [start as Long = 1], [Count as Long = 1], Compare as  vbCompareMethod = vbBinaryCompare)as string

Expression: The string to replace a sequence of characters with another set of characters.

Find: The word or string that will be searched for in Expression.

Replace: It will replace find in Expression.

Start [Optional]:  This is the position in string 1 to start the search. If default, the Replace function will start the search at position 1.

Count [Optional]: the number of occurrences to replace. If default the Replace function will replace all occurrences of find with replacement.

Compare [Optional]: Since we don’t use the compare argument, skip this optional argument.

To understand REPLACE Function in Excel VBA, we will write a subroutine for Split Function in Excel VBA after inserting the module in VBE.

Sub UseReplace()

End Sub

After writing the subroutine we will define the variable

Sub UseReplace()
Dim InpStr as String
Dim Result as variant

End Sub

After we define the variable, For the InpStr we will store some text.

Sub UseReplace() 
Dim InpStr as String 
Dim Result as variant 

InpStr = "Honesty is the best policy. He is the best leader. 
End Sub

 We will use Replace function to find the value.

Sub UseReplace() 
Dim InpStr as String 
Dim Result as variant 

InpStr = "Honesty is the best policy. He is the best leader.
Result = Replace (InpStr, "best", "good")

End Sub

And now we will use debug. Print to print the value result in the Immediate window

Sub UseReplace() 
Dim InpStr as String 
Dim Result as variant 

InpStr = "Honesty is the best policy. He is the best leader.
Result = Replace (InpStr, "best", "good")
debug. Print Result
End Sub

And we will click Run button

Now you can see Replaced Word in String.

[Start as Long] Parameter-

The starting position from where we want to start to find. In the start parameter, we will write a value like 9, then after skipping the 9 characters, the string will start. 

Sub UseReplace() 
Dim InpStr as String 
Dim Result as variant

InpStr = "Honesty is the best policy. He is the best leader.
Result = Replace (InpStr, "best", "good", 9)

End Sub

And now we will use debug. and Click Run button,

Sub UseReplace()
Dim InpStr as String 
Dim Result as variant

InpStr = "Honesty is the best policy. He is the best leader. 
Result = Replace (InpStr, "best", "good", 9) 

debug. Print Result 
End Sub

 You can see in immediate window string started after skipping 9 characters. then Replaced Word in String.

Count as Long Parameter

It describes the No. of replacement we want to make. 

Sub UseReplace()
Dim InpStr as String 
Dim Result as variant

InpStr = "Honesty is the best policy. He is the best leader. 
Result = Replace (InpStr, "best", "good",,1)

End Sub

And now we will use debug. Print and Click Run button,

Sub UseReplace()
Dim InpStr as String 
Dim Result as variant

InpStr = "Honesty is the best policy. He is the best leader. 
Result = Replace (InpStr, "best", "good",,1)

debug. Print Result
End Sub

You can see in the immediate window. 1 was filled in the Count Parameter and when else the first find parameter word changed

Compare Uses

If you change the case of the find parameter. It will not Replace, because Replace function is case-sensitive by default.

Sub UseReplace() 
Dim InpStr as String 
Dim Result as variant 

InpStr = "Honesty is the best policy. He is the best leader. 
Result = Replace (InpStr, "BEST", "good")

End Sub

And now we will use debug. Print and Click Run button,

Sub UseReplace() 
Dim InpStr as String 
Dim Result as variant 

InpStr = "Honesty is the best policy. He is the best leader. 
Result = Replace (InpStr, "BEST", "good")

debug. Print Result
End Sub

You can see in the immediate window. The words in the string are not replaced because the Replace function is case-sensitive by default.

We can switch to case insensitive by adding the Optional parameter (vbTextCompare)

Sub UseReplace()
Dim InpStr as String 
Dim Result as variant

InpStr = "Honesty is the best policy. He is the best leader.
Result = Replace (InpStr, "BEST", "good", , ,vbTextCompare)

End Sub

And now we will use debug. Print and Click Run button,

Sub UseReplace()
Dim InpStr as String 
Dim Result as variant

InpStr = "Honesty is the best policy. He is the best leader.
Result = Replace (InpStr, "BEST", "good", , ,vbTextCompare)

debug. Print Result
End Sub

You can see in the immediate window. We have replaced string words using vbTextCompare.

Change the way the code is written, and word will replace it in the string. So let us now write the replace function in a new way.

Sub UseReplace() 
Dim InpStr as String 
Dim Result as variant
InpStr = "Honesty is the best policy. He is the best leader. 
Result = Replace(Expression:=inpStr, Find:="Best", Replace:="good", Compare:=vbTextCompare)

End Sub

And now we will use debug. Print and Click Run button,

Sub UseReplace() 
Dim InpStr as String 
Dim Result as variant

InpStr = "Honesty is the best policy. He is the best leader. 
Result = Replace(Expression:=inpStr, Find:="Best", Replace:="good", Compare:=vbTextCompare)

debug. Print Result
End Sub

You can see in the immediate window. Now you can see Replaced Word in String.

We can replace many words or sentences at a time. There is no limit of words or text what we cannot replace with.

So, I hope you have understood How to use REPLACE Function in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.

DOWNLOAD USED EXCEL FILE FROM HERE>>

LEARN MORE TOPIC IN VBA HERE

You can also see well-explained video here about Replace Function in Excel VBA