VBA run module 1, wait till finished then run module 2, wait till finished then run module 3, wait till finished then run module 4.?

Excel 2019 Windows

I have 4 page workbook and each page has a VBA module that performs various tasks on the data from the previous worksheet so they need to be run one after another but ONLY when the previous one has finished and at the moment they are being ran manually.

I would like to have a single module / macro, for arguements sake called "RunAll", that when clicked/envoked performs the same task.

Module1. Imports and cleans up data from a delimited txt file into sheet 1 - the data consistes of anywhere between 2000 - 110,000 lines.

Module2. Looks at Sheet 1 and selects certain data and formats it into Sheet 2.

Module3. Looks at Sheet 2 and selects certain data and formats it into Sheet 3.

Module4. Looks at Sheet 3 and selects certain data and formats it into Sheet 4.

Each module is coded in such a way that in knows which sheet to look at and where to insert its data.

This is a very simplified version so that you are not presented with a long post to read but when ran manually one after another they all work perfectly.

But if I simply try a maco that states:

Call module1
Call module2
Call module3
Call module4

The data on some sheets gets muddled up so and I suspect that as some modules take longer to run than others (module1 & module2), the other modules are being ran before all of the data is correctly imported and formatted before the precedeing ones have completed thier tasks.

So what I am trying to do is...

Call Module1 (and only when it has finished then)
Call Module2 (and only when it has finished then)
Call Module3 (and only when it has finished then)
Call Module4

I have performed copious searches online for possible answers before posting in here but nothing seems to match my needs and a good chunk of what I have found goes straight over my head?

2

1 Answer

VBA is single threaded. So the different calls do not execute simultaneously.

@Mathieu hits the nail on the head. If you call "module 1" macro manually with sheet1 selected but what happens when you execute manually with say sheet2 or 3 selected. You main need to hardcode the references to sheet#.

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy