Returns TextIn with each occurrence of Placeholder replaced with Replacement.
- Syntax
-
TextOut = Replace(TextIn, Placeholder, Replacement)
Part
|
Description
|
| TextIn |
Required text with embedded Placeholders.
|
| Placeholder |
Required text to be found in TextIn.
|
| Replacement |
Required text that replaces each Placeholder in TextIn.
|
| Compare |
Optional number indicates the kind of comparison to use.
See Settings section below.
If omitted, the default value is 0 for Binary comparison
regardless of any Option Compare statement.
|
Settings
The Compare argument can have the following values:
Constant
|
Value
|
Description
|
| vbBinaryCompare |
0 |
Perform a binary comparison. This is case sensitive.
Placeholders in TextIn must be in the same case
as the Placeholder argument in order to get replaced.
This is the default regardless of any Option Compare statement.
Binary comparisons are much faster than text comparisons.
|
| vbTextCompare |
1 |
Perform a textual comparison. This is not case sensitive.
The Placeholder argument can be in upper case
yet still match lower case text in TextIn.
|
Examples
If you put text in the Caption property of a Label control,
one Ampersand (&) shows up as a Hot-Key.
And two Ampersands (&&) show up as one (&).
Therefore, to make one Ampersand show up as one,
replace them with two.
Label1.Caption = Replace(Text1, "&", "&&")
To make text out of a label caption,
reverse the process.
There's a trick to this.
This example uses %and% as a temporary placeholder.
Function GetCaption(TextIn)
Work = Replace(TextIn, "&&", "%and%")
Work = Replace(Work, "&", "")
GetCaption = Replace(Work, "%and%", "&")
End Function
Source
Function Replace(TextIn As String, _
ByVal Placeholder As String, _
ByVal Replacement As String, _
Optional ByVal Compare As Variant) As String
Dim TextOut As String
Dim Text As String
Dim Length As Long 'Bytes in Placeholder text.
Dim Pos As Long 'Start position of Placeholder.
Dim PrevPos As Long 'Pos after prev Placeholder.
If IsMissing(Compare) Then Compare = vbBinaryCompare
PrevPos = 1
Length = Len(Placeholder)
Pos = InStr(1, TextIn, Placeholder, Compare)
Do Until Pos = 0
Text = Mid$(TextIn, PrevPos, Pos - PrevPos)
Text = Text & Replacement
TextOut = TextOut & Text
PrevPos = Pos + Length
Pos = InStr(PrevPos, TextIn, Placeholder, Compare)
Loop
Replace = TextOut & Mid$(TextIn, PrevPos)
End Function
|
