Replace VBA function – How to replace a string part

Last Updated on January 11, 2023 by token

1. Replace VBA function – Description

The  Replace VBA  function is used to replace strings. If we want to replace a word or a fragment of text with another, we use the Replace function. The function has additional arguments with which we can also determine how many occurrences of strings are to be replaced. The VBA Replace function can be compared to the Find and Replace functionality in an Excel sheet.

2. VBA 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 : A string of characters to search for sentences, words or characters to replace.

Find : The string we are looking for and want to replace it with.

Replace : The string with which we want to replace the search text.

Start : Optional argument. The position in the text from which to start the search

Count : Optional argument. The number of occurrences of the string we want to replace

Compare : An optional argument. A method for comparing strings. This argument also determines whether the search is case-sensitive.

Function return : String / Value text value.

3. VBA Replace function – Example

How to use the Replace  function  in VBA Excel? Below are examples of using the Replace function in the VisualBasic Editor.

Example 1 : An example of using the Replace VBA function – result in the MsgBox window.

Dim strValue As String
strValue = "Learn VBA functions. Learn functions"
MsgBox Replace (strValue, "functions", "methods", 1, 1, vbTextCompare)
MsgBox Replace (strValue, "functions", "methods", 1, vbTextCompare)

Example 2 : VBA Replace – function example – result in an Excel sheet.

Dim strValue2 As String
strValue2 = "Learn VBA functions. Learn functions"
Range ("A1") = Replace (strValue2, "functions", "methods", 1, 1, vbTextCompare)
Range ("A2") = Replace (strValue2, "functions", "methods", 1, vbTextCompare)

4. VB Replace function – Additional information

• If we want the program to be case insensitive when searching, we should complete the Compare argument with vbTextCompare .
• If we want the replacement of characters to start, for example, from the 10th character in the text, we should use the Start argument, supplementing it with, for example, 10.
• In case we want to replace the first few occurrences of the searched phrase in the text, and not all of them, we use the Count argument and enter the number of occurrences we want to replace.