Replace Function

Returns a string in which a specified substring has been replaced with another substring a specified number of times.

Syntax

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

Parameters

• expression - Required. String expression containing substring to replace.
• find - Required. Substring being searched for.
• replace - Required. Replacement substring.
• start - Optional. Position within expression where substring search is to begin. If omitted, 1 is assumed. Must be used in conjunction with count.
• count - Optional. Number of substring substitutions to perform. If omitted, default value is -1, which means make all possible substitutions.
• compare - Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. If omitted, 0 is assumed (vbBinaryCompare).

Compare Values

Return Value

Returns a String with the replacements made. The return value depends on the parameters:

Remarks

The Replace function returns a string with substitutions made. Unlike the Replace method of regular expressions, this function performs simple string substitution without pattern matching.

The return value of the Replace function is a string that begins at the position specified by start, with substitutions made, and concludes at the end of the expression string. It is not a copy of the original string from start to finish.

Important Notes: - If start is specified, the return value starts from that position, not from position 1 - The original string before start position is not included in the result - Use count parameter to limit the number of replacements - Binary comparison (default) is case-sensitive; textual comparison is case-insensitive - Empty find string returns the original expression unchanged - Empty replace string removes all occurrences of find

Typical Uses

1. Text Sanitization: Remove or replace unwanted characters from user input
2. Data Formatting: Replace delimiters or format characters in data
3. Template Processing: Replace placeholders in template strings
4. Path Manipulation: Replace path separators or modify file paths
5. Case Normalization: Replace mixed-case text with standardized case
6. String Cleaning: Remove multiple spaces, tabs, or other whitespace
7. Data Import/Export: Convert between different data formats
8. SQL String Building: Escape quotes and special characters

Basic Examples

Example 1: Simple Replacement

Dim result As String
result = Replace("Hello World", "World", "VB6")
' Returns: "Hello VB6"

Example 2: Case-Insensitive Replacement

Dim result As String
result = Replace("Hello WORLD", "world", "VB6", 1, -1, vbTextCompare)
' Returns: "Hello VB6"

Example 3: Remove Substring

Dim cleaned As String
cleaned = Replace("Remove   extra   spaces", "   ", " ")
' Returns: "Remove extra spaces"

Example 4: Limited Replacements

Dim result As String
result = Replace("one, two, three, four", ", ", " | ", 1, 2)
' Returns: "one | two | three, four" (only first 2 commas replaced)

Common Patterns

Pattern 1: RemoveAllSpaces

Function RemoveAllSpaces(text As String) As String
RemoveAllSpaces = Replace(text, " ", "")
End Function

Pattern 2: NormalizeWhitespace

Function NormalizeWhitespace(text As String) As String
Dim result As String
result = text

' Replace tabs with spaces
result = Replace(result, vbTab, " ")

' Replace multiple spaces with single space
Do While InStr(result, "  ") > 0
result = Replace(result, "  ", " ")
Loop

NormalizeWhitespace = Trim(result)
End Function

Pattern 3: EscapeSQLString

Function EscapeSQLString(text As String) As String
' Escape single quotes for SQL
EscapeSQLString = Replace(text, "'", "''")
End Function

Pattern 4: ReplaceMultiple

Function ReplaceMultiple(text As String, findList() As String, _
replaceList() As String) As String
Dim i As Integer
Dim result As String

result = text

For i = LBound(findList) To UBound(findList)
result = Replace(result, findList(i), replaceList(i))
Next i

ReplaceMultiple = result
End Function

Pattern 5: ReplaceCaseInsensitive

Function ReplaceCaseInsensitive(text As String, find As String, _
replaceWith As String) As String
ReplaceCaseInsensitive = Replace(text, find, replaceWith, 1, -1, vbTextCompare)
End Function

Pattern 6: ReplaceSpecialChars

Function ReplaceSpecialChars(text As String, replacement As String) As String
Dim result As String
Dim specialChars As String
Dim i As Integer

result = text
specialChars = "!@#$%^&*()[]{}|;:,.<>?/"

For i = 1 To Len(specialChars)
result = Replace(result, Mid(specialChars, i, 1), replacement)
Next i

ReplaceSpecialChars = result
End Function

Pattern 7: SanitizeFilename

Function SanitizeFilename(filename As String) As String
Dim result As String
Dim invalidChars As String
Dim i As Integer

result = filename
invalidChars = "\/:*?""<>|"

For i = 1 To Len(invalidChars)
result = Replace(result, Mid(invalidChars, i, 1), "_")
Next i

SanitizeFilename = result
End Function

Pattern 8: ConvertLineEndings

Function ConvertLineEndings(text As String, newEnding As String) As String
Dim result As String

result = text

' Normalize to LF first
result = Replace(result, vbCrLf, vbLf)
result = Replace(result, vbCr, vbLf)

' Convert to desired ending
If newEnding <> vbLf Then
result = Replace(result, vbLf, newEnding)
End If

ConvertLineEndings = result
End Function

Pattern 9: ReplaceWithCounter

Function CountReplacements(text As String, find As String) As Long
' Count how many times find appears in text
Dim original As String
Dim replaced As String

If Len(find) = 0 Then
CountReplacements = 0
Exit Function
End If

original = text
replaced = Replace(original, find, "")

CountReplacements = (Len(original) - Len(replaced)) / Len(find)
End Function

Pattern 10: TemplateReplace

Function ProcessTemplate(template As String, replacements As Collection) As String
' Replace {key} placeholders with values from collection
Dim result As String
Dim key As Variant
Dim placeholder As String

result = template

For Each key In replacements
placeholder = "{" & key & "}"
result = Replace(result, placeholder, CStr(replacements(key)))
Next key

ProcessTemplate = result
End Function

Advanced Usage

Example 1: Text Sanitizer with Multiple Rules

' Advanced text sanitization with configurable rules
Class TextSanitizer
Private Type ReplacementRule
Find As String
ReplaceWith As String
CaseSensitive As Boolean
MaxReplacements As Long
End Type

Private m_rules() As ReplacementRule
Private m_ruleCount As Integer

Public Sub Initialize()
m_ruleCount = 0
ReDim m_rules(0 To 9)
End Sub

Public Sub AddRule(find As String, replaceWith As String, _
Optional caseSensitive As Boolean = True, _
Optional maxReplacements As Long = -1)
If m_ruleCount > UBound(m_rules) Then
ReDim Preserve m_rules(0 To UBound(m_rules) + 10)
End If

With m_rules(m_ruleCount)
.Find = find
.ReplaceWith = replaceWith
.CaseSensitive = caseSensitive
.MaxReplacements = maxReplacements
End With

m_ruleCount = m_ruleCount + 1
End Sub

Public Function Sanitize(text As String) As String
Dim result As String
Dim i As Integer
Dim compareMode As Integer

result = text

For i = 0 To m_ruleCount - 1
With m_rules(i)
If .CaseSensitive Then
compareMode = vbBinaryCompare
Else
compareMode = vbTextCompare
End If

result = Replace(result, .Find, .ReplaceWith, 1, .MaxReplacements, compareMode)
End With
Next i

Sanitize = result
End Function

Public Sub ClearRules()
m_ruleCount = 0
End Sub

Public Function GetRuleCount() As Integer
GetRuleCount = m_ruleCount
End Function
End Class

Example 2: String Template Engine

' Simple template engine with variable replacement
Module TemplateEngine
Public Function ProcessTemplate(template As String, _
variables As Scripting.Dictionary) As String
Dim result As String
Dim key As Variant
Dim placeholder As String
Dim value As String

result = template

' Replace {variable} placeholders
For Each key In variables.Keys
placeholder = "{" & CStr(key) & "}"
value = CStr(variables(key))
result = Replace(result, placeholder, value)
Next key

ProcessTemplate = result
End Function

Public Function ProcessConditional(template As String, condition As Boolean, _
trueValue As String, falseValue As String) As String
Dim result As String

