# 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) > 0
ModString = Left(ModString, InStr(1, ModString, InvalidStr) - 1) & _
ValidStr & Mid(ModString, Len(InvalidStr) + InStr(1, ModString, InvalidStr))
Wend
RepStr = ModString
End Function

• 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 Aug 23 '17 at 1:42
• 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? – ThunderFrame Aug 23 '17 at 2:16
• @ThunderFrame not my choice on the upgrade unfortunately. – Doug Coats Aug 23 '17 at 13:57

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

• That's nice, except it's VBA5, and the Join function doesn't exist either. – ThunderFrame Aug 23 '17 at 1:35
• I was about to say, it would be nice if certain things were actually availble to this project. – Doug Coats Aug 23 '17 at 1:39
• 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. – ThunderFrame Aug 23 '17 at 1:45
• Good call: MS is the fastest option - I'll update the answer – paul bica Aug 23 '17 at 1:53