获取excel中模块的宏列表，然后调用所有这些宏 [英] Get a list of the macros of a module in excel, and then call all those macros

问题描述

请帮助以下内容：

1）设置Module3所有宏列表的代码，并将此列表放在Sheet5中，启动在单元格E14中。

1) A code that sets a list of all macros of "Module3", and place this list in "Sheet5", starting in cell "E14" below.

2）然后，代码应该运行所有列出的宏

2) Then, the code should run all the listed macros

I尝试使用引用 VBComponent 的代码，但是我收到错误。

I tried with a code that referred VBComponent, but I got an error.

推荐答案

p>根据我的谷歌搜索，我发现答案我评论过你，但他们忘记了重要的事情，那就是检查和选项，让你运行宏。

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.

首先在excel中列出所有宏并返回并以白色空格分隔的字符串的功能：

First the Function to list all macros in excel and return and string separated by white space:

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

下一步，可能是第一个，您需要更改办公室（Excel）信任中心的一些配置，检查以下图像：

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:

步骤1：

步骤2：

步骤3（最终）检查选项依靠访问数据模型项目vba ：

Step 3 (Final) Check the option "rely on access to the data model project vba":

然后，您需要将此引用添加到您的Excel中：

Then you need to add this reference to your Excel:

Don'如果您有其他版本的Microsoft Visual Basic应用程序可扩展性，在这种情况下是5.3，担心。检查然后接受。不要忘记，您需要找到该参考，列表顶部没有。

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.

最后你可以调用ListAllMacroNames（）函数With This other macro named （），看看我被验证不会调用相同的宏（执行，ListAllMacroNames）或可能会产生无限循环。

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.

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

编辑2 将第一种方法中的Module_name更改为需求模块，并设置更正表单名称（在此案例表5）执行方法。

EDIT 2 Change "Module_name" in first method, to your desire module, and set the corret sheet name (in this case Sheet 5) in execute method.

这篇关于获取excel中模块的宏列表，然后调用所有这些宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！