how to make a macro run other macros

[anewton_lists]

Hello,

I'm using Project 2003 and have stored four macros in my global.mpt. Ea
macro saves off some data into a .csv file. Ea macro runs successfully
when invoked by itself.

After creating the four macros, I then used the GUI (as I had done with
the earlier macros) to create another macro that would run the four
macros in succession. It appeared as if I was successful (nothing
squacked at me in the GUI) while recording this macro.

But, when I tried running the macro, nothing happened. Or, more to the
point, I clicked Run in the Macros dialog and the focus within the
macro list jumped to the top of the macros list and the app temporarily
became unresponsive. I had to click Cancel three or four times to
dismiss the dialog.

When I looked at the VBA, this is what I had:

Sub Pubs_All()
' Macro Pubs_All
' Macro Recorded Wed 2/15/06 by Me.
Macro
Macro
Macro
Macro
End Sub

Which certainly looked suspect. . .

Having done something similar in Excel that I know works, I went over
there and had a look at that macro's code:

Sub Pubs_All()
'
' Pubs_All Macro
' Macro recorded 2/15/2006 by Me
'

'
Application.Run "PERSONAL.XLS!Pubs_one"
Application.Run "PERSONAL.XLS!Pubs_two"
Application.Run "PERSONAL.XLS!Pubs_three"
Application.Run "PERSONAL.XLS!Pubs_four"
End Sub

So, I thought I'd be clever and copy that code into the Project VBA
editor, modifying it slightly like so:

Sub Pubs_Test()
'
' Pubs_Test Macro
' Macro copied from Excel 2/15/2006 by Me
'

'
Application.Run "GLOBAL.MPT!Pubs_one"
Application.Run "GLOBAL.MPT!Pubs_two"
Application.Run "GLOBAL.MPT!Pubs_three"
Application.Run "GLOBAL.MPT!Pubs_four"
End Sub

Clicking Run in the Macros dialog now closes the dialog (no weird
unresponsiveness), but no .csv files are getting produced.

Anyone have any ideas?

Thanks in advance.

 

[JackD]

You will probably have to go into the visual basic editor to find out what
the macros you created are called. You also want to make sure they are set
as public rather than private.

To do this hit the F11 key. This brings up the visual basic editor. IN the
upper left there is a box which is a treeview type list. You can expand the
Global.mpt until you get to the macros. Since you auto-recorded them, they
are probably listed as separate modules. At this point you can find the
names and write code which calls all 4 of them in succession. Something like
this:

Sub MasterMacro()
Macro1
Macro2
Macro3
Macro4
End Sub

There are some other serious considerations with recorded macros. You might
want to edit the code a bit here and there. Take a look at the macro
examples on my sites listed below. I have an example of working with macro
recorder generated code about halfway down this page:
http://zo-d.com/blog/archives/programming.html

 

[anewton_lists]

Thanks, Jack. After all that, all I needed the macro to look like was
this:

Sub Pubs_Test2()
'
' Pubs_Test2 Macro
' Macro copied from Excel 2/15/2006 by Me
'

'
Pubs_one
Pubs_two
Pubs_three
Pubs_four
End Sub

And it works like a charm!

Great! Thanks.

 

[Dean]

You are on the right track with your approach to have multiple macros. You
will find that have many small macros that perform very specific tasks will
be easier to debug and more reusable.

 

[anewton_lists]

