Find, ReplaceLine Methods Example

The following function finds a specified string in a module and replaces the line that contains that string with a new specified line.

Function FindAndReplace(strModuleName As String, _
    strSearchText As String, _
    strNewText As String) As Boolean
    Dim mdl As Module
    Dim lngSLine As Long, lngSCol As Long
    Dim lngELine As Long, lngECol As Long
    Dim strLine As String, strNewLine As String
    Dim intChr As Integer, intBefore As Integer, _
        intAfter As Integer
    Dim strLeft As String, strRight As String

    ' Open module.
    DoCmd.OpenModule strModuleName
    ' Return reference to Module object.
    Set mdl = Modules(strModuleName)

    ' Search for string.
    If mdl.Find(strSearchText, lngSLine, lngSCol, lngELine, _
        lngECol) Then
        ' Store text of line containing string.
        strLine = mdl.Lines(lngSLine, Abs(lngELine - lngSLine) + 1)
        ' Determine length of line.
        intChr = Len(strLine)
        ' Determine number of characters preceding search text.
        intBefore = lngSCol - 1
        ' Determine number of characters following search text.
        intAfter = intChr - CInt(lngECol - 1)
        ' Store characters to left of search text.

        strLeft = Left$(strLine, intBefore)
        ' Store characters to right of search text.
        strRight = Right$(strLine, intAfter)
        ' Construct string with replacement text.
        strNewLine = strLeft & strNewText & strRight
        ' Replace original line.
        mdl.ReplaceLine lngSLine, strNewLine
        FindAndReplace = True
    Else
        MsgBox "Text not found."
        FindAndReplace = False
    End If

Exit_FindAndReplace:
    Exit Function

Error_FindAndReplace:

MsgBox Err & ": " & Err.Description
    FindAndReplace = False
    Resume Exit_FindAndReplace
End Function