use macro to find and replace specific code in vba

i am wondering if it is possible to have a macro that opens the vbe for the active worksheet, finds the code line

.Top = xxx

and replaces it with

.Top = .Top.Value - 9

or something similar.

what i am trying to accomplish is have a macro permanently change the position and height of the active worksheet. when i run the macro it should go into the "This Workbook" excel object module for the active workbook, find the specific line of code (no matter its location in the code), and subtract "9" from its current value

thanks

Hi Hopeful,

Try the attached file (tested using Excel 2003), with full code following. It is MANDATORY that the PREREQUISITES exist, or the code will not compile and/or you will get runtime errors. I want to thank all those who provided the referenced links. Without those links, I wouldn't have known where to start.

Lewis

In an ordinary code module:

Option Explicit


''''''''''''''''''''''''''''''''''''''''''''''
'Modify the following constants as follows:
'sDisplaySheetNAME is the name of the SHEET TAB whose code is to be modified which is the name in PARENTHESES
'                  in the VBA Project Explorer (CASE INSENSITIVE).
'
'                  'sDisplaySheetNAME' MUST BE BLANK for 'ThisWorkbook'.
'                  'sDisplaySheetNAME' MUST BE BLANK for an Ordinary Code Module.
'
'sModuleName       is the 'Module Name' (CASE INSENSITIVE) where the code resides (used only for 'Ordinary Code Modules').
'
'
'sOldString        is the original string (CASE SENSITIVE) to be replaced (no wildcards allowed).
'
'                  NOTE: This WILL NOT WORK for text with embedded DOUBLE QUOTES (").
'
'sNewString        is the replacement string.
'
'
'Examples:         For ALL modules that contain code in the Active Workbook
'                  sDisplaySheetNAME = ""
'                  sModuleName = ""
'
'                  For 'ThisWorkbook'
'                  sDisplaySheetNAME = ""
'                  sModuleName = "ThisWorkbook"
'
'                  For the TAB whose Sheet Name is 'Main'
'                  sDisplaySheetNAME = "Main"
'                  sModuleName = ""
'
'                  For the Module Name 'ModVbeFindReplace'
'                  sDisplaySheetNAME = ""
'                  sModuleName = "ModVbeFindReplace"
'
'                  For the Module Name 'Sheet1' (whose TAB Sheet Name is 'Main')
'                  NOTE: This is really the same case as the previous example.
'                  sDisplaySheetNAME = ""
'                  sModuleName = "Sheet1"
'
''''''''''''''''''''''''''''''''''''''''''''''
Public Const sDisplaySheetNAME = ""
Public Const sModuleName = "Sheet1"

Public Const sOldString = ".Top = xxx"
Public Const sNewString = ".Top = .Top.Value - 9"

Public Const sNeverModifyModuleNAME = "ModVbeFindReplace"

Const nOptionLOOK_BUT_DONT_TOUCH = 1
Const nOptionREPLACE_OLD_STRING_WITH_NEW_STRING = 2
Const nOptionREPLACE_NEW_STRING_WITH_OLD_STRING = 3


''''''''''''''''''''''''''''''''''''''''''''''
'Prerequisites:
''''''''''''''''''''''''''''''''''''''''''''''
'1. Enable programmatic access to the VBA Project (runtime Error 1004 if not done).
'In Excel 2003 and earlier, go the Tools menu (in Excel, not in the VBA editor),
'choose Macros and then the Security item. In that dialog,
'click on the Trusted Publishers tab and check the Trust access to the Visual Basic Project setting.

'In Excel 2007, click the Developer item on the main Ribbon and then click the Macro Security item
'in the Code panel. In that dialog, choose Macro Settings and check the Trust access to
'the VBA project object model.

'2.' Enable Microsoft Visual Basic For Applications Extensibility 5.3'
'Generates Compile error: User-defined type not defined if missing
'In the VBA editor, go the the Tools menu and choose References.
'In that dialog, scroll down to and check the entry for
'Microsoft Visual Basic For Applications Extensibility 5.3
'
'
''''''''''''''''''''''''''''''''''''''''''''''
'References:
''''''''''''''''''''''''''''''''''''''''''''''
'General VBE:              http://www.cpearson.com/excel/vbe.aspx
'Remove/Replace VBE lines: http://vangelder.orconhosting.net.nz/excel/removecomment.html
'List VBE Procedures:      http://stackoverflow.com/questions/2630872/how-to-get-the-list-of-function-and-sub-of-a-given-module-name-in-excel-vba
'
'
''''''''''''''''''''''''''''''''''''''''''''''
'Code:
''''''''''''''''''''''''''''''''''''''''''''''
Private Sub DummySubContainingTextToBeReplaced()

