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?
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
[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, ...
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.