Replace function in VBA 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) &gt; 0
        ModString = Left(ModString, InStr(1, ModString, InvalidStr) - 1) &amp; _
        ValidStr &amp; Mid(ModString, Len(InvalidStr) + InStr(1, ModString, InvalidStr))
    Wend
    RepStr = ModString
End Function
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) &amp; sReplace &amp; Mid(sOut, nPos + Len(sFind))

        If nCount &lt;&gt; -1 And nC &gt;= nCount Then Exit Do

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

    Loop While nPos &gt; 0

EndFn:

    RepStr3 = sOut

End Function