|
考虑到调用Sub子过程可能会是跨模块之间的调用……
所以附件做了更新:- Sub SubCodeCopy2() 'by kagawa 2014/02/06
-
- With Application.FileDialog(msoFileDialogFolderPicker)
- If .Show Then myPath = .SelectedItems(1) Else Exit Sub
- End With
-
- Open ActiveWorkbook.Path & "" & Replace(Mid(myPath, 4), "", "_") & "_VBASubCode2.txt" For Output As #1
- Print #1, myPath; vbCrLf
-
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- myFile = Dir(myPath & "\*.xls")
- Do
- On Error Resume Next
- With Workbooks.Open(myFile)
- FileSubCnt = 0
- mySubName = ""
- Print #1, " "; vbCrLf; String(20, "="); vbCrLf; myFile; vbCrLf; " "
- For Each vbc In .VBProject.VBComponents
- With vbc.CodeModule
- If .CountOfLines Then
- For i = 1 To .CountOfLines
- myStr = .Lines(i, 1)
- If myStr Like "Sub *" Or myStr Like " Sub *" Then
- myStr = Mid(myStr, InStr(myStr, "Sub ") + 4)
- myStr = Left(myStr, InStr(myStr, "(") - 1)
- mySubName = mySubName & "|" & myStr
- End If
- Next
- End If
- End With
- Next
-
- mySub = Split(mySubName, "|")
- mySubName = "|"
- For Each vbc In .VBProject.VBComponents
- With vbc.CodeModule
- If .CountOfLines Then
- For j = 1 To UBound(mySub)
- For i = 1 To .CountOfLines
- myStr = Trim(.Lines(i, 1))
- If myStr = mySub(j) Or myStr Like mySub(j) & " ,*" Or myStr = "Call " & mySub(j) Or myStr Like "Call " & mySub(j) & "(*" Then
- If InStr(mySubName, "|" & mySub(j) & "|") = 0 Then mySubName = mySubName & mySub(j) & "|"
- End If
- Next
- Next
- End If
- End With
- Next
-
- If mySubName <> "|" Then
- mySub = Split(mySubName, "|")
- For Each vbc In .VBProject.VBComponents
- With vbc.CodeModule
- If .CountOfLines Then
- SubCnt = 0
- ' Print #1, " "; vbCrLf; String(10, "-"); .Name; String(10, "-"); vbCrLf
- For j = 1 To UBound(mySub) - 1
- For i = 1 To .CountOfLines
- myStr = .Lines(i, 1)
- If myStr Like "*Sub " & mySub(j) & "*" Then flag = True: SubCnt = SubCnt + 1: If SubCnt = 1 Then Print #1, " "; vbCrLf; String(10, "-"); .Name; String(10, "-"); vbCrLf Else Print #1, " "; vbCrLf
- If flag And myStr Like "*End Sub*" Then flag = False: Print #1, myStr; vbCrLf
- If flag Then Print #1, myStr
- Next
- Next
- If SubCnt Then Print #1, vbCrLf; " "; "Get "; SubCnt; " SubCodes in Module ["; .Name; "]"
- FileSubCnt = FileSubCnt + SubCnt
- End If
- End With
- Next
- End If
- If FileSubCnt Then Print #1, vbCrLf; " "; "Get total "; FileSubCnt; " SubCodes in File <<"; myFile; ">>" Else Print #1, "No SubCode Exist"
- End With
- Windows(myFile).Close False
- myFile = Dir
- FileCount = FileCount + 1
- Loop Until myFile = ""
- Application.ScreenUpdating = True
-
- Print #1, vbCrLf; String(20, "="); vbCrLf; " "; FileCount; " Files Checked"
- Close #1
-
- MsgBox FileCount & " Files Checked !"
- End Sub
复制代码 |
|