How to use Replace function in VBA?

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.

facebooklinkedin