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.

Unlock Your Potential

Excel

Basic - Advanced

Access

Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *