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