running multiple macro's at once with a twist

[Gti182]

hi all,

So my challenge is to save time running 20 or so similar macro's which in effect do the same thing (export project data from a master file into a pre saved template).

I've added the code below for one of the macro's. Only the sections highlighted in red differ between macro's.

On running any one macro the user would enter the REF number of the project he'd like to export. This needs to be done 20+ times for each project and this is where i'm hoping to save the time.

My question now is can i create a new macro which will call all 20+ macro's and only ask the user once to input the REF number?

Please Login or Register  to view this content.

[TMS]

Wherever you refer to "Fire Safety", change that to a variable ... and you might as well make the subroutine name generic. Then you can make a list, either on a worksheet, or in an array in code and loop through it passing the area in the subroutine header.

Regards, TMS

[Kaper]

Call this procedure in a loop, passing argument into it - like:

Please Login or Register  to view this content.

Check for syntax error - i wrote it from scratch and run Test.

[Gti182]

Thanks for the replies so far.

Apologies one thing i just picked up. Some of the macro's may have small variances in code like first row of the template or the array reference columns may differ.

In my head i can picture something like this working, is there a way to do something like below?

Please Login or Register  to view this content.

[Kaper]

As for what you call "automatically click OK try at the beginning of your ExportAll

Please Login or Register  to view this content.

And back to first question - yes, you can call sequence of procedures one by one.

[Gti182]

great, let me give that a try

[Norie]

Why not have the user input the REF number, and anything else that's required, in the ExportAll sub?

Then you can pass the REF number etc to the subs you are calling.

For example.

Please Login or Register  to view this content.

PS You might want to declare the paths, eg FPath, TPath etc, as constants at the top of the module.

[Gti182]

Norie I like that option but ideally i'd like the user to still have the option to run individually which i think your way won't allow for, am i correct?

Kaper i think i understand your thought process but on review I think there are too many small inconsistencies in each template and each macro's code for it to work.

Ideally I'd like to keep the existing code for the individual macro's as is but now wondering it this is possible.

[Norie]

If you want to keep the individual subs as they are just call them as you did in post #4.

To avoid the user having to input the REF for each sub declare it at the top of the module,

Please Login or Register  to view this content.

then add something like this to the each of the subs you are calling.

Please Login or Register  to view this content.

[Gti182]

Norie, with post #9 if i'm following you correctly, I leave original macro's as is and create a new macro like so?:

Please Login or Register  to view this content.

[Norie]

You could do it that way I suppose but, to me anyway, it would make more sense to have this in each individual sub rather than repeated multiple times in the ExportAll sub.

Please Login or Register  to view this content.

This wouldn't affect the subs being run individually, if they were the user would be prompted for the REF.

[TMS]

Untested, but maybe something like:

Please Login or Register  to view this content.

Regards, TMS

[TMS]

Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[Gti182]

TMS, had a little play around with your code but struggling a bit as trying to get my head around what its trying to do, i might revisit it tomorrow morning when my mind is fresh

Norie, I managed to get your way to work all at once but it gives me the error "Argument not optional" when run individually with below code. I copied that bit of code in (red) but it doesn't give the user the option to exit the sub on cancel, did i put it in the right place?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[TMS]

All it is doing is a) requesting the unique reference and then b) looping through an array of elements and passing each element to a generic routine.

The first parameter passed to the routine is the element, for example, Fire Safety, and the second is the unique reference.

The element/item variable is used in the three locations. In other words, you only need one routine and you pass to it whatever parameters you need.


You can't manually run a subroutine that requires parameters.


Regards, TMS

[Norie]

What about the approach I suggested in #9 with unique declared as Public at the top of the module?

Please Login or Register  to view this content.

[Gti182]

Norie thanks! That kind of works.

Export all macro works a treat, no problems. I did add the red bit of code for if the user clicks cancel.

Please Login or Register  to view this content.

The problem i'm having is with the individual macro's. After running the export all and then running say fire safety it doesn't give the option to choose a REF number but it does export the REF number I chose when i ran the export all macro so i'm guessing excel saves the REF number last used in memory?

I did notice when i saved the file and reopened it and then ran the fire safety macro first it did ask for the REF number but when i ran it the second time it didn't.

Is there something i could change to prevent this or a way to clear the REF from memory? My vba skills are pretty amateur so forgive me if i'm missing the obvious

[Gti182]

Been playing around and this fixed the individual macro issue so you can choose a REF number everytime it is is run but it then messes up the export all macro

Please Login or Register  to view this content.

[romperstomper]

You can't manually run a subroutine that requires parameters.

You can - you just need either a control to run it, or to type the name into the macros dialog.

[TMS]

You can - you just need either a control to run it, or to type the name into the macros dialog.

I would normally set up a small test subroutine that called the subroutine and passed the parameter(s) but that wasn't the point I was making. You can't just click in the subroutine and press F5 to run it if the subroutine expects parameters. Not sure how you use the macro dialogue box to call the routine and pass parameters. Please can you clarify that method.

I realise that if you know a macro exists, even though it does not show up in the list of macros, you can type the macro name in and run it. But, if the reason it is not listed is because it requires parameter input, then I'm not sure what happens.

Regards, TMS

[romperstomper]

Given a routine like this:

Please Login or Register  to view this content.

to call it without parameter, as would be the case in this question, simply type:
foomanchoo
in the macros dialog and press run. If you wanted to pass a string parameter (I can't really think why you would do this though), you would enter:
'fooManChoo "some text"'
in the dialog and then press Run. Simples.

[TMS]

Ah, yes, simples ... when you know . In my example, the parameters were not stated as optional. But, good to know ... never tried to do that. As I said, I would usually set up a test script that called the subroutine and passed it parameters to test various options. I wouldn't manually type a subroutine name and a list of parameters. Good to have different ways of doing things though

Cheers, TMS

[Gti182]

ahhh I wish i had the knowledge to understand what you two are talking about, one day

[Gti182]

i managed to get everything to work by taking a bit of a long way around.

Made duplicates of all the individual macro's and renamed them with "ALL" at the end of them and referring to those in the export all macro.

like so

Please Login or Register  to view this content.