![](https://codky.com/wp-content/uploads/2024/01/How-to-use-Replace-function-in-VBA-1024x576.png)
In VBA (Visual Basic for Applications), the
Replace function is used to replace occurrences of a substring with a new string within a given input string.
Here is the syntax of the
Replace function:
Replace(expression, find, replacewith, [start, [count, [compare]]])
expression: The string that you want to search within.
find: The substring you want to find.
replacewith: The substring you want to use to replace
find.
start: Optional. The position within
expression to start the search. If omitted, the search begins at the first character.
count: Optional. The number of occurrences to replace. If omitted, all occurrences will be replaced.
compare: Optional. The type of comparison to perform. It can be
vbBinaryCompare or
vbTextCompare or
vbDatabaseCompare. If omitted,
vbBinaryCompare is used.
Here is an example of how to use the
Replace function in VBA:
Sub ExampleReplace()
Dim originalString As String
Dim searchString As String
Dim replaceString As String
Dim resultString As String
' The string in which we want to replace substrings
originalString = "Hello World. World is great."
' The substring we want to replace
searchString = "World"
' The substring we'll replace it with
replaceString = "Universe"
' Using the Replace function
resultString = Replace(originalString, searchString, replaceString)
' Output the result
MsgBox resultString ' Output will be "Hello Universe. Universe is great."
End Sub
In this example, we are replacing all instances of “World” with “Universe” in the provided
originalString.
You would put this code in a new module inside the VBA editor in either Excel, Access, or another Office application that supports VBA. To run the code, press F5 within the VBA editor or create a button in the application interface that runs the
ExampleReplace subroutine when clicked.
Unlock Your Potential
Help us grow the project