Batch Macro

[charley]

this is my first post on the forum so hi to all..

i am just learning vba and and am a complete amateur, but i have created several mini macros (because i dont' know how to link various commands to gether in one macro) that i would like to chain together so that one click will fire them all off in succession. I have tried this by just creating one mother macro and inputting the names of the mini macros, but the 1st macro requires user unput ie: answer yes or no to deletion of columns. this causes the automation of the the following macros to screw up.
I have also tried a wait commend without success..(prob because i dont fully understand the syntax or method)

What I would like if poss - is for there to be a pause when the 1st macro asks for a reply and only continues upon replies OR for the answers to be answered automatically with "yes" twice, so that the remainder of the script will run smoothly

I have attached a notepad file showing the script of the mini macros in order. Could someone possibly advise me how as to how i can achieve what i am after please.


any help or tips would be great
many thanks

charley

[mrice]

Try inserting something like...

Please Login or Register  to view this content.

[Richard Buttrey]

this is my first post on the forum so hi to all..

i am just learning vba and and am a complete amateur, but i have created several mini macros (because i dont' know how to link various commands to gether in one macro) that i would like to chain together so that one click will fire them all off in succession. I have tried this by just creating one mother macro and inputting the names of the mini macros, but the 1st macro requires user unput ie: answer yes or no to deletion of columns. this causes the automation of the the following macros to screw up.
I have also tried a wait commend without success..(prob because i dont fully understand the syntax or method)

What I would like if poss - is for there to be a pause when the 1st macro asks for a reply and only continues upon replies OR for the answers to be answered automatically with "yes" twice, so that the remainder of the script will run smoothly

I have attached a notepad file showing the script of the mini macros in order. Could someone possibly advise me how as to how i can achieve what i am after please.


any help or tips would be great
many thanks

charley

Hi,

If as you say the main macro requires a Yes/No answer to proceed, that implies that you are throwing up a Message Box inviting the user to choose a Yes or No button. In which case whichever button the user presses returns a value to the macro. If Yes is pressed the answer is 6, if No the result is 7.

Hence you need to test for a Yes/No answer with and IF..THEN test.

The way I usually do this is to set a variable for the answer, e.g. Dim iAnswer as Integer. Then for the Message Box line use:

iAnswer = MsgBox ("Your prompt",vbYesNo)

now use the iAnswer

Please Login or Register  to view this content.

Hope this gives some ideas.

[charley]

thanks for your replies guys...

i have little clue as to how/where this would actually sit in the code i have in Macro 1 in the tex doc i uploaded.....what preceeds it and what comes after either..

what is happening is that i am asking the macro to copy 2 columns and then in turn execute a "text data to columns"..each of these is throwing up a message box asking if the use would like to proceed and overwrite destination cells each time

is there no way of overriding the message box at all by answering yes automatically without user intervention?

i fear i am going to get a little lost here..

thanks for any help

charley

[Richard Buttrey]

thanks for your replies guys...

i have little clue as to how/where this would actually sit in the code i have in Macro 1 in the tex doc i uploaded.....what preceeds it and what comes after either..

what is happening is that i am asking the macro to copy 2 columns and then in turn execute a "text data to columns"..each of these is throwing up a message box asking if the use would like to proceed and overwrite destination cells each time

is there no way of overriding the message box at all by answering yes automatically without user intervention?

i fear i am going to get a little lost here..

thanks for any help

charley

Yes. Just find the bit of code that refers to a Message box 'MsgBox' and comment it out or delete it. The macro will then proceed without any user intervention. If you're still struggling, zip it up and attach it here and I'll take a look.

Rgds

[charley]

Hi Richard ..

thanks for your post..

I've looked and i dont actually see any code relating to "msgbox"..very odd?!

the macro does the job ok - if you run it you can just see that its a simple copy and data to columns execution, but requires user interraction which screws everything up thereafter

i've attached a very basic copy of the sheet i'm having problems with. if poss i would like run this 1st macro and then on completion run a batch of other macros in turn after this has finished.

many thanks for your help

regards

charley

[Richard Buttrey]

Hi Richard ..

thanks for your post..

I've looked and i dont actually see any code relating to "msgbox"..very odd?!

the macro does the job ok - if you run it you can just see that its a simple copy and data to columns execution, but requires user interraction which screws everything up thereafter

i've attached a very basic copy of the sheet i'm having problems with. if poss i would like run this 1st macro and then on completion run a batch of other macros in turn after this has finished.

many thanks for your help

Hi,

To get over your warning box, which is actually not a Message Box, but Excel simply checking if you want to overwrite some cells, you can put the single line of code

Application.DisplayAlerts = False

as the first line of code in the macro.

It also looks like the macro could be tidied up and simplified a lot. It's probably been recorded via the macro recorder, which is a good way to start, but VBA doesn't really need all the .Select stuff. In fact this can slow things down in larger applications.

Can you tell me what you're trying to achieve with it, so that I can understand better how to change the macro. For instance how many copies of columns F&L are you wanting to create, and is it corrrect that you want to split say '2-0' in F4, to
'2' in col L and '0' in col M. And ditto for column I when copied to column O&P

Rgds

[charley]

Hi Richard..

thanks for your reply..thats brilliant i have tried it and that works.

yes. i'm afraid i am no vba guru and did record the actions. i understand what you say about there being some bloat on the macro code, but with the little code i kinow it seems the best way to get results..

what i am trying to do is have column F split into S & T respectively and then column I split into column X & Y respectively.. the columns L,M O & P are simply beig used to act as the area where the data text to column is executed..

i have ammended as per your advice and the warning display no longer appears..

thanks very much for your help should you wish to proceed with further ammendments to the code or not..

if i wish to run another macro immeditaley after this macro would i just type in the run and the name or is there a more professional method of calling a batch of macros within one overriding macro

thanks again

charley

[Richard Buttrey]

Hi Richard..

thanks for your reply..thats brilliant i have tried it and that works.

yes. i'm afraid i am no vba guru and did record the actions. i understand what you say about there being some bloat on the macro code, but with the little code i kinow it seems the best way to get results..

what i am trying to do is have column F split into S & T respectively and then column I split into column X & Y respectively.. the columns L,M O & P are simply beig used to act as the area where the data text to column is executed..

i have ammended as per your advice and the warning display no longer appears..

thanks very much for your help should you wish to proceed with further ammendments to the code or not..

if i wish to run another macro immeditaley after this macro would i just type in the run and the name or is there a more professional method of calling a batch of macros within one overriding macro

thanks again

charley

Hi,

I'm not a professional programmer so I don't really know what they teach you about this aspect. However I know what works for me, and whenever possible, I always try and create applications which are driven by a series of 'Call MySuperbMacro1()'.... etc statements in what I call a program control macro. i.e. it usually contains nothing more than directions to other macros.

I do this for two reasons. First it avoids 'spaghetti' code where one VBA procedure calls another, which in turn calls another. This is harder to follow when you get more than a few procedures and you're trying to debug the thing. More importantly, and usefully, it allows you to check VBA procedures one by one as you build them, and you know they're not going to divert elsewhere.

Incidentally I didn't know about the Run command until fairly recently. You can start a macro by just listing its name, or as you've found with Run. My preference however is the Call MySuperbMacro() syntax. When you get more experience with macros, you'll find it useful to be able to pass variables/parameter from one procedure to another. You can only do this with the Call syntax, where you include the parameters inside the () bit.

Good luck - if I get chance I may have a look at your code and suggest an alternative.

Rgds