replace text in code module

Question

Im trying to use the replaceline function to update code in Access VBA module. it keeps coming up with a compile error. Ive checked that the VBA Extension are selected and compared it to other examples that I have looked up.

this is the first time that Ive used this type of function, so I haven't fully got my head around them.

code below

Sub ReplaceCodeModuleText(strModule As String, strFindWhat As String, strReplaceWith As String)
'FUNCTION:
'           Search the code module for specific text
'           Replace with new text


Dim VBProj As VBProject
Dim VBComp As VBComponent
Dim CodeMod As CodeModule


Dim SL As Long ' start line
Dim EL As Long ' end line
Dim SC As Long ' start column
Dim EC As Long ' end column
Dim strCodeLine As String
Dim vDummy As Variant

Dim Found As Boolean

    Set VBProj = Application.VBE.ActiveVBProject
    Set VBComp = VBProj.VBComponents(strModule)
    Set CodeMod = VBComp.CodeModule '    '.CodeModule

    With CodeMod
        SL = 1:        EL = .CountOfLines
        SC = 1:        EC = 255

        Found = .Find(Target:=strFindWhat, StartLine:=SL, StartColumn:=SC, _
            EndLine:=EL, EndColumn:=EC, _
            wholeword:=True, MatchCase:=False, patternsearch:=False)

        If Found Then
            strCodeLine = CodeMod.Lines(SL, 1)
            strCodeLine = Replace(strCodeLine, strFindWhat, strReplaceWith, Compare:=vbTextCompare) 'not case sensitive = vbTextCompare
            .ReplaceLine(SL, strCodeLine)

            Debug.Print "Successfully Replaced: " & strFindWhat & " in VBA Module: " & strModule & " with : " & strReplaceWith
        Else
            Debug.Print "Did not find: " & strFindWhat;

        End If
    End With
End Sub

It looks like you only ever process the first line. The .Find line says search from line 1 to EL, but the assignment of strCodeLine and .ReplaceLine only use SL which doesn't appear to have been updated. It might be worth using a for loop and do the string replace and ReplaceLine on every line in the module. — GregHNZ
– GregHNZ, Commented Jun 4, 2017 at 1:25
@GregHNZ: CodeModule.Find sets its StartLine, StartColumn, etc. parameters. — Andre
– Andre, Commented Jun 4, 2017 at 7:08

Graham · Accepted Answer · 2017-09-21 18:09:48Z

0

        .ReplaceLine(SL, strCodeLine)

must be either

        Call .ReplaceLine(SL, strCodeLine)

or

        .ReplaceLine SL, strCodeLine

edited Sep 21, 2017 at 18:09

Graham

7,86020 gold badges67 silver badges92 bronze badges

answered Jun 4, 2017 at 7:06

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ian Cox Over a year ago

thanks Andre, that fixed the problem. I didn't see any examples that used the "Call" proceedure. One thing I noticed was that I need to ensure that I don't call it up on a function that is in use as it will cause Access to crash( kind of makes sense).

Collectives™ on Stack Overflow

replace text in code module

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related