ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] Macro List

[µo°Ý] Macro List

¤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§äµ{¦¡?

¦^´_ 1# PJChen

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

TOP

¦^´_ 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¥¿±`°õ¦æ

TOP

xx001.gif
2020-4-17 09:59

TOP

¦^´_ 4# ­ã´£³¡ªL
¦^´_ 3# quickfixer
¤G¦ì¦n,
§Ú§âµ{¦¡§ì¤U¨Ó·Q´ú¸Õ,¦ý¥d¦í¤F,¥i¥HÀ°§Ú¬Ý¬Ý¶Ü?
20200417.223301.jpg
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.     ' Declare variables to access the macros in the workbook.
  7.     Dim objProject As VBIDE.VBProject
  8.     Dim objComponent As VBIDE.VBComponent
  9.     Dim objCode As VBIDE.CodeModule

  10.     ' Declare other miscellaneous variables.
  11.     Dim iLine As Integer
  12.     Dim sProcName As String
  13.     Dim pk As vbext_ProcKind

  14.     ' Open Excel, and open the workbook.
  15.     Set objXLApp = New Excel.Application
  16.     Set objXLWorkbooks = objXLApp.Workbooks   
  17.     Set objXLABC = objXLWorkbooks.Open("C:\ABC.XLS")

  18.     ' Empty the list box.
  19.     List1.Clear

  20.     ' Get the project details in the workbook.
  21.     Set objProject = objXLABC.VBProject

  22.     ' Iterate through each component in the project.
  23.     For Each objComponent In objProject.VBComponents

  24.         ' Find the code module for the project.
  25.         Set objCode = objComponent.CodeModule

  26.         ' Scan through the code module, looking for procedures.
  27.         iLine = 1
  28.         Do While iLine < objCode.CountOfLines
  29.             sProcName = objCode.ProcOfLine(iLine, pk)
  30.             If sProcName <> "" Then
  31.                 ' Found a procedure. Display its details, and then skip
  32.                 ' to the end of the procedure.
  33.                 List1.AddItem objComponent.Name & vbTab & sProcName
  34.                 iLine = iLine + objCode.ProcCountLines(sProcName, pk)
  35.             Else
  36.                 ' This line has no procedure, so go to the next line.
  37.                 iLine = iLine + 1
  38.             End If
  39.         Loop
  40.         Set objCode = Nothing
  41.         Set objComponent = Nothing
  42.     Next

  43.     Set objProject = Nothing

  44.     ' Clean up and exit.
  45.     objXLABC.Close
  46.     objXLApp.Quit
  47. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ quickfixer ©ó 2020-4-18 00:14 ½s¿è

¦^´_ 5# PJChen


ºô§}¤ºªºmicrosoft³sµ²¤£¬O¦³»¡©ú?»Ý¥ý³]©w¤Þ¥Î¶µ¥Ø
Microsoft Visual Basic for Applications Extensibility 5.3
¥´¤Ä
   
Image 4.png
2020-4-18 00:05




²Ä¤@­Ócode ¸Ì­±¤]¦³»¡©ú­n¤Þ¥Î
Image 3.png
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¦Û°Ê¤Þ¥Î

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-5-18 21:02 ½s¿è

¦^´_ 1# PJChen


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

©Ò¥H§Ú¤£¦h°µ¸ÑÄÀ¡A·Q¤F¸Ñ½Ð«ö "F1"
  1. Sub §ä¥¨¶°¦WºÙ()

  2. Application.DisplayAlerts = False

  3. [A1] = "¬¡­¶Ã¯¦WºÙ"
  4. [B1] = "¼Ò²Õ¦WºÙ"
  5. [C1] = "µ{§Ç¦WºÙ"

  6. R = 2
  7. For Each wb In Workbooks
  8.   For Each VBC In wb.VBProject.VBComponents
  9.     Select Case VBC.Type
  10.       Case 1  '1=¤@¯ë¼Ò²Õ
  11.         LS = VBC.CodeModule.CountOfLines
  12.         'Debug.Print LS
  13.         LineStr = VBC.CodeModule.Lines(1, LS)
  14.         Lines = Split(LineStr, vbCrLf)
  15.         For Each Line In Lines
  16.             If Line Like "Sub*" Then
  17.               Cells(R, 1) = wb.Name
  18.               Cells(R, 2) = VBC.Name
  19.               Cells(R, 3) = Line
  20.               R = R + 1
  21.             End If
  22.         Next 'Line
  23.       Case Else
  24.     End Select
  25.   Next  'VBC
  26. Next 'wb

  27. Columns.AutoFit

  28. End Sub
½Æ»s¥N½X
§ä¥¨¶°¦WºÙ.rar (12.46 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

        ÀR«ä¦Û¦b : µÊ®ð¼L¤Ú¤£¦n¡A¤ß¦a¦A¦n¤]¤£¯àºâ¬O¦n¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD