Get a list of the macros of a module in excel, and

问题:

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.

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

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

回答1:

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:

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:

Step 1:

Step 2:

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

Then you need to add this reference to your 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.

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

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.