比Substitute功能更强的批量替换文本函数NewText

=NewText(A1,"A,B","C")等价于=SUBSTITUTE(SUBSTITUTE(A1,"A","C"),"B","C")

=NewText(E22,"A,C,E","JK")等价于SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E22,"A","JK"),"C","JK"),"E","JK")
Function NewText(r As String, s As Variant, Optional ss = "")
    Dim i As Integer
    Dim myRange As Range
    Dim st, rng, test
    rng = r
    Select Case VBA.TypeName(s)
    Case "String"
        st = Split(s, ",")
        For i = 0 To UBound(st)
            test = VBA.Replace(rng, st(i), ss)
            rng = test
        Next
    Case "Range"
        For Each myRange In s
            test = VBA.Replace(rng, myRange, ss)
            rng = test
        Next
    End Select
   
    NewText = test
End Function