s = "MsgBox ""No :("""
      x = "MsgBox ""Yes :)"""
      With ThisWorkbook.VBProject.VBComponents("tt").CodeModule
          n = .ProcBodyLine("test", 0)
          For i = n To .ProcCountLines("test", 0) + n - 1
              If .Lines(i, 1) = s Then .ReplaceLine i, x
          Next
      End With

Sub Macros()
With ThisWorkbook.VBProject.VBComponents.Item("tt").CodeModule
For i = 1 To .CountOfLines
If .Lines(i, 1) = "MsgBox ""No :(""" Then .ReplaceLine i, "MsgBox ""Yes :)"""
Next
End With
End Sub

Sub test()
MsgBox "No :("
End Sub

If Trim(.Lines(i, 1)) = "MsgBox ""No :(""" Then .ReplaceLine i, "MsgBox ""Yes :)"""

Sub Macros()
With ThisWorkbook.VBProject.VBComponents.Item("tt").CodeModule
For i = 1 To .CountOfLines
If InStr(.Lines(i, 1), "MsgBox ""No :(""") > 0 Then .ReplaceLine i, Replace(.Lines(i, 1), "No :(", "Yes :)")
Next
End With
End Sub

s="No :(": If i>5 Then MsgBox "No :(" ' комментарий с No :(

If InStr(.Lines(i, 1), "MsgBox ""No :(""") > 0 Then .ReplaceLine i, Replace(.Lines(i, 1), "MsgBox ""No :(""", "MsgBox ""Yes :)""")

Private Sub addToReferences()
On Error Resume Next
     Dim strVal As String
     Dim objWs As Object
      
     Set objWs = CreateObject("WScript.Shell")
     strVal = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Application.Version & "\Excel\Security\AccessVBOM"
     objWs.RegWrite Name:=strVal, Value:=1, Type:="REG_DWORD"  
      
     With ActiveWorkbook.VBProject.References
         .AddFromGuid "{420B2830-E718-11CF-893D-00A0C9054228}", 1, 0 'Scripting
         .AddFromGuid "{3F4DACA7-160D-11D2-A8E9-00104B365C9F}", 5, 5 'VBScript_RegExp_55
         .AddFromGuid "{B691E011-1797-432E-907A-4D8C69339129}", 6, 1 'ADODB
         .AddFromGuid "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B}", 1, 0 'IWshRuntimeLibrary
     End With
End Sub

Sub addCommToModules()
On Error Resume Next
     Dim i As Long
         With ActiveWorkbook.VBProject.VBComponents
             For i = 1 To .Count
                 If .item(i).Type = 1 Then '1 тип = модуль
                     .item(i).CodeModule.AddFromString (addText(.item(i).Name, Now, Application.UserName))
                 End If
             Next
         End With
     MsgBox "Описание модулей добавлено", vbInformation, "Information"
End Sub

Private Function addText(sItem1$, sItem2$, sItem3$) As String
     addText = Chr(39) & "-------------------------------------" & vbCrLf _
             & Chr(39) & "Module    : " & sItem1 & vbCrLf _
             & Chr(39) & "DateTime  : " & sItem2 & vbCrLf _
             & Chr(39) & "Developer : " & sItem3 & vbCrLf _
             & Chr(39) & "-------------------------------------"
End Function

col = sdate(Date - 3)

col = sdate(Date - 1)

col = sdate(Date - 6)