Worsened macro performance if called from another macro?

Hi,

I have a quite heavy macro that takes 5-10 minutes to run since it includes a lot of simulations. I now face a situation where I would like to run this macro in ~50 different files, all different versions of the one containing the heavy macro. The "outer macro" would be quite simple, fetching the names of the files to be opened, simulated, saved and closed using a loop:

Please Login or Register  to view this content.

So to my question: Will this way of running the "heavy macro" worsen its performance, compared to doing it manually? I get the impression that the heavy macro runs much slower if called from an "outer macro", but it would be very helpful with some actual facts on how this works.

Many thanks in advance.

I apologise for the rule-breaking, and I'll make sure to include a link from my post in the MrExcel.com forum.

Originally Posted by romperstomper

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this, then please edit your first post to include links to any and all cross-posts in other forums.

Thanks, I'll hope for this hypothesis to hold :-).

Originally Posted by Domski

Hi,

Don't think it should run any slower.

Dom

Ok, so your code doesn't open the workbook with the heavy macro as such, but rather runs the macro and saves the results -- correct?

If so, it definitely seems like a more efficient way to run the macro 40 times than having to open each file with the Workbooks.Open command -- opening one of these workbooks actually takes another 2 minutes each time due to their inherent complexity.

Originally Posted by snb

If the workbooks won't be visible the macro will run faster (no screenupdating for example):

Please Login or Register  to view this content.

Thanks!

Unfortunately, the With GetObject solution does not seem to allow the heavy macro to run properly -- there is an "out of range" error when it is supposed to select a specific sheet in the workbook containing the heavy macro using Sheets("Sheet1").Select

It seems I'll have to stick with the original solution after all.

I would now also agree with you from practical experience. I discovered the reason for the slow performance was that I had forgot to set recalculation to manual in the general Excel settings. This caused the heavy simulations to run very slowly, thus prompting my initial question.

Thanks a lot for pointing me in the right direction!

Originally Posted by romperstomper

To be honest, I can't see why there would be a difference in the way that the workbook is opened. If the sheet is in the workbook with the code, there should not be an issue.