替换代码行中的特定元素

Question

我想用其他元素替换代码中的某些元素。例如：C.Offset(0, 1).Value = 0 to C.Offset(0, 28).Value = 5在VbaEditor的帮助下，我可以找到所需元素的行数和列数。并借助Mid ( string And，start，length) =string替换它们。如何通过行号和列号访问stringvar？

Sub кс()
Dim book1 As Workbook
Dim VBAEditor As VBIDE.VBE
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim FindWhat As String
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 Found As Boolean

Set VBAEditor = Application.VBE
Set book1 = Workbooks.Open("...")
Set VBProj = book1.VBProject
Set VBComp = VBProj.VBComponents("Module1")
Set CodeMod = VBComp.CodeModule

FindWhat = "C.Offset(0, 1).Value = 0"

      
With CodeMod
    SL = 1
    EL = .CountOfLines
    SC = 1
    EC = 255
    Found = .Find(target:=FindWhat, StartLine:=SL, StartColumn:=SC, _
        EndLine:=EL, EndColumn:=EC, _
        wholeword:=True, MatchCase:=False, patternsearch:=False)
    Do Until Found = False
        MsgBox "Found at: Line: " & CStr(SL) & " Column: " & CStr(SC)
        EL = .CountOfLines
        SC = EC + 1
        EC = 255
        Found = .Find(target:=FindWhat, StartLine:=SL, StartColumn:=SC, _
            EndLine:=EL, EndColumn:=EC, _
            wholeword:=True, MatchCase:=False, patternsearch:=False)
    Loop
End With
book1.Save
book1.Close
End Sub

Answer 1

扫描各行并使用Replace。

Option Explicit
Sub KC()
    Dim wb As Workbook
    Dim VBAEditor As VBIDE.VBE
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim n As Long, sLine As String, i As Long
    Dim t0 As Single: t0 = Timer
   
    Const OLDCODE = "c.Offset(0, 1).Value = 0"
    Const NEWCODE = "c.Offset(0, 28).Value = 5"

    Set wb = ThisWorkbook
    Set VBProj = wb.VBProject
    Set VBComp = VBProj.VBComponents("Module1")
    Set CodeMod = VBComp.CodeModule
    With CodeMod
        For n = 1 To .CountOfLines
            sLine = .Lines(n, 1)
            If InStr(sLine, OLDCODE) > 0 Then
                 Debug.Print "BEFORE", n, sLine
                 sLine = Replace(sLine, OLDCODE, NEWCODE)
                 Debug.Print "AFTER", n, sLine
                 .ReplaceLine n, sLine
                 i = i + 1
            End If
        Next
    End With
    MsgBox i & " of " & n - 1 & " lines updated", vbInformation, _
           Format(Timer - t0, "0.00") & " seconds"
      
End Sub