[µo°Ý] Macro List

PJChen

¤j¤j¦n,

ÀHµÛ¨Ï¥ÎVBAªº¶q¼W¥[«á,¦³®É­n§äµ{¦¡ªº©Ò¦b,­nªá¦n¤@·|¨à¥\¤Ò,
¥B¦P¤@­ÓModule·|¦³¦h­ÓSub ¦WºÙ
·Q½Ð°Ý¯à§_±N¼Ò²Õ¤¤ªº©Ò¦³Module & Sub ¦WºÙ
¨Ì§Ç±NModule1.Sub ¦WºÙ
Module2.Sub ¦WºÙ ....
§â¥¦¥þ³¡List ¦bTXT or excel¤¤ ? ¤è«K¬d§äµ{¦¡?

quickfixer

¦^´_ 1# PJChen

ÀH«Kgoogle´N¦³¤@°ï²{¦¨ªº¸Ñµª¤F,³o­Ó¦C¥X¬¡­¶Á¡¤¤©Ò¦³ªºmacro name
http://www.vbaexpress.com/kb/getarticle.php?kb_id=398
excel 2016¥i¥¿±`°õ¦æ

quickfixer

¦^´_ 1# PJChen

³o­Ó¦C¥X¨ä¥¦Àɮתº macro name §t module name
https://superuser.com/questions/1365898/macro-vba-code-to-list-and-print-names-and-code-of-all-macros-in-a-workbook
­nuserform¡Bcommandbutton¡Blistbox
¨ä¤¤ objXLABC.Close ­n§ï¦¨objXLABC.Close False,¤£µMµ{¦¡·|¥d¦í
excel 2016¥i¥¿±`°õ¦æ

­ã´£³¡ªL

¤U¸ü (7.96 KB)

2020-4-17 09:59

PJChen

¦^´_ 4# ­ã´£³¡ªL
¦^´_ 3# quickfixer
¤G¦ì¦n,
§Ú§âµ{¦¡§ì¤U¨Ó·Q´ú¸Õ,¦ý¥d¦í¤F,¥i¥HÀ°§Ú¬Ý¬Ý¶Ü?

¤U¸ü (11.73 KB)

2020-4-17 22:38

1. Sub Command1_Click()
   
2.     ' Declare variables to access the Excel workbook.
   
3.     Dim objXLApp As Excel.Application
   
4.     Dim objXLWorkbooks As Excel.Workbooks
   
5.     Dim objXLABC As Excel.Workbook
   
6. 
   
7.     ' Declare variables to access the macros in the workbook.
   
8.     Dim objProject As VBIDE.VBProject
   
9.     Dim objComponent As VBIDE.VBComponent
   
10.     Dim objCode As VBIDE.CodeModule
    
11. 
    
12.     ' Declare other miscellaneous variables.
    
13.     Dim iLine As Integer
    
14.     Dim sProcName As String
    
15.     Dim pk As vbext_ProcKind
    
16. 
    
17.     ' Open Excel, and open the workbook.
    
18.     Set objXLApp = New Excel.Application
    
19.     Set objXLWorkbooks = objXLApp.Workbooks   
    
20.     Set objXLABC = objXLWorkbooks.Open("C:\ABC.XLS")
    
21. 
    
22.     ' Empty the list box.
    
23.     List1.Clear
    
24. 
    
25.     ' Get the project details in the workbook.
    
26.     Set objProject = objXLABC.VBProject
    
27. 
    
28.     ' Iterate through each component in the project.
    
29.     For Each objComponent In objProject.VBComponents
    
30. 
    
31.         ' Find the code module for the project.
    
32.         Set objCode = objComponent.CodeModule
    
33. 
    
34.         ' Scan through the code module, looking for procedures.
    
35.         iLine = 1
    
36.         Do While iLine < objCode.CountOfLines
    
37.             sProcName = objCode.ProcOfLine(iLine, pk)
    
38.             If sProcName <> "" Then
    
39.                 ' Found a procedure. Display its details, and then skip
    
40.                 ' to the end of the procedure.
    
41.                 List1.AddItem objComponent.Name & vbTab & sProcName
    
42.                 iLine = iLine + objCode.ProcCountLines(sProcName, pk)
    
43.             Else
    
44.                 ' This line has no procedure, so go to the next line.
    
45.                 iLine = iLine + 1
    
46.             End If
    
47.         Loop
    
48.         Set objCode = Nothing
    
49.         Set objComponent = Nothing
    
50.     Next
    
51. 
    
52.     Set objProject = Nothing
    
53. 
    
54.     ' Clean up and exit.
    
55.     objXLABC.Close
    
56.     objXLApp.Quit
    
57. End Sub

½Æ»s¥N½X

quickfixer

¦^´_ 5# PJChen


ºô§}¤ºªºmicrosoft³sµ²¤£¬O¦³»¡©ú?»Ý¥ý³]©w¤Þ¥Î¶µ¥Ø
Microsoft Visual Basic for Applications Extensibility 5.3
¥´¤Ä
   

¤U¸ü (57.61 KB)

2020-4-18 00:05

²Ä¤@­Ócode ¸Ì­±¤]¦³»¡©ú­n¤Þ¥Î

¤U¸ü (67.98 KB)

2020-4-18 00:03

¤£µM§â²Ä¤@­Ócode¸Ì­±ªº³o2¦æ
On Error Resume Next
    ThisWorkbook.VBProject.References.AddFromGuid _
    "{0002E157-0000-0000-C000-000000000046}", 5, 3
¥[¨ì²Ä¤G­Ócode¸Ì­±³Ì«e­±,¥i¥H§ï¦¨¥Îµ{¦¡½X¦Û°Ê¤Þ¥Î

n7822123

¦^´_ 1# PJChen


¤F¸ÑVBE¬[ºc´N¤£Ãø¼g........°ò¥»¤W§AÀ³¸Ó¥u·Q­n¥Î¦Ó¤w

©Ò¥H§Ú¤£¦h°µ¸ÑÄÀ¡A·Q¤F¸Ñ½Ð«ö "F1"

1. Sub §ä¥¨¶°¦WºÙ()
   
2. 
   
3. Application.DisplayAlerts = False
   
4. 
   
5. [A1] = "¬¡­¶Ã¯¦WºÙ"
   
6. [B1] = "¼Ò²Õ¦WºÙ"
   
7. [C1] = "µ{§Ç¦WºÙ"
   
8. 
   
9. R = 2
   
10. For Each wb In Workbooks
    
11.   For Each VBC In wb.VBProject.VBComponents
    
12.     Select Case VBC.Type
    
13.       Case 1  '1=¤@¯ë¼Ò²Õ
    
14.         LS = VBC.CodeModule.CountOfLines
    
15.         'Debug.Print LS
    
16.         LineStr = VBC.CodeModule.Lines(1, LS)
    
17.         Lines = Split(LineStr, vbCrLf)
    
18.         For Each Line In Lines
    
19.             If Line Like "Sub*" Then
    
20.               Cells(R, 1) = wb.Name
    
21.               Cells(R, 2) = VBC.Name
    
22.               Cells(R, 3) = Line
    
23.               R = R + 1
    
24.             End If
    
25.         Next 'Line
    
26.       Case Else
    
27.     End Select
    
28.   Next  'VBC
    
29. Next 'wb
    
30. 
    
31. Columns.AutoFit
    
32. 
    
33. End Sub

½Æ»s¥N½X

§ä¥¨¶°¦WºÙ.rar (12.46 KB)

¤U¸ü¦¸¼Æ: 4

2020-5-18 20:58