result = template

If condition Then
result = Replace(result, "{if}", trueValue)
result = Replace(result, "{else}", "")
Else
result = Replace(result, "{if}", "")
result = Replace(result, "{else}", falseValue)
End If

ProcessConditional = result
End Function

Public Function ProcessLoop(template As String, items() As String) As String
Dim result As String
Dim itemText As String
Dim i As Integer

' Extract the loop template
Dim loopStart As Long
Dim loopEnd As Long
Dim loopTemplate As String

loopStart = InStr(template, "{loop}")
loopEnd = InStr(template, "{/loop}")

If loopStart = 0 Or loopEnd = 0 Then
ProcessLoop = template
Exit Function
End If

loopTemplate = Mid(template, loopStart + 6, loopEnd - loopStart - 6)

itemText = ""
For i = LBound(items) To UBound(items)
itemText = itemText & Replace(loopTemplate, "{item}", items(i))
Next i

result = Left(template, loopStart - 1) & itemText & Mid(template, loopEnd + 7)

ProcessLoop = result
End Function
End Module

Example 3: CSV/TSV Converter

' Convert between CSV and TSV formats
Class DelimiterConverter
Private m_sourceDelimiter As String
Private m_targetDelimiter As String
Private m_textQualifier As String

Public Sub Initialize(sourceDelim As String, targetDelim As String, _
Optional textQual As String = """")
m_sourceDelimiter = sourceDelim
m_targetDelimiter = targetDelim
m_textQualifier = textQual
End Sub

Public Function Convert(data As String) As String
Dim result As String
Dim inQuotes As Boolean
Dim i As Long
Dim ch As String

result = ""
inQuotes = False

For i = 1 To Len(data)
ch = Mid(data, i, 1)

If ch = m_textQualifier Then
inQuotes = Not inQuotes
result = result & ch
ElseIf ch = m_sourceDelimiter And Not inQuotes Then
result = result & m_targetDelimiter
Else
result = result & ch
End If
Next i

Convert = result
End Function

Public Function ConvertSimple(data As String) As String
' Simple conversion without quote handling
ConvertSimple = Replace(data, m_sourceDelimiter, m_targetDelimiter)
End Function

Public Function EscapeField(field As String) As String
' Escape field for CSV/TSV
Dim needsQuotes As Boolean
Dim result As String

result = field

' Check if field needs quoting
needsQuotes = (InStr(field, m_sourceDelimiter) > 0) Or _
(InStr(field, m_textQualifier) > 0) Or _
(InStr(field, vbCrLf) > 0)

If needsQuotes Then
' Escape existing quotes
result = Replace(result, m_textQualifier, m_textQualifier & m_textQualifier)
result = m_textQualifier & result & m_textQualifier
End If

EscapeField = result
End Function
End Class

Example 4: Smart String Replacer

' Advanced string replacement with history and undo
Class SmartReplacer
Private m_originalText As String
Private m_currentText As String
Private m_history() As String
Private m_historyCount As Integer

Public Sub Initialize(text As String)
m_originalText = text
m_currentText = text
m_historyCount = 0
ReDim m_history(0 To 99)
AddToHistory text
End Sub

Public Function ReplaceText(find As String, replaceWith As String, _
Optional caseSensitive As Boolean = True, _
Optional maxCount As Long = -1) As String
Dim compareMode As Integer

If caseSensitive Then
compareMode = vbBinaryCompare
Else
compareMode = vbTextCompare
End If

m_currentText = Replace(m_currentText, find, replaceWith, 1, maxCount, compareMode)
AddToHistory m_currentText

ReplaceText = m_currentText
End Function

Public Function ReplaceFromStart(find As String, replaceWith As String, _
startPos As Long) As String
' Replace starting from a specific position
Dim beforeStart As String
Dim afterStart As String

If startPos < 1 Then startPos = 1
If startPos > Len(m_currentText) Then
ReplaceFromStart = m_currentText
Exit Function
End If

beforeStart = Left(m_currentText, startPos - 1)
afterStart = Replace(Mid(m_currentText, startPos), find, replaceWith)

m_currentText = beforeStart & afterStart
AddToHistory m_currentText

ReplaceFromStart = m_currentText
End Function

Public Function GetCurrent() As String
GetCurrent = m_currentText
End Function

Public Function Undo() As String
If m_historyCount > 1 Then
m_historyCount = m_historyCount - 1
m_currentText = m_history(m_historyCount - 1)
End If

Undo = m_currentText
End Function

Public Function Reset() As String
m_currentText = m_originalText
m_historyCount = 0
ReDim m_history(0 To 99)
AddToHistory m_currentText

Reset = m_currentText
End Function

Public Function GetReplacementCount(find As String) As Long
Dim withFind As Long
Dim withoutFind As Long

If Len(find) = 0 Then
GetReplacementCount = 0
Exit Function
End If

withFind = Len(m_currentText)
withoutFind = Len(Replace(m_currentText, find, ""))

GetReplacementCount = (withFind - withoutFind) / Len(find)
End Function

Private Sub AddToHistory(text As String)
If m_historyCount > UBound(m_history) Then
' Shift history
Dim i As Integer
For i = 0 To UBound(m_history) - 1
m_history(i) = m_history(i + 1)
Next i
m_history(UBound(m_history)) = text
Else
m_history(m_historyCount) = text
m_historyCount = m_historyCount + 1
End If
End Sub
End Class

Error Handling

The Replace function can raise errors in the following situations:

• Invalid Procedure Call (Error 5): When:
• start parameter is less than 1
• count parameter is less than -1
• Type Mismatch (Error 13): When parameters cannot be converted to appropriate types
• Invalid Use of Null (Error 94): When expression is Null

Always validate inputs when necessary:

Function SafeReplace(text As String, find As String, replaceWith As String) As String
On Error Resume Next
SafeReplace = Replace(text, find, replaceWith)
If Err.Number <> 0 Then
SafeReplace = text  ' Return original on error
Err.Clear
End If
On Error GoTo 0
End Function

Performance Considerations

• The Replace function is optimized and very fast for most use cases
• Multiple sequential Replace calls can be slow for large strings
• Consider building a new string if making many replacements
• Using count parameter can improve performance by limiting replacements
• Binary comparison is faster than textual comparison
• Replacing with empty string is efficient for removing substrings

Best Practices

1. Use Meaningful Names: Name variables clearly (find, replaceWith, not f, r)
2. Check Empty Strings: Validate find parameter is not empty when expected
3. Consider Case Sensitivity: Choose appropriate compare parameter
4. Limit Replacements: Use count parameter when you know the limit
5. Chain Carefully: Be aware that multiple Replace calls compound
6. Escape Special Characters: Properly escape quotes and special chars
7. Validate Start Position: Ensure start is within string bounds
8. Test Edge Cases: Test with empty strings, no matches, all matches
9. Document Assumptions: Comment why specific replacements are made
10. Use Constants: Define commonly replaced strings as constants

Comparison with Related Functions

Platform and Version Notes

• Available in VB6 and VBA (Office 2000 and later)
• Not available in earlier VBA versions (use custom function)
• Behavior consistent across Windows platforms
• Case-insensitive comparison uses system locale settings
• vbDatabaseCompare only works in Microsoft Access

Limitations

• Cannot use regular expressions (use VBScript.RegExp for patterns)
• Cannot replace with different string based on match context
• Case-insensitive comparison depends on system locale
• No built-in way to replace with function result
• Cannot perform multiple different replacements in one call
• When start > 1, characters before start are not in result

Related Functions

• InStr: Returns position of substring within string
• InStrRev: Returns position of substring searching from end
• Mid: Returns specified portion of string
• Left: Returns specified number of characters from left
• Right: Returns specified number of characters from right
• LCase: Converts string to lowercase
• UCase: Converts string to uppercase