0

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.

RubberDuck
  • 11,933
  • 4
  • 50
  • 95
Flavinho
  • 19
  • 2
  • 6
  • you try with this code ? http://stackoverflow.com/questions/3922772/return-a-list-of-all-macros WD2000: Sample Macro to Return Macro and Procedure Names ? – Juan Ruiz de Castilla Jan 25 '15 at 02:01
  • Thanks @JuanRuizdeCastilla, I tried it but appears an error. I 've already set the excel settings, inserted a new module, and added the function and the sub in there. However, it appears: "Compile error: User-defined type not defined", and "Dim pj As VBProject" is highlighted in blue in the VBA window. – Flavinho Jan 25 '15 at 03:37
  • did you see my answer ?, I will edit because you need to add a reference, check my answer in a while please. – Juan Ruiz de Castilla Jan 25 '15 at 04:03
  • Ok, check the new other two images. – Juan Ruiz de Castilla Jan 25 '15 at 04:17
  • Thanks @JuanRuizdeCastilla. That specific problem is solved, however there are two issues now: 1) This code is calling all macros, I need to call macros of Module3 only, 2) I need to get the list of the macros of Module3 in "Sheet5", starting in cell "E14" below. Thanks! – Flavinho Jan 27 '15 at 16:50
  • Ok, i can help you, but it will better if you adapt my code, but well I will help you again this time. First of all, the modules are independant to Sheet's and to cells... so i will list just macros in module3. – Juan Ruiz de Castilla Jan 27 '15 at 17:26
  • Thanks! I mean that I need the physical list of the macros of "Module3". In other words, I need to see the list of the macros of "Module 3", placed in cells E14, E15, E16.... and so on below of the "Sheet5". For example, if I have 3 macros in module3 (macroA, macroB, and macroC), then cell E14 of Sheet5 should contain the word "macroA", E15 should be "macroB", and E16 should be "macroC". – Flavinho Jan 27 '15 at 18:24
  • So you need run the macros and then put the names in the sheets that you told me rigth? – Juan Ruiz de Castilla Jan 27 '15 at 19:52
  • Yes, that's correct. – Flavinho Jan 27 '15 at 20:04
  • Ok done, I hope you can make this by your own next time. – Juan Ruiz de Castilla Jan 27 '15 at 23:28
  • please tell if its all ok. – Juan Ruiz de Castilla Jan 28 '15 at 01:18

1 Answers1

5

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 1

Step 2:

Step 2

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

Final Step

Then you need to add this reference to your Excel:

step 2.5

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.

step 2.6

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.

Juan Ruiz de Castilla
  • 964
  • 14
  • 25