PDA

View Full Version : [SOLVED] Need help with Question, is it possible to Call modules to work or loop through?



estatefinds
08-13-2017, 02:45 PM
I have modules 7,8,9,10,11,12 that are Subs

Using the Code:


Sub MasterMacro()
Call Macro7
Call Macro8
Call Macro9
Call Macro10
Call Macro11
Call Macro12
End Sub


I Believe each macro would run, one at a time, one by one; The question is, is there a way to run by calling these macros to repeat to a specific amount of times before it stops.

Any help on this is Greatrly Appreciated!!!
Thank you in Advance,
Sincerely

Dennis

D_Marcel
08-13-2017, 03:05 PM
Hello!
I may be underestimating your request, but according to my understanding:

Sub Loop_Through_Subs()

Dim varIndex As Integer
Dim varTotal As Integer

varTotal = 5 'Number of times your macros will be called inside the loop
For varIndex = 1 To varTotal
'Call Macro1
'Call Macro2
'Call Macro3
'Call Macro4
Next varIndex


End Sub

Regards,

Douglas

estatefinds
08-13-2017, 03:35 PM
if I want the Macros 7,8,9,10,11,12 one at a time meaning;

7 runs then, 8 runs, then 9 runs, then 10 runs, then 11 runs, then 12 runs then It would go and do this over again about over 8000 times then stop.

offthelip
08-13-2017, 03:45 PM
for i = 7 to 8000
for j = 1 to i
call macro7
call macro8, etc
.... etc
next j
next i

estatefinds
08-13-2017, 03:51 PM
Awsome!!! Ill Give it a test Thank You!!!!!!:)
I appreciate it!!!

so it should look like this? so this is how it should look in regards to settin this up to work?


SubMasterMacro()
For i=7 to 8000
For j = 1 To i
Call macro7
Call macro8
Call macro9
Call macro10
Call macro11
Call macro12
Next j
Next i

mdmackillop
08-13-2017, 03:55 PM
More simply

For 1 = 1 to 8000
Call MasterMacro
Next i

offthelip
08-13-2017, 03:56 PM
I hope you realise that it will take a long time to run, since you will be running the macros 7+8+ 9 + all numbers up to 8000 times. I put it in because that is what you asked for.

So you might want to try setting the i loop limit to something sensible like 20 to start with.

estatefinds
08-13-2017, 04:15 PM
I understand it will take a long time. all macros 7,8,9,10,11,12 take a total of about 2 minutes.
Just want to be sure Im setting it up correctly?
SubMasterMacro()
For i=7 To 8000
For j = 1 To i
Call macro7
Call macro8
Call macro9
Call macro10
Call macro11
Call macro12
Next j
Next i

offthelip
08-13-2017, 04:16 PM
Your requirements are not totally clear: After you have run all the macros 7 times, are you thinking that you might do something ( like record a result) before you go on to run all the macros 8 times? And then similarly do you want to record are result after 8 times through the macros. If you aren't doing this , then MdMackillop's or d Marcel's solution will run all the macros 8000 times. If you want to record a result in between each set of runs through macros then you need a solution like mine. If you want to record results between each set of runs you would do it like this:


SubMasterMacro()
For i=7 To 8000
For j = 1 To i
Call macro7
Call macro8
Call macro9
Call macro10
Call macro11
Call macro12
Next j
' write code to record results between runs here
Next i

estatefinds
08-13-2017, 04:28 PM
ok sorry for the unclarity,

So I have the modules 7,8,9,10,11,12.

I want to run these one at a time;
run 7 then it ends, then 8 will start then end, then 9 will start and then end, then 10 will start then end, then 11 will start then end, then finally 12 will start then end.
Then I need it to repeat this process for about 8000 times or what ever amount I need it to.

so the end result:
each macro records a result that finally leads to a final result. with the fianl macro.

so each macro performs a task and results it. so it automatically as i do it now shows the result.

So instead of doing it manually running each macro as i do now,
I would open ALT F8 then run then when thats done it would go to next one until it finishes and so on.

Then I would do it all over again.

mdmackillop
08-13-2017, 04:39 PM
Here is a simple example. Run Test on a blank worksheet


Dim j

Sub Test()
For j = 1 To 3
Call MasterMacro
Next
End Sub

Sub MasterMacro()
Call Macro7
Call Macro8
Call Macro9
Call Macro10
Call Macro11
Call Macro12
End Sub

Sub Macro7()
For i = 1 To 100000
Cells(i, 1) = i * j
Next i
End Sub

Sub Macro9()
For i = 1 To 100000
Cells(i, 3) = i * j
Next i
End Sub

