I am trying to modify the code below to search the VBA of all sheets in my workbook…
- 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
- 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.SearchMODLinkedDocsEnd Sub
- 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.
- 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 SubThis code searches for occurrences of
Call Modulein the module code, extracts the module name, and adds it to the worksheet specified bylinkedDocsWs. The module name is extracted as text after theCall Modulestatement until a space character is encountered. - 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
- 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 SubThis 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.
- 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
- 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 SubThis 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.
- User
Can you also apply a data sort after the removal of duplicates
- 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 SubThis 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.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.