vba 在excel中获取一个模块的宏列表，然后调用所有这些宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28132276/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get a list of the macros of a module in excel, and then call all those macros
提问by Flavinho
Please help with the following:
请帮助解决以下问题：
1) A code that sets a list of all macros of "Module3", and place this list in "Sheet5", starting in cell "E14" below.
1) 设置“Module3”所有宏列表的代码，并将此列表放在“Sheet5”中，从下面的单元格“E14”开始。
2) Then, the code should run all the listed macros
2) 然后，代码应该运行所有列出的宏
I tried with a code that referred VBComponent, but I got an error.
我尝试使用引用的代码VBComponent，但出现错误。
回答by Juan Ruiz de Castilla
Based on my google search, I found the answer That I commented you , but They forgot and important thing, that is check and option to allow you run the macro.
根据我的谷歌搜索，我找到了我评论过你的答案，但他们忘记了重要的事情，那就是检查和允许你运行宏的选项。
First the Function to list all macros in excel and return and string separated by white space:
首先列出excel中的所有宏并返回和用空格分隔的字符串的函数：
Function ListAllMacroNames() As String
Dim pj As VBProject
Dim vbcomp As VBComponent
Dim curMacro As String, newMacro As String
Dim x As String
Dim y As String
Dim macros As String
On Error Resume Next
curMacro = ""
Documents.Add
For Each pj In Application.VBE.VBProjects
For Each vbcomp In pj.VBComponents
If Not vbcomp Is Nothing Then
If vbcomp.CodeModule = "Module_name" Then
For i = 1 To vbcomp.CodeModule.CountOfLines
newMacro = vbcomp.CodeModule.ProcOfLine(Line:=i, _
prockind:=vbext_pk_Proc)
If curMacro <> newMacro Then
curMacro = newMacro
If curMacro <> "" And curMacro <> "app_NewDocument" Then
macros = curMacro + " " + macros
End If
End If
Next
End If
End If
Next
Next
ListAllMacroNames = macros
End Function
The next step, of well could be the first one, you need to change some configuration of the office (Excel) trustcenter, check the follow images:
下一步，当然可以是第一步，你需要更改office（Excel）信任中心的一些配置，查看下图：
Step 1:
第1步：
Step 2:
第2步：
Step 3 (Final) Check the option "rely on access to the data model project vba":
Step 3 (Final) 勾选“依赖访问数据模型项目vba”选项：
Then you need to add this reference to your Excel:
然后您需要将此引用添加到您的 Excel：
Don't worry if you have another version of Microsoft Visual Basic for Applications Extensibility, in this case is 5.3. Check and then accept.Don't forget that you need to find that reference, there is no on the top of the list.
如果您有另一个版本的 Microsoft Visual Basic for Applications Extensibility，请不要担心，在这种情况下是 5.3。检查然后接受。不要忘记您需要找到该参考，列表顶部没有。
Finally you can invoke the ListAllMacroNames ( ) function With This other macro named execute () , Look That I 'm Validated That doesn't call the same macros (execute , ListAllMacroNames ) or could make an infinite loop.
最后，您可以使用另一个名为 execute () 的宏调用 ListAllMacroNames ( ) 函数，看看我已验证它不会调用相同的宏（execute ， ListAllMacroNames ），或者可以进行无限循环。
Public Sub execute()
Dim AppArray() As String
AppArray() = Split(ListAllMacroNames, " ")
For i = 0 To UBound(AppArray)
temp = AppArray(i)
If temp <> "" Then
If temp <> "execute" And temp <> "ListAllMacroNames" Then
Application.Run (AppArray(i))
Sheet5.Range("E" & i + 14).Value = temp
End If
End If
Next i
End Sub
EDIT 2Change "Module_name" in first method, to your desire module, and set the corret sheet name (in this case Sheet 5) in execute method.
编辑 2 将第一种方法中的“Module_name”更改为您想要的模块，并在执行方法中设置正确的工作表名称（在本例中为工作表 5）。
