Call "Macro_Name" then pass back to macro calling?

[TechTank]

Hi,

I want to reduce the amount of editing I have to do on a lot of macros. The top 10 lines of each of my modules are unique to that module but the other 100+ lines beneath these 10 are the same in each module.

Is there a way to call a Macro2 that can contain these 100+ lines from Macro1, have it run the sub and then pass back to the macro that called it? Once it's passed back to Macro1 Macro1 would finish processing and call the next unique macro that it needed to run the 10 lines of unique code and then calls Macro2 again.

I was thinking like this:

Sub Macro1()

10 unique lines of code

Call Macro2

>>>> Waits for Macro2 <<<<<

Sub Macro2()

100+ lines of code

Call Macro1

>>>>> Hands back to Macro1 <<<<<

Sub Macro1()

Continues from after it called Macro2

Call Next_Unique_Macro

I'm really sorry if that's not clear but please feel free to ask questions.

Thank you for your time and again apologies for what could be such a poor explanation.

Mark.

EDIT: All modules are in the same project.

 

[Firefly2012]

Mark

What have you tried? What you have described is exactly the way calling macros works in VBA. Consider:

Sub Main1()

'some code

Call Derivative_Macro

'some other code that executes after code in Derivative_Macro

End Sub


Sub Derivative_Macro()

'100 lines of derivative code

End Sub

'Other module
Sub Main2()

'some code

Call Derivative_Macro

'control has returned - do some more code
'...

End Sub

So in the above, Derivative_Macro contains all the derivative code. It is called from both Main1 and Main2. Code execution in either Main after conclusion of Derivative_Macro

 

[TechTank]

Maybe I don't understand macros as well as I thought I did

I thought that:

Macro1 calls Macro2. Macro1 is now just a module like all the others.
Macro2 becomes "The Boss" and takes over and never goes back to Macro1 unless told to in it's code?

 

[Firefly2012]

Macro2 will execute and at its conclusion will pass back control (& execution) to Macro1.


Try it with a simple example with no/very little code and use F8 to step thru the code lines. You'll see as execution switches between subs.

 

[TechTank]

Oh my god...I AM an idiot!

Thank you so much Firefly2012 for pointing this out to me! I just did as you explained and it works! I've been updating 30 odd macro's every time I discovered a better way to do the 100+ lines of code or need to change something when I could've just used macro's properly!

This is RESOLVED!

 

[Firefly2012]

I've had a few of those epiphany moments in Excel over the years too Glad you have it sorted.