'The '.' in '.Top' is in character position 4
'        1         2
'2345678901234567890
'  .Top = xxx
End Sub



Public Sub LookButDontTouch()
  Call VbeFindReplace(nOptionLOOK_BUT_DONT_TOUCH)
End Sub

Public Sub ReplaceOldStringWithNewString()
  Call VbeFindReplace(nOptionREPLACE_OLD_STRING_WITH_NEW_STRING)
End Sub

Public Sub ReplaceNewStringWithOldString()
  Call VbeFindReplace(nOptionREPLACE_NEW_STRING_WITH_OLD_STRING)
End Sub

Public Sub VbeFindReplace(nOption As Integer)
  'This finds and replaces code in the Active Workbook
  '
  'NOTE: Code in any module named 'ModVbeFindReplace' will NEVER be modified.
  
  Dim vbc As VBComponent
  
  Dim iModuleType As Integer
  
  Dim i As Long
  Dim iReplacementCount As Long
  
  Dim bProcessThisModule As Boolean
  Dim bTextModule As Boolean
  
  Dim s As String
  Dim sData As String
  Dim sActualModuleName As String
  Dim sModuleType As String
  Dim sSheetName As String
  
  Debug.Print
  Debug.Print "------------------------------"
  Debug.Print "Processing Started on " & Now()
  Debug.Print "File: '" & ActiveWorkbook.Name & "'"
  Debug.Print "Sheet Name (Excel Tab): '" & sDisplaySheetNAME & "'"
  Debug.Print "Module Name: '" & sDisplaySheetNAME & "'"
  Debug.Print "Old String: " & sOldString & "'"
  Debug.Print "New String: " & sNewString & "'"
  Debug.Print "Action To Be Taken: " & DisplayOptionValue(nOption)
  Debug.Print
  
  
  ''''''''''''''''''''''''''''''''''''''''''''''
  'Process each component in the Active Workbook
  ''''''''''''''''''''''''''''''''''''''''''''''
  For Each vbc In ActiveWorkbook.VBProject.VBComponents
    
    'Get the Component Name
    sActualModuleName = Trim(vbc.Name)
    
    'Get the module type
    'Determine if the module can be processed (i.e. contains text)
    'Get the module type description
    iModuleType = vbc.Type
    bTextModule = IsTextModule(iModuleType)
    sModuleType = GetModuleTypeDescription(iModuleType)

    'Get the 'Sheet Name' if the module is a Worksheet or a Chart
    sSheetName = ""
    If iModuleType = vbext_ct_Document Then
      'Get the Sheet Name (blank if both are the same and doesn't start with "Sheet")
      sSheetName = Trim(vbc.Properties("Name"))
    End If
    
    ''''''''''''''''''''''''''''''''''''''''''''''
    'Determine if the module (Sheet) is to be processed or not
    ''''''''''''''''''''''''''''''''''''''''''''''
    bProcessThisModule = False
    If Len(Trim(sDisplaySheetNAME)) = 0 And Len(Trim(sModuleName)) = 0 Then
    
      'Process all code modules
      bProcessThisModule = True
      
    ElseIf Trim(UCase(sModuleName)) = "THISWORKBOOK" And UCase(sActualModuleName) = Trim(UCase(sModuleName)) Then
    
      'Process 'ThisWorkbook' only
      bProcessThisModule = True
      
    ElseIf Len(Trim(sDisplaySheetNAME)) > 0 And UCase(Trim(sDisplaySheetNAME)) = UCase(sSheetName) Then
    
      'Process a TAB whose Sheet Name is a specific name (e.g. 'Main')
      bProcessThisModule = True
      
    ElseIf UCase(sDisplaySheetNAME) = "" And UCase(sActualModuleName) = Trim(UCase(sModuleName)) Then
    
      'Process a specific module name
      bProcessThisModule = True
      
    End If
    
    If nOption <> nOptionLOOK_BUT_DONT_TOUCH Then
      If UCase(sActualModuleName) = UCase(sNeverModifyModuleNAME) Then
    
        'NEVER Process the Module that contains this code (when a change is to take place)
        bProcessThisModule = False
      
      End If
    End If
    
    
    ''''''''''''''''''''''''''''''''''''''''''''''
    'Output a 'header line' for the module
    ''''''''''''''''''''''''''''''''''''''''''''''
    sData = sModuleType & ":  '" & sActualModuleName & "'"
    If Len(sSheetName) > 0 Then
      sData = sData & "  (Sheet Name: '" & sSheetName & "')"
    End If
    
    If bProcessThisModule = False Then
      sData = sData & " (THIS MODULE NOT PROCESSED)"
    End If
    Debug.Print sData
    
    
    ''''''''''''''''''''''''''''''''''''''''''''''
    'Process the module if it is a text module and processing is ALLOWED
    ''''''''''''''''''''''''''''''''''''''''''''''
    If bTextModule = True And bProcessThisModule = True Then
      i = 1
      Do Until i > vbc.CodeModule.CountOfLines
        s = vbc.CodeModule.Lines(i, 1)
        
        Select Case nOption
        
          Case nOptionLOOK_BUT_DONT_TOUCH
            
            'Output the matching line
            If InStr(s, sOldString) > 0 Then
              Debug.Print i & "  " & s
            End If
          
          Case nOptionREPLACE_OLD_STRING_WITH_NEW_STRING
          
            'Replace 'Old String' with 'New String'
            If InStr(s, sOldString) > 0 Then
              Debug.Print i & " From:  " & s
              iReplacementCount = iReplacementCount + 1
        
              s = Replace(s, sOldString, sNewString)
              vbc.CodeModule.ReplaceLine i, s
              Debug.Print i & " To:    " & s
              Debug.Print
            End If
            
          Case nOptionREPLACE_NEW_STRING_WITH_OLD_STRING
            
            'Replace 'New String' with 'Old String'
            If InStr(s, sNewString) > 0 Then
              Debug.Print i & " From:  " & s
              iReplacementCount = iReplacementCount + 1
        
              s = Replace(s, sNewString, sOldString)
              vbc.CodeModule.ReplaceLine i, s
              Debug.Print i & " To:    " & s
              Debug.Print
            End If
            
        End Select
        
        i = i + 1
      Loop
    End If
  
  Next vbc
  
  Debug.Print
  Debug.Print iReplacementCount & " data lines were modified."
  
