INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Functions

How do I simulate the VB Replace Function in VBA? by bustell
Posted: 22 Apr 02

Here is a function that works with Access to simulate the built in VB Replace function.  For those of you have have trouble with recursion, this function does not use it which makes it easier to understand.  

It also allows you to set the string comparison method. The compare argument can be omitted, or it can be 0 or 1. Specify 0 (default) to perform a binary comparison. Specify 1 to perform a textual, noncase-sensitive comparison.

Public Function Replace(sString As Variant _
          , sFind As String _
          , sReplace As String _
          , Optional iCompare As Long = vbBinaryCompare)
       _ As Variant

    Dim iStart As Integer, iLength As Integer
    If IsNull(sString) Then
        Replace = Null
    Else
        iStart = InStr(1, sString, sFind, iCompare)
        Do While iStart > 0
            sString = Left(sString, iStart - 1) _
                    & sReplace & Mid(sString, iStart + Len(sFind), Len(sString) - iStart - Len(sFind) + 1)
            iStart = InStr(iStart, sString, sFind)
        Loop
        Replace = sString
    End If

End Function

Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close