Finding an instruction in a macro

**Raiby** · 02-19-2007, 04:14 AM

Hi all

My problem is not very easy. That's why i don't know if i am on the good topic. I hesitated between the three topics: miscellaneous, general and programming. I will be long and forgive me. So, i begin. I have created some files with macros. What i want to do is to find an instruction. For example, on my file called example.xls, i have the following macro:

 
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, Orientation:=xlTopToBottom
ActiveSheet.Range("B1").End(xlDown).Offset(1, 0).Select
'Select the first empty cell in the column
ActiveCell.Offset(-1, 0).Select

Later, i want to create a new file with a macro and i want to select the first empty cell in the column. Then i need to use the good instruction. But i don't know it. In fact, i'm not a great programmer. Then i want to search it in my files thanks to the explorer with Ctrl+F. In the explorer, i write: "select the first empty cell in the column". I fact i want to find:"ActiveSheet.Range("B1").End(xlDown).Offset(1, 0).Select".The problem is that the macros are hidden. If i have the following text in the cell A1: "Box". In the explorer, i write "box" and the explorer shows me all results or all files with the text "box". But if i write "select the first empty cell in the column", there's no result because this instruction isn't in a cell but in a macro. Is there a method to find an instruction in a macro. Of course, i have many files and i don't remember. I hope be clear

Thanks in advance.

**Carim** · 02-19-2007, 05:53 AM

Hi,

Most probably this feature is possible with VBE ...

**Carim** · 02-19-2007, 05:56 AM

Hi again,

Code proposed by RB Smissaert ...

Sub SearchWBsForCode() 

   Dim strTextToFind As String 
   Dim strFolder As String 
   Dim arr 
   Dim i As Long 
   Dim strWB As String 
   Dim VBProj As VBProject 
   Dim VBComp As VBComponent 
   Dim lStartLine As Long 
   Dim lEndLine As Long 
   Dim lFound As Long 
   Dim lType As Long 
   Dim lSkipped As Long 
   Dim oWB As Workbook 
   Dim bOpen As Boolean 
   Dim bNewBook As Boolean 

   strTextToFind = InputBox("Type the text to find", _ 
                            "finding text in VBE") 

   If Len(strTextToFind) = 0 Or StrPtr(strTextToFind) = 0 Then 
      Exit Sub 
   End If 

   strFolder = GetDirectory() 

   If Len(strFolder) = 0 Then 
      Exit Sub 
   End If 

   lType = Application.InputBox("Type file type to search" & _ 
                                vbCrLf & vbCrLf & _ 
                                "1. Only .xls files" & vbCrLf & _ 
                                "2. Only .xla files" & vbCrLf & _ 
                                "3. Either file type", _ 
                                "finding text in VBE", 1, Type:=1) 

   Select Case lType 
      Case 1 
         arr = RecursiveFindFiles(strFolder, "*.xls", True, True) 
      Case 2 
         arr = RecursiveFindFiles(strFolder, "*.xla", True, True) 
      Case 3 
         arr = RecursiveFindFiles(strFolder, "*.xl*", True, True) 
      Case Else 
         Exit Sub 
   End Select 

   With Application 
      .ScreenUpdating = False 
      .EnableEvents = False 
      .DisplayAlerts = False 
   End With 

   For i = 1 To UBound(arr) 

      Application.StatusBar = i & "/" & UBound(arr) & _ 
                              " - Searching " & arr(i) 

      strWB = FileFromPath(arr(i)) 

      On Error Resume Next 
      Set oWB = Workbooks(strWB) 

      If oWB Is Nothing Then 
         bOpen = False 
         Workbooks.Open arr(i) 
      Else 
         'for preventing closing WB's that are open already 
         bOpen = True 
         Set oWB = Nothing 
      End If 

      bNewBook = True 

      For Each VBComp In Workbooks(strWB).VBProject.VBComponents 

         If Err.Number = 50289 Then   'for protected WB's 
            lSkipped = lSkipped + 1 
            Err.Clear 
            GoTo PAST 
         End If 

         lEndLine = VBComp.CodeModule.CountOfLines 
         If VBComp.CodeModule.Find(strTextToFind, _ 
                                   lStartLine, _ 
                                   1, _ 
                                   lEndLine, _ 
                                   -1, _ 
                                   False, _ 
                                   False) = True Then 

            If bNewBook = True Then 
               lFound = lFound + 1 
               bNewBook = False 
            End If 

            Application.ScreenUpdating = True 

            If MsgBox("Workbook: " & arr(i) & vbCrLf & _ 
                      "VBComponent: " & VBComp.Name & vbCrLf & _ 
                      "Line number: " & lStartLine & _ 
                      vbCrLf & vbCrLf & _ 
                      "WB's found so far: " & lFound & vbCrLf & _ 
                      "Protected WB's skipped: " & lSkipped & _ 
                      vbCrLf & vbCrLf & _ 
                      "Stop searching?", _ 
                      vbYesNo + vbDefaultButton1 + vbQuestion, _ 
                      i & "/" & UBound(arr) & _ 
                      " - found " & strTextToFind) = vbYes Then 

               With Application 
                  .StatusBar = False 
                  .EnableEvents = True 
                  .DisplayAlerts = True 
               End With 

               With VBComp.CodeModule.CodePane 
                  .SetSelection lStartLine, 1, lStartLine, 1 
                  .Show 
               End With 
               Exit Sub 
            End If 
            Application.ScreenUpdating = False
         End If 
      Next 

PAST: 
      If bOpen = False Then 
         Workbooks(strWB).Close savechanges:=False 
      End If 
      On Error GoTo 0 
   Next 

   On Error Resume Next 
   If bOpen = False Then 
      Workbooks(strWB).Close savechanges:=False 
   End If 

   With Application 
      .ScreenUpdating = True 
      .StatusBar = False 
      .EnableEvents = True 
      .DisplayAlerts = True 
   End With 

   MsgBox lFound & " WB's found with " & strTextToFind & " in VBE" & _ 
          vbCrLf & vbCrLf & _ 
          "protected WB's skipped: " & lSkipped, , _ 
          "finding text in VBE" 
End Sub

**Raiby** · 02-20-2007, 09:38 AM

Hi Carim

Thanks for the response but i'm sorry. It's a bit complicated for me. I have the following message (compilation error):"User Defined type not defined (the instruction VBProj As VBProject is highlighted in blue). It's maybe a problem with an object in Microsoft Object Library.I'm lost. Thank you for your help.