Caution

toStr is assumed to be the same length as frstr.If not, the remaining characters are considered null ("").The function is case sensitive.To replace all instances of a,use a and A.You can't replace one character with two characters. This =MSUBSTITUTE("This is a test",''i","[email protected]")

results in this:

Function example:

Function MSUBSTITUTE(ByVal trStr As Variant, frStr As String, _

toStr As String) As Variant Dim iRow As Integer Dim iCol As Integer Dim j As Integer Dim Ar As Variant Dim vfr() As String Dim vto() As String ReDim vfr(1 To Len(frStr)) ReDim vto(1 To Len(frStr)) 'place the strings into an array For j = 1 To Len(frStr)

vfr(j) = Mid(frStr, j, 1) If Mid(toStr, j, 1) <> "" Then vto(j) = Mid(toStr, j, 1)

'compare each character and substitute if needed If IsArray(trStr) Then Ar = trStr

For iCol = LBound(Ar, 2) To UBound(Ar, 2) For j = 1 To Len(frStr)

Ar(iRow, iCol) = Application.Substitute(Ar(iRow, iCol), vfr(j), vto(j)) Next j Next iCol Next iRow

Else

Ar = trStr

Ar = Application.Substitute(Ar, vfr(j), vto(j)) Next j End If

MSUBSTITUTE = Ar End Function

Figure 4.10

Multiple substitution.

0 0

Post a comment