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))
    RepStr = ModString
End Function
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ Aug 23, 2017 at 1:42
  • 5
    \$\begingroup\$ 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? \$\endgroup\$ Aug 23, 2017 at 2:16
  • \$\begingroup\$ @ThunderFrame not my choice on the upgrade unfortunately. \$\endgroup\$
    – Doug Coats
    Aug 23, 2017 at 13:57

1 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:

        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


    RepStr3 = sOut

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.