Sub Macro11()
For i = 1 To 100000
Cells(i, 5) = i * j
Next i
End Sub

Sub Macro8()
For i = 1 To 3
Cells(i, 2) = i * j
Next i
End Sub

Sub Macro10()
For i = 1 To 3
Cells(i, 4) = i * j
Next i
End Sub

Sub Macro12()
For i = 1 To 3
Cells(i, 6) = i * j
Next i
End Sub

estatefinds
08-13-2017, 04:45 PM
the codes that I run depends on the others result.

So I cant Have the first Code 7 run for example 8000 times then have code 8 run 8000 times etc.



They have to be run consecutively 7 then it ends, then 8 then it ends, then 9 then it ends , then 10 then it ends then 11 then it ends then 12 then it ends then repeat.


until it has done this for about 8000 times.

mdmackillop
08-13-2017, 05:10 PM
And your question is?

SamT
08-13-2017, 05:14 PM
Read MD's entire post. That code runs each of his macro#'s one after the other... 8000 times.
See how long it takes.

estatefinds
08-13-2017, 05:14 PM
so the code above in post 11 should do this?

Im testing now,
just ran it and it replaced my data in column C with "0"


Ok i ran it again and still it turns my data to zero's

I tried also only 10 rows and it still puts zeros where my data is.

it has never done this I copied and pasted the code in post #11 exactly

estatefinds
08-13-2017, 06:22 PM
im having trouble,
I even tried doing the code again an is not working correctly I even tried replacing the "Macro" in the Code to "Module" as the 7 8 9 10 11 12 are Modules.
When i entered my codes I inserted as modules.

the Dim j
Should be under the Sub. I thought that looked odd being its declaring variable should be between Subs.


I ran again and is keeps placing zeros in my data.

its not working like when i do it manually

I aslo rearranged the 7 8 9 10 11 12 to go in order in your code and still it isnt working. Im not sure why its not working.
Im stumped.

if i cant do it this way with call module is there a way to place each code in order under the other?


This might help in regards to macro name and Module number

Sub Test () is module7 (code)
Sub testA_v2() is Module8 (code)
Sub test2 () is Module9 (code)
Sub SplitAddress() is Module10(code)
Sub Test_v3() is Module11 (code)
Sub sbClearCells() is Module12 (code)

Bob Phillips
08-14-2017, 01:10 AM
It is not modules that you run, they are just containers for procedures, but the procedures themselves. MD showed you exactly what you wanted in post #11, just replace Macro7 with Test, Macro 8 with testA_v2, etc.. The problem with 0s would seem to be your code, there is nothing in MDs code that sets anything, and you never mentioned setting to 0 before.

offthelip
08-14-2017, 02:47 AM
I understand it will take a long time. all macros 7,8,9,10,11,12 take a total of about 2 minutes.

a quick calculation one iteration takes two minutes, so 8000 interations is going to take:

2 * 8000= 16000 minutes = 266.66 hours = 11.11 days

Before I tried running a test like that , I would seriously look at trying to speed up macros7 to 12. I would be very surprised if we couldn't help you with that , but we would need to see the macros

offthelip
08-14-2017, 03:22 AM
the Dim j
Should be under the Sub. I thought that looked odd being its declaring variable should be between Subs.

Your problem with the zeros is because that the "Dim j" MUST be right at the top of the module before any of the "subs". This defines it as a varialbe which is the same variable where ever it is used within that module. if you look at Md code you will see that each sub used "j" . if it is defined at the top of the module all of the "j"s are the same variable and thus the value gets carried across from sub to sub. If it is not defined at the top then each subroutine creates it's own J , and all but one of them (test) are blank, hence the zeros.

estatefinds
08-14-2017, 05:04 AM
The zeros were appearing when I copied code exactly with dim directly above subs that is when I got the zeros, so I thought it was because of location of Dim

