Replace function in VBA

Question

For reasons I'd rather like to not talk about, I am in a VBA environment that doesn't have the replace function (Access 97). I have wrote this to alleviate my frustrations.

My question is, what is the quickest and most efficient way of writing this function? I am not married to it, I just want the best possible piece of code. While I do enjoy readability I am also OK with stepping away from that to learn something super cool.

Public Function RepStr(InputString As String, InvalidStr As String, ValidStr As String) As String
    Dim ModString As String

    ModString = InputString

    While InStr(1, ModString, InvalidStr) > 0
        ModString = Left(ModString, InStr(1, ModString, InvalidStr) - 1) & _
        ValidStr & Mid(ModString, Len(InvalidStr) + InStr(1, ModString, InvalidStr))
    Wend
    RepStr = ModString
End Function

Answer 1

This is a bit faster

---

Public Function RepStr2(txt As String, str1 As String, str2 As String) As String

    RepStr2 = Join(Split(txt, str1), str2)

End Function

---

Light testing with about 300K cells in col A, output in col B:

RepStr2(CStr(arr(i, 1)), "a", "x")

Col A          | Col B
abcabcabc      | xbcxbcxbc
aaa            | xxx
aaaaaaaaa      | xxxxxxxxx
aaaaaaaaaaaaaa | xxxxxxxxxxxxxx
... (duplicated down to 300K)

After repeated tests all functions run faster

Your version: 2.039 secs
This version: 1.406 secs
MS version:   1.688 secs (bellow)

---

Edit (as pointed out by ThunderFrame in the comment)

Microsoft's version is fast as well:

Public Function RepStr3(sIn As String, sFind As String, sReplace As String, _
                        Optional nStart As Long = 1, Optional nCount As Long = -1, _
                        Optional bCompare As VbCompareMethod = vbBinaryCompare) As String

    Dim nC As Long, nPos As Integer, sOut As String

    sOut = sIn
    nPos = InStr(nStart, sOut, sFind, bCompare)

    If nPos = 0 Then GoTo EndFn:

    Do
        nC = nC + 1
        sOut = Left(sOut, nPos - 1) & sReplace & Mid(sOut, nPos + Len(sFind))

        If nCount <> -1 And nC >= nCount Then Exit Do

        nPos = InStr(nStart, sOut, sFind, bCompare)

    Loop While nPos > 0

EndFn:

    RepStr3 = sOut

End Function