VBA のコードを書き換えるやつ

gistfile1.vba

' 事前準備
'    https://hake.hatenablog.com/entry/20170812/p1
' 更新用マクロを動作させるExcelで以下の設定を行う
' ファイル
' →オプション
' →セキュリティセンター
' →セキュリティセンターの設定
' →マクロの設定
' →VBAプロジェクトオブジェクトモデルへのアクセスを信頼する。にチェック

Sub ReplaceModule()


    Dim cnt As Long
    Dim word As String
    Dim modName As String
    Dim limit As Long
    Dim startLine As Long
    Dim endLine As Long
  
  
    modName = "Module2"
    word = "Range(A1).Value"
  
    startLine = 0
    endLine = ThisWorkbook.VBProject.VBComponents(modName).CodeModule.CountOfLines
  
    Dim flg As Boolean
    flg = isExistWord(modName, word, 0, cnt)

    ' 二分木探索で対象行数を判定 (単純に 2 で割ると奇数の扱いが大丈夫なのかよく分からん)
    limit = 0
    If flg Then
        Do While flg
            If limit > 100 Then
                Exit Do
            End If
            limit = limit + 1
        
            flg = isExistWord(modName, word, startLine, endLine / 2)
            If flg Then
                endLine = endLine / 2
            Else
                flg = isExistWord(modName, word, endLine / 2, endLine)
                If flg Then
                    Dim before As Long
                    before = endLine / 2
                    If startLine = before Then
                        startLine = startLine + 1
                    Else
                        startLine = endLine / 2
                    End If
                End If
            End If
        Loop
    End If
    
  
    ' ほんとに見つかったかを再チェック
    
    If flg Then
        flg = isExistWord(modName, word, startLine, startLine)
    Else
        MsgBox ("みつからない (=ΦωΦ)")
    End If
    
    ' 書き換え
    If flg Then
        ' 対象コードの取得
        Dim code As String
        Dim after As String
        code = ThisWorkbook.VBProject.VBComponents(modName).CodeModule.Lines(startLine, 1)
        ' 念の為確認する
        after = Replace(code, "A1", "B1")
        result = MsgBox(startLine & " 行目を書き換えますか？ " & vbCrLf & code & vbCrLf & "↓" & vbCrLf & after, Buttons:=vbYesNo)
        ' 書き換える
        If result = vbYes Then
            Call ThisWorkbook.VBProject.VBComponents(modName).CodeModule.ReplaceLine(startLine, after)
        End If
        
    End If

End Sub

Function isExistWord(modName As String, word As String, startLine As Long, endLine As Long) As Boolean
    Dim flg As Boolean

    flg = ThisWorkbook.VBProject.VBComponents(modName).CodeModule.Find(word, startLine, 0, endLine, 999)
        
    isExistWord = flg
    
End Function