SamT
08-14-2017, 05:25 AM
[mdmackillop: Post 11
Here is a simple example. Run Test on a blank worksheet


Dim j

Sub Test()
For j = 1 To 3
Call MasterMacro
Next
End Sub
'
'
'
'
'
'
'







Emph added

estatefinds
08-14-2017, 03:20 PM
Ok I ran it on the blank sheet and it places Zeros int F column int first 2nd and 3rd row .

Im new at this method of callung the macros and need help on how to solve this. i dont under stand why some modules in the code have this > For i = 1 To 100000 For i = 1 To 3

estatefinds
08-14-2017, 03:32 PM
ok soI placed the right under

the Sub Test () For j=1 to 100 and i got this below




27
27
27
27
27
27


54
54
54
54
54
54


81
81
81
81
81
81


108
108
108
108
108
108


135
135
135
135
135
135


162
162
162
162
162
162


189
189
189
189
189
189


216
216
216
216
216
216


243
243
243
243
243
243


270
270
270
270
270
270


297
297
297
297
297
297


324
324
324
324
324
324


351
351
351
351
351
351


378
378
378
378
378
378


405
405
405
405
405
405


432
432
432
432
432
432


459
459
459
459
459
459


486
486
486
486
486
486


513
513
513
513
513
494


540
540
540
540
540
520


567
567
567
567
567
546


594
594
594
594
594
572


is this
showing the how many times each macro runs? this is on the blank sheet.S
o if I get it to run on my sheet with data will this be placed over my existing data and not just run my macros ?

mdmackillop
08-14-2017, 04:16 PM
Implementation of the demonstration code

estatefinds
08-14-2017, 04:56 PM
ok I watched the demonstration,

does the count that you have in column A have to be there. when i tun the code in mibe i writes over my data

Also when I run mine does the code wait until the first module fisnish before goin on to the next module 8? and so on down the modules. meaning does the call module go as fast as each code/ 7 takes maybe a minute to complete then 8 takes half a minute and so on until the 7 8 9 10 11 12 take a little over 2 minutes to run.



Also you ve in you deveoper box the individual macros call macro 7 then macro 8 etc

my macros in the box show the actual name of the macros not module 7 or module 8 etc or even Macros 7 ro macros 8 etc. im just trying to figure out why it doesnt work



Sub Test () is module7 (code)
Sub testA_v2() is Module8 (code)
Sub test2 () is Module9 (code)
Sub SplitAddress() is Module10(code)
Sub Test_v3() is Module11 (code)
Sub sbClearCells() is Module12 (code)

ok so I had Change the name of the macros to macro 7 8 9 10 11 12
I ran it and it over wrote everything in columns A though F on my sheet with Zeros

so i ran it but the result of my codes didn't occur the only thing that happened was zeros over wrote over my data my codes use to run.




Sub run_all_six()
macro7
macro8
macro9
macro10
Macro11
Macro12
End Sub

could this work to run macros consecutively then add code that will repeat these for 8000 times?

mdmackillop
08-15-2017, 02:52 AM
I have modules 7,8,9,10,11,12 that are Subs

Using the Code:


Sub MasterMacro()
Call Macro7
Call Macro8
Call Macro9
Call Macro10
Call Macro11
Call Macro12
End Sub


I Believe each macro would run, one at a time, one by one; The question is, is there a way to run by calling these macros to repeat to a specific amount of times before it stops.

Any help on this is Greatrly Appreciated!!!
Thank you in Advance,
Sincerely

Dennis


Sub Test()
For j = 1 To 3
Call MasterMacro
Next
End Sub

I believe this question has been answered.

estatefinds
08-15-2017, 06:33 AM
I tried that but the only thing I get are zeros over writing my data and the macros are not carrying out the job my macros are meant to do.
Question is why are my macros not being carried out using the call mastermacro?
help on this is appreciated.

Paul_Hossler
08-15-2017, 11:21 AM
I tried that but the only thing I get are zeros over writing my data and the macros are not carrying out the job my macros are meant to do.
Question is why are my macros not being carried out using the call mastermacro?
help on this is appreciated.

Since no one knows what your macros are doing, I think you'll need to revise them to handle 8000 re-runs

As Mac says, there have been many examples of how to run your set of macros multiple times

If the ...




Sub Test()
For j = 1 To 8000
Call MasterMacro
Next
End Sub


... doesn't do what you want, you'll need to revise Macro7, Macro8, ...

SamT
08-15-2017, 01:13 PM
No one here has a clue what you did with the above suggestions.

Writing Code requires great precision in logic, word choice and grammar, and typing and spelling.

estatefinds
08-15-2017, 07:23 PM
Thank you for your help on this!
I appreciate everyone's help!!!
Thank you!
Sincerly,
Dennis

snb
08-16-2017, 01:12 AM
the answer:

Excel VBA Programming For Dummies by John Walkenbach | 9781119077398 | Paperback | Barnes & Noble (http://www.barnesandnoble.com/w/excel-vba-programming-for-dummies-john-walkenbach/1101874584)

estatefinds
08-16-2017, 04:05 PM
I have that book,Thank you!

I have figured out what exactly I needed,
Sub LoopMacro()
Dim x As integer
For x=1 to (whatever number)
Call (name of macro)
Next x
End sub

Thank you, everyone for your input!

mdmackillop
08-16-2017, 04:26 PM
Exactly as you were advised in Post #6