VBA module that runs other modules

Question

I'm programming in Microsoft VBA. At first I need to generate a QueryTable with the help of a macro (I've got the code for that) and after that with the help of macros I need to apply formulas that use the data in the QueryTable. The problem that I am facing is that the QueryTable appears only after the Sub, in which its code is, has finished working. That means that I cannot include the code that generates formulas in it, because there is no data for the formulas to be generated on.

The idea right now is to write a module that runs other modules:

Sub moduleController()
    Run "Module1"
    Run "Module2"
End Sub

This gives the error:

Run time error 1004 - cannot run the macro "macroname". The macro may not be available in this workbook or all macros may be disabled.

What could be the solution? Maybe there is another solution for my QueryTable loading problem?

Solution 1

As long as the macros in question are in the same workbook and you verify the names exist, you can call those macros from any other module by name, not by module.

So if in Module1 you had two macros Macro1 and Macro2 and in Module2 you had Macro3 and Macro 4, then in another macro you could call them all:

Sub MasterMacro()
    Call Macro1
    Call Macro2
    Call Macro3
    Call Macro4
End Sub

Solution 2

Is "Module1" part of the same workbook that contains "moduleController"?
If not, you could call public method of "Module1" using Application.Run someWorkbook.xlsm!methodOfModule.

Solution 3

I just learned something new thanks to Artiso. I gave each module a name in the properties box. These names were also what I declared in the module. When I tried to call my second module, I kept getting an error: Compile error: Expected variable or procedure, not module

After reading Artiso's comment above about not having the same names, I renamed my second module, called it from the first, and problem solved. Interesting stuff! Thanks for the info Artiso!

In case my experience is unclear:

Module Name: AllFSGroupsCY Public Sub AllFSGroupsCY()

Module Name: AllFSGroupsPY Public Sub AllFSGroupsPY()

From AllFSGroupsCY()

Public Sub FSGroupsCY()

    AllFSGroupsPY 'will error each time until the properties name is changed

End Sub

Comments

[mischab1]

Also, if Macro1 - Macro4 are not in the same module as MasterMacro, then Macro1 - Macro4 need to be declared as public, not private.

[ositra]

Yes, It is on the same Workbook. Nevertheless, thank you for the code line! I learned something new.

[ositra]

Thank you, Jerry! You solved it! It should be noted that another problem that I stumbled upon was that the name of the macro cannot be the same as the name of the module. They have to be different!

[Jake8281]

Hi I tried to call my macro names but it didn't work. I am trying to run 11 different macros to generate 11 different excel sheet reports in sequence.