End Sub

Function IsTextModule(iModuleType As Integer) As Boolean
  'This returns 'True' if the module is a 'Text Module'

  Dim bTextModule As Boolean

  'Determine if the module can be processed (i.e. contains text)
  Select Case iModuleType
    
    Case vbext_ct_StdModule
      bTextModule = True                           'Standard code module
        
    Case vbext_ct_ClassModule
      bTextModule = True                           'Class module
      
    Case vbext_ct_MSForm
      bTextModule = False                          'UserForm module
      
    Case vbext_ct_ActiveXDesigner
      bTextModule = False                          'Active X designer module
      
    Case vbext_ct_Document
      bTextModule = True                           'Worksheet (or chart)
      
    Case Else
      bTextModule = False                          'Unknownn type
            
  End Select

  IsTextModule = bTextModule
  
End Function

Function GetModuleTypeDescription(iModuleType As Integer) As String
  'This returns a text description of the 'Module type'

  Dim sModuleType As String

  'Get a text description of the 'Module Type'
  Select Case iModuleType
    
    Case vbext_ct_StdModule
      sModuleType = "STANDARD MODULE"             'Standard code module
        
    Case vbext_ct_ClassModule
      sModuleType = "CLASS MODULE"                'Class module
      
    Case vbext_ct_MSForm
      sModuleType = "USERFORM MODULE"             'UserForm module
      
    Case vbext_ct_ActiveXDesigner
      sModuleType = "ACTIVE X DESIGNER MODULE"    'Active X designer module
      
    Case vbext_ct_Document
      sModuleType = "DOCUMENT MODULE"             'Worksheet (or chart)
      
    Case Else
      sModuleType = "UNKNOWN MODULE"               'Unknownn type
            
  End Select

  GetModuleTypeDescription = sModuleType
  
End Function

Function DisplayOptionValue(nOption As Integer) As String
  'This returns a string indicating the option selected

  Select Case nOption
        
    Case nOptionLOOK_BUT_DONT_TOUCH
      DisplayOptionValue = "FIND ALL 'Old String' Matches"
          
    Case nOptionREPLACE_OLD_STRING_WITH_NEW_STRING
      DisplayOptionValue = "REPLACE 'Old String' WITH 'New String'"
          
            
    Case nOptionREPLACE_NEW_STRING_WITH_OLD_STRING
      DisplayOptionValue = "REPLACE 'New String' WITH 'Old String'"
                        
  End Select

End Function