VBA: Replace function

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Replace Function Description

The VBA Replace function replaces a certain substring (find) within a given string (expression) with another substring (replace).

Syntax

The syntax for the Replace function in VBA is:

Replace ( expression, find, replace, [start, [count, [compare]]] )

Parameters

expression
The string to replace a substring with replace substrings.

find
The string searched for the expression string.

replace
The replacement for find substrings found in expression.

start
Optional. The index position in expression to start search. If omitted, search will begin at position 1.

count
Optional. The number of occurrences to replace in expression. If omitted, will replace all occurrences of find with replace.

compare
Optional. This can be one of the following VBA values:

Constant Value Description
vbUseCompareOption -1 Uses option compare (default)
vbBinaryCompare 0 Binary comparison (distinguishes letter case)
vbTextCompare 1 Textual comparison (ignores letter case)

Example usage

The Replace function can be used in VBA code. Let’s look at some VBA Replace function examples:

Replace "Hello World!", "World", "Tony"
'Result: "Hello Tony!"

Replace "Hello Hello Hello", "Hello", "Tony", 7, 1
'Result: "Tony Hello"

Replace "Hello Hello Hello", "Hello", "Tony", 7, 2
'Result: "Tony Tony"

Replace "Hello hello Hello", "Hello", "Tony", 7, vbBinaryCompare
'Result: "hello Hello"

Replace "Hello hello Hello", "Hello", "Tony", 7, vbTextCompare
'Result: "hello Tony"

Simply the best place to learn VBA!

Error: Please enter a valid email address

Error: Invalid email

Error: Please enter your first name

Error: Please enter your last name

Error: Please enter a username

Error: Please enter a password

Error: Please confirm your password

Error: Password and password confirmation do not match