how to defined chained substitute function in vba

Question

How to use vba function to expand the substitute function from the replacement of one pattern to several patterns?

The desired result is : Instead of substitute(cell, pattern, replacement, instancenumber), is it possible to define a function like sub2(cell, pat1, replacement 1, pat2, replacement 2, pat3,replacement 3,...)?

Thanks.

Answer 1

You can do this using ParamArrays which allow you to pass in as many arguments as you like. From here you interpret the values in the array to perform custom logic:

' patternReplacements are expected to have an even number of arguments.
' The even param (0, 2, etc) would be the search text and the odd (1, 3, etc) the respective replacement text.
Public Sub Substitute(ByVal cell As Range, ParamArray patternReplacements() As Variant)
    Dim text As String
    text = cell.Value

    ' Perform text replacements.
    ' Note - No logic here ensures an even number of arguments are passed, this could be easily added though.
    Dim i As Integer
    For i = 0 To UBound(patternReplacements) Step 2
        text = Replace(text, patternReplacements(i), patternReplacements(i + 1))
    Next

    cell.Value = text
End Sub

Now calling it, would look something like this:

Substitute Range("A1"), "find1", "replace1", "find2", "replace2", "find3", "replace3"