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

• ThunderFrame: Microsoft published an article (now only available via archive.org) How To Simulate Visual Basic 6.0 String Functions in VB5 which provides VBA5 code for Replace, InStrRev, StrReverse, Join and Split.
• ThunderFrame: Whatever you end up using, I'd recommend mirroring the name and arguments and return type of the VB6 Replace function, so that when you eventually migrate from Access 97, you can just delete your shadowing functions, and fall back to the built-in functions. Of course, if you haven't upgraded from Access 97 yet, I suppose you're unlikely to do so in the foreseeable future. Maybe I shouldn't have linked to those functions? Maybe I'm giving you one less reason to upgrade?
• Doug Coats: @ThunderFrame not my choice on the upgrade unfortunately.

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

• ThunderFrame: That's nice, except it's VBA5, and the Join function doesn't exist either.
• Doug Coats: I was about to say, it would be nice if certain things were actually availble to this project.
• ThunderFrame: Come to think of it, Split wasn't in VB5 either. See my comment on original question with link to MS KB article that offers code for the missing string functions.
• paul bica: Good call: MS is the fastest option - I'll update the answer