Calling a macro through another spreadsheet takes forever!

Hi,

I am facing the following problem. I have setup a macro in a file and, with slight changes, replicated this for 4 different files. So each workbook has the same macro pretty much.

Two of the workbooks refer to the same "line" and each one works on a different "direction" (south/north for example).

What I did, for matter of convenience is to create another workbook and place some nice buttons that when you click one of them it will open two of the workbooks and run the macro that is included inside them.

The problem is, this takes forever!! When I open each workbook individually and run their macros they take a reasonable amount of time, whenever I attempt to open them through another workbook and call the macros it takes forever!. I tried calling only one workbook and running 1 macro but it still takes much longer than opening the file manually and running the macro manually from within the file.

Any ideas why that happens? I can post the code if needed but it's nothing special, just opening the xls and running the macro.

Ok, this is the code that opens and calls the macro from another workbook. It first copies tha values of two cells from the ConsoleWorkbook to the opened workbook, runs the macro and copy/pastes-special-values-only a worksheet from the opened workbook to the ConsoleWorkbook.

Please Login or Register  to view this content.

Now the macro tha runs on the opened workbook (and does the calculations)
is pretty big. I'll put here first the 2 main calculation modules and if that's not enough information I'll put the main body (grab data from sql) as well.

create and delete worksheet

Please Login or Register  to view this content.

and this one does claculations (array formulas)

Please Login or Register  to view this content.

You should avoid selecting and never work on all cells just the used ranged

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

VBA Noob

Originally Posted by excelmarksway

A suggestion or two:

Maybe have a Master workbook that has the macros and no macros in the other workbooks.

I notice you ChDir at some stage. Do you ChDir to the original location?

I did not see at any stage where the workbooks are saved.

- - excelmarksway

Hi,

Thanks for the feedback. I do change the directory to open the workbooks with the macro that is to be run (that actually does the data grabbing etc). I don't save the workbook at all, I just use the master workbook to open a series of others, run the macros inside them and then copy/paste the result worksheets back to the master one. I don't save anything at the moment.

Still that shouldn't be delaying anything, should it?

Originally Posted by VBA Noob

You should avoid selecting and never work on all cells just the used ranged

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

VBA Noob

Hi,

I am not sure to which part you are referring exactly but I assume that you mean where I create a worksheet and then copy paste all the cells from another workbook-worksheet to the master newly created worksheet.

This shouldn't be causing the delay though as it happens at the end and the way it looks like when I run the macro is that there is a long pause at the beginning and then it starts working. In short, the copy/pasting of the cells doesn't see (and can't see why it should) cause the delay.

hi Kostas

I can't see from your code how this all ends up in the "Consol'" workbook (maybe it's in the called macro of "application.run..."?).
Anyway, I've had a go at modifying the below code to run a little smoother by removing selections & declaring variables etc but haven't tested it at all - so hopfully it works...
I have prefixed my comments with "###".

Please Login or Register  to view this content.

Even if this code doesn't work in its entirety it should give you plenty of ideas on how to optimise your working code - Goodluck!

hth
Rob