Rats! I guess I need to my curb my earlier enthusiasm. ( Turns out
that my example:

Sub Pubs_Test2()
'
' Pubs_Test2 Macro
' Macro copied from Excel 2/15/2006 by Me
'

'
Pubs_one
Pubs_two
Pubs_three
Pubs_four
End Sub

Doesn't work after all. Instead, when I try to run Pubs_Test2, I get
this error dialog:

+---------------------------------------------------------------+
Compile error:
Expected variable or procedure, not module.
+----------------------------------------------------------------+

I have all the referenced macros stored in the same .mpp.

Anyone have an ideas?

 

[JackD]

Are they all stored in the same module?
I'd try that first. Make sure they are marked public too

-Jack

 

[anewton_lists]

Hi Jack,

I guess I need to beg clarification. First, can you explain what you
mean by "stored in the same module?" I recorded four macros:

Pubs_one
Pubs_two
Pubs_three
Pubs_four

in a .mpp file. After they were recorded, they initially showed up as
Module 1, Module2, Module 3, and Module 4 in the VBA editor. I renamed
them. I then recorded another macro, which basically recorded my
running each of the above four macros, one after the other. After
recording that macro, which showed up as Module 5 in the editor, I
renamed it as Pubs_Test2. Is a module synonymous with a macro?

Also, I am unclear on what you mean by making sure they are marked
public. I don't know how to check if they are public or private. Can
you explain?

Thanks for your time and help,
Adam

 

[John]

Hi Jack,

I guess I need to beg clarification. First, can you explain what you
mean by "stored in the same module?" I recorded four macros:

Pubs_one
Pubs_two
Pubs_three
Pubs_four

in a .mpp file. After they were recorded, they initially showed up as
Module 1, Module2, Module 3, and Module 4 in the VBA editor. I renamed
them. I then recorded another macro, which basically recorded my
running each of the above four macros, one after the other. After
recording that macro, which showed up as Module 5 in the editor, I
renamed it as Pubs_Test2. Is a module synonymous with a macro?

Also, I am unclear on what you mean by making sure they are marked
public. I don't know how to check if they are public or private. Can
you explain?

Thanks for your time and help,
Adam

Hi Adam,
Jack may be off-line so I'll jump in. It's been a while since I ran
multiple macros so I'll have to brush up a bit.

First, to answer your questions. A module is NOT synonymous with a
procedure (more commonly known as a macro). A module contains one or
more procedures. If multiple procedures are recorded, they will by
default be placed in individual modules. However, individual procedures
can always be created or copied into a single module. Generally but not
necessarily, when a group of procedures are run in sequence, they will
all be in a single module. However, it is not necessary to have all the
procedures in the same module, it just makes the call to them a little
simpler (more on that later).

A recorded procedure is by default "Public". That is, it is available to
all projects or modules. On the other hand a "Private" procedure must be
explicitly declared as such although Event procedures and procedures
associated with userforms are by default "Private". Private procedures
are only available to the module where they reside.

You can learn more about the above concepts by going to the VBA help
file and clicking on "Visual Basic Conceptual Topics".

In your specific case, if all 4 recorded procedures are in the same
module, the call for them is simple.

Sub Main()
Pubs_one
Pubs_two
[etc.]
End Sub

If the procedures are in separate modules, the calling code must
reference the module (it's kind of like a path when working with files
in different directories)

Sub Main()
Module1.Pubs_one
Module2.Pubs_two
[etc.]
End Sub

Something the user must remember when running a sequence of procedures
in different modules. If a module is re-named, any procedure that calls
it must also be edited to reflect the change. That's one reason it is
simpler to have sequential procedures all in the same module.

Hopefully this has given you a little better understanding of the
wonderful world of VBA.

John
Project MVP

 

[anewton_lists]

John, thanks for the information. I modified my macro to include
references to the modules. It now looks like:

Sub Pubs_Test2()
Pubs_one.Pubs_one
Pubs_two.Pubs_two
Pubs_three.Pubs_three
Pubs_four.Pubs_four
End Sub

And, better still, it now works!

Thanks for your time and help.

 

[John]

John, thanks for the information. I modified my macro to include
references to the modules. It now looks like:

Sub Pubs_Test2()
Pubs_one.Pubs_one
Pubs_two.Pubs_two
Pubs_three.Pubs_three
Pubs_four.Pubs_four
End Sub

And, better still, it now works!

Thanks for your time and help.

Adam,
Great, and you're welcome.
John