Calling on a macro within a macro!

M

mellowe

Hey All!!

Quick Question - How do you call up a macro within a current macro? I
have one big macro and I need it to jump to several other macros when
it runs! Does it matter where the other macros are stored or is it
better to have them all in one module?

Help appreciated Thnx!
 
N

Norman Jones

Hi Mellowe,

'=============>>
Public Sub Main()
'Your code
Call One
'your code
End Sub
'<<=============

'=============>>
Public Sub One()
MsgBox "Hi from One"
End Sub
'<<=============
Does it matter where the other macros are stored or is it
better to have them all in one module?
Click to expand...

Unless there are a larger number of macros, they can be stored in a single
module. However, for organisational reasons, it is usually better to store
macros by type in different modules.
 
M

mellowe

Thanks for the prompt reply Norman!!
Sorry but I dont understand where does this piece of code goes ?
I basically want start a macro that calls up in turn 5 other macros one
after the other - I have stored these other five macros in 5 seperate
modules under one Excel Sheet.
Just not sure how to pull it all together! thnx
 
C

CLR

I use the following command line to call "outside" macros from within my
main macro.....

Application.Run ("YourFirstOutsideMacroName")
Application.Run ("YourSecondOutsideMacroName")
Application.Run ("YourThirdOutsideMacroName")
Application.Run ("YourFourthOutsideMacroName")
Application.Run ("YourFifthOutsideMacroName")

These lines can be slpit up and placed at various places within your Main
Macro at your desire.

Vaya con Dios,
Chuck, CABGx3
 
M

mellowe

Sorry just a quick question on this - does it matter where the other 5
macros are stored? Is it ok for them to be in different modules under
the same Excel sheet? thnx?
 
C

CLR

It should not matter which module the called macros are stored in......I
usually place them for my convienience of remembering where they are for
future editing.

Vaya con Dios,
Chuck, CABGx3
 
D

Dave Peterson

If the macros are in the same workbook, then you don't need application.run.

You can just use:

call Macro1
call macro2
call macro3

And it sounds like these types of macros should not be in any worksheet
module--or behind the ThisWorkbook module.

They should be in just plain old General modules. The kind you get when you do
Insert|Module.

===
Application.run is very useful if you don't know the name of the macro that you
need to call (it'll be determined by the code that's running). Or if the macro
resides in another workbook.
 
M

mellowe

Thanks everyone for your help here!!...

This is the situation: I have one worksheet that has a button to run my
main macro - this macro opens up several other workbooks to initially
complie a dataset.

Once the dataset is completed I then need my main macro to run the
additional 5 macros in order - they use the completed dataset (they
also open up and change other workbooks' data) . All of these macros
are in general plain modules, as you said Dave from: Insert|Module, and
they are all behind one worksheet.

So should I use Application. Run and name the macros or is the Call
'Macro' option better?. Thnx again!
 
S

SpookiePower

Why do you use "Call" before the name of the macro ?

I just write the name af the macro, and then it jumps to that macro.
 
D

Dave Peterson

First, behind a worksheet means (to me) that they are behind one of the Sheet
modules--not in a general module. If you really put them in a General module
(Insert|module), then it's just a wording issue.

It sure seems to me that there would be less things for excel to do if I Call a
routine--rather than using application.run.
 
E

Ed

A few thoughts:

If I'm writing a routine that will use the same variables through several
different Subs, I will usually put all the Subs in the same module so I can
declare all the variables at the top. That way I don't accidentally re-Dim
a variable name in another Sub and destroy it for the rest of the Subs.

Remember that when you call a Sub from within another one, you have NOT left
the first one! When the second Sub is finished, you will drop back into the
first Sub just below the Call line. I got myself very tangled more than a
few times by jumping from one to another, forgetting that at some point I
had to return and finish everything out.

If you're opening and closing a lot of files, you may want to monitor your
objects in the Locals window, especially just before you think you're done,
to make sure you haven't left some "ghost" of a file hanging in memory.
This has got me a couple of times, too! If you want, put "Stop" on a single
line just before your final End Sub - it will break the code and give you a
chance to open the Locals window to check things.

HTH
Ed
 
M

mellowe

Thanks again everyone!!!
Used the Call functionality and works perfect - using Application.run
actually slowed down the macros!
 
Top