Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

I am trying to modify the code below to search the VBA of all sheets in my workbook…

  1. User

    I am trying to modify the code below to search the VBA of all sheets in my workbook where there is a reference to a Module (Call Module). Unfortunately I can not get the code to work as required. Can you please make corrections: Sub SearchModuleLinks() 'SEARCHES VBA OF ALL SHEETS CALLING MODULES Dim wb As Workbook Dim linkedDocsWs As Worksheet Dim lastRow As Long Dim ws As Worksheet Dim moduleCode As String Dim addressPath As String Dim lineIndex As Long Dim startPos As Long Dim endPos As Long Set wb = ThisWorkbook Set linkedDocsWs = wb.Sheets("Test Sheet") For Each ws In wb.Sheets On Error Resume Next lineIndex = 1 ' Check if the sheet has a code module If ws.Type = xlWorksheet Then ' Get the module code of the sheet moduleCode = wb.VBProject.VBComponents(ws.CodeName).codeModule.Lines(1, wb.VBProject.VBComponents(ws.CodeName).codeModule.CountOfLines) startPos = InStr(1, moduleCode, "Module") Do Until startPos = 0 endPos = InStr(startPos, moduleCode, """") If endPos > startPos Then addressPath = Mid(moduleCode, startPos, endPos - startPos) lastRow = linkedDocsWs.Cells(linkedDocsWs.Rows.Count, "D").End(xlUp).Row + 1 linkedDocsWs.Cells(lastRow, "G").Value = addressPath linkedDocsWs.Cells(lastRow, "H").Value = "'" & ws.Name End If startPos = InStr(startPos + 1, moduleCode, "Module") Loop End If On Error GoTo 0 Next ws 'Call Module32.SearchMODLinkedDocs End Sub

  2. Assistant

    Sub SearchModuleLinks() 'SEARCHES VBA OF ALL SHEETS CALLING MODULES Dim wb As Workbook Dim linkedDocsWs As Worksheet Dim lastRow As Long Dim ws As Worksheet Dim modComponent As Object Dim moduleCode As String Dim addressPath As String Dim lineIndex As Long Dim startPos As Long Dim endPos As Long

    Set wb = ThisWorkbook
    Set linkedDocsWs = wb.Sheets("Test Sheet")
    
    For Each ws In wb.Sheets
        On Error Resume Next
        lineIndex = 1
        ' Check if the sheet has a code module
        If ws.Type = xlWorksheet Then
            ' Loop through each module in the sheet
            For Each modComponent In wb.VBProject.VBComponents
                If modComponent.Type = 1 Then ' 1 = vbext_ct_StdModule
                    ' Get the module code of the sheet
                    moduleCode = modComponent.CodeModule.Lines(1, modComponent.CodeModule.CountOfLines)
                    
                    ' Search for the "Module" keyword
                    startPos = InStr(1, moduleCode, "Module")
                    Do Until startPos = 0
                        endPos = InStr(startPos, moduleCode, """")
                        If endPos > startPos Then
                            ' Extract the path
                            addressPath = Mid(moduleCode, startPos, endPos - startPos)
                            lastRow = linkedDocsWs.Cells(linkedDocsWs.Rows.Count, "D").End(xlUp).Row + 1
                            linkedDocsWs.Cells(lastRow, "G").Value = addressPath
                            linkedDocsWs.Cells(lastRow, "H").Value = "'" & ws.Name
                        End If
                        startPos = InStr(startPos + 1, moduleCode, "Module")
                    Loop
                End If
            Next modComponent
        End If
        On Error GoTo 0
    Next ws
    
    'Call Module32.SearchMODLinkedDocs
    

    End Sub

  3. User

    I believe there might need to be major changes to the code. Reference to Modules start 'Call Module' as in this example "Call Module32.SearchMODLinkedDocs" and there is no distinct end to the statement such as ". Usually the Call Module statement is on a line of its own.

  4. Assistant

    I see, here's an updated code that searches for "Call Module" statements in the VBA code:

    Sub SearchModuleLinks()
        ' SEARCHES VBA OF ALL SHEETS CALLING MODULES
        Dim wb As Workbook
        Dim linkedDocsWs As Worksheet
        Dim lastRow As Long
        Dim ws As Worksheet
        Dim moduleCode As String
        Dim startPos As Long
        Dim endPos As Long
        Dim lineIndex As Long
        
        Set wb = ThisWorkbook
        Set linkedDocsWs = wb.Sheets("Test Sheet")
        
        For Each ws In wb.Sheets
            On Error Resume Next
            lineIndex = 1
            
            ' Check if the sheet has a code module
            If ws.Type = xlWorksheet Then
                moduleCode = wb.VBProject.VBComponents(ws.CodeName).CodeModule.Lines(1, wb.VBProject.VBComponents(ws.CodeName).CodeModule.CountOfLines)
                
                ' Search for "Call Module" in the module code
                startPos = InStr(1, moduleCode, "Call Module")
                Do Until startPos = 0
                    ' Extract the module name
                    endPos = InStr(startPos, moduleCode, " ")
                    If endPos > startPos Then
                        Dim moduleName As String
                        moduleName = Mid(moduleCode, startPos, endPos - startPos)
                        
                        lastRow = linkedDocsWs.Cells(linkedDocsWs.Rows.Count, "D").End(xlUp).Row + 1
                        linkedDocsWs.Cells(lastRow, "G").Value = moduleName
                        linkedDocsWs.Cells(lastRow, "H").Value = "'" & ws.Name
                    End If
                    startPos = InStr(startPos + 1, moduleCode, "Call Module")
                Loop
            End If
            
            On Error GoTo 0
        Next ws
    End Sub
    

    This code searches for occurrences of Call Module in the module code, extracts the module name, and adds it to the worksheet specified by linkedDocsWs. The module name is extracted as text after the Call Module statement until a space character is encountered.

  5. User

    What about if we searched all the VBA in the sheets of the workbook for the word starting "Module" and listed the search in column G of the worksheet 'Test Sheet' and the location where the word is found in column H

  6. Assistant

    Certainly! Here's an updated code that searches for the word "Module" in all the VBA code of the sheets and lists the search results in columns G and H of the "Test Sheet" worksheet:

    Sub SearchModuleLinks()
        ' SEARCHES VBA OF ALL SHEETS FOR "MODULE"
        Dim wb As Workbook
        Dim linkedDocsWs As Worksheet
        Dim lastRow As Long
        Dim ws As Worksheet
        Dim moduleCode As String
        Dim startPos As Long
        Dim endPos As Long
        Dim lineIndex As Long
        
        Set wb = ThisWorkbook
        Set linkedDocsWs = wb.Sheets("Test Sheet")
        
        For Each ws In wb.Sheets
            On Error Resume Next
            lineIndex = 1
            
            ' Check if the sheet has a code module
            If ws.Type = xlWorksheet Then
                moduleCode = wb.VBProject.VBComponents(ws.CodeName).CodeModule.Lines(1, wb.VBProject.VBComponents(ws.CodeName).CodeModule.CountOfLines)
                
                ' Search for "Module" in the module code
                startPos = InStr(1, moduleCode, "Module")
                Do Until startPos = 0
                    ' Extract the module name
                    endPos = InStr(startPos, moduleCode, vbCrLf)
                    If endPos > startPos Then
                        Dim moduleName As String
                        moduleName = Mid(moduleCode, startPos, endPos - startPos)
                        
                        lastRow = linkedDocsWs.Cells(linkedDocsWs.Rows.Count, "G").End(xlUp).Row + 1
                        linkedDocsWs.Cells(lastRow, "G").Value = moduleName
                        linkedDocsWs.Cells(lastRow, "H").Value = "'" & ws.Name
                    End If
                    startPos = InStr(startPos + 1, moduleCode, "Module")
                Loop
            End If
            
            On Error GoTo 0
        Next ws
    End Sub
    

    This code searches for occurrences of the word "Module" in the module code of each sheet and extracts the module name. It then adds the module name to column G and the sheet name to column H of the "Test Sheet" worksheet. The search results are listed row by row starting from the first empty row in columns G and H.

  7. User

    In column H, I have values that have several duplicates in the same column. I want a formula in column J that will extract the unique values without the duplicates

  8. Assistant

    To extract the unique values from column H without duplicates and display them in column J, you can use the following VBA code:

    Sub ExtractUniqueValues()
        Dim linkedDocsWs As Worksheet
        Dim lastRow As Long
        
        Set linkedDocsWs = ThisWorkbook.Sheets("Test Sheet")
        
        lastRow = linkedDocsWs.Cells(linkedDocsWs.Rows.Count, "H").End(xlUp).Row
        
        ' Copy values from column H to column J
        linkedDocsWs.Range("H1:H" & lastRow).Copy Destination:=linkedDocsWs.Range("J1")
        
        ' Remove duplicates in column J
        linkedDocsWs.Range("J1:J" & lastRow).RemoveDuplicates Columns:=1, Header:=xlNo
    End Sub
    

    This code copies the values from column H to column J in the "Test Sheet" worksheet. Then, it removes duplicates in column J, keeping only the unique values. The resulting unique values will be displayed starting from cell J1.

  9. User

    Can you also apply a data sort after the removal of duplicates

  10. Assistant

    Certainly! Here's the updated code that applies a data sort after removing duplicates in column J:

    Sub ExtractUniqueValuesWithSort()
        Dim linkedDocsWs As Worksheet
        Dim lastRow As Long
        
        Set linkedDocsWs = ThisWorkbook.Sheets("Test Sheet")
        
        lastRow = linkedDocsWs.Cells(linkedDocsWs.Rows.Count, "H").End(xlUp).Row
        
        ' Copy values from column H to column J
        linkedDocsWs.Range("H1:H" & lastRow).Copy Destination:=linkedDocsWs.Range("J1")
        
        ' Remove duplicates in column J
        linkedDocsWs.Range("J1:J" & lastRow).RemoveDuplicates Columns:=1, Header:=xlNo
        
        ' Sort column J in ascending order
        linkedDocsWs.Sort.SortFields.Clear
        linkedDocsWs.Sort.SortFields.Add Key:=linkedDocsWs.Range("J1:J" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending
        linkedDocsWs.Sort.SetRange linkedDocsWs.Range("J1:J" & lastRow)
        linkedDocsWs.Sort.Header = xlNo
        linkedDocsWs.Sort.MatchCase = False
        linkedDocsWs.Sort.Orientation = xlTopToBottom
        linkedDocsWs.Sort.SortMethod = xlPinYin
        linkedDocsWs.Sort.Apply
    End Sub
    

    This code first removes duplicates from column J after copying values from column H. It then applies a data sort to column J in ascending order. The result will be the unique values in column J sorted in ascending order.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.