how do you split a large procedure into two or more smaller procedures? I
put an End Sub statement, but then I need to run two seperate macros...is
there a better way so I only have to run 1?

There isn't really a way around it. There is a limit to the size of a
procedure. At the end of the first half you can call the 2nd half (new
procedure) or you can call both from a new procedure.
--
JNW


"DB74" wrote:

how do you split a large procedure into two or more smaller procedures? I
put an End Sub statement, but then I need to run two seperate macros...is
there a better way so I only have to run 1?

Well, if you split a procedure up, you're going to have to run the new ones
if you want the same result! You can do it in two ways:

Let's say that MyOriginalProc is the original procedure

Sub MyOriginalProc
statementblock1
statementblock2
statementblock3
End Sub

You can split it up like this:

Sub MyNewProc
statementblock1
MyNewProcA
End Sub

Sub MyNewProcA
statementblock2
MyNewProcB
End Sub

Sub MyNewProcB
statementblock3
End Sub

Or, you can do it like this:

Sub MyNewProc
MyNewProcA
MyNewProcB
MyNewProcC
End Sub

Sub MyNewProcA
statementBlock1
End Sub

Sub MyNewProcB
statementBlock2
End Sub

Sub MyNewProcC
statementBlock3
End Sub


"DB74" wrote:

how do you split a large procedure into two or more smaller procedures? I
put an End Sub statement, but then I need to run two seperate macros...is
there a better way so I only have to run 1?

I think I understand your question, now. You can use either approach I showed
you above. In either case, you just call MyNewProc, so you're just making 1
call. MyNewProc then either calls the other procs, or starts the call chain
so that the next one calls the next one, and that calls the next one, etc.

"ppsa" wrote:

Well, if you split a procedure up, you're going to have to run the new ones
if you want the same result! You can do it in two ways:

Let's say that MyOriginalProc is the original procedure

Sub MyOriginalProc
statementblock1
statementblock2
statementblock3
End Sub

You can split it up like this:

Sub MyNewProc
statementblock1
MyNewProcA
End Sub

Sub MyNewProcA
statementblock2
MyNewProcB
End Sub

Sub MyNewProcB
statementblock3
End Sub

Or, you can do it like this:

Sub MyNewProc
MyNewProcA
MyNewProcB
MyNewProcC
End Sub

Sub MyNewProcA
statementBlock1
End Sub

Sub MyNewProcB
statementBlock2
End Sub

Sub MyNewProcC
statementBlock3
End Sub


"DB74" wrote:

how do you split a large procedure into two or more smaller procedures? I
put an End Sub statement, but then I need to run two seperate macros...is
there a better way so I only have to run 1?

I guess I am trying to run the macro and walk away from the computer...right
now with all the calculations and the speed of my computer it takes about 15
min to run the entire thing. Is there a way to automate it so the macro does
not stop at each End Sub?

"ppsa" wrote:

Well, if you split a procedure up, you're going to have to run the new ones
if you want the same result! You can do it in two ways:

Let's say that MyOriginalProc is the original procedure

Sub MyOriginalProc
statementblock1
statementblock2
statementblock3
End Sub

You can split it up like this:

Sub MyNewProc
statementblock1
MyNewProcA
End Sub

Sub MyNewProcA
statementblock2
MyNewProcB
End Sub

Sub MyNewProcB
statementblock3
End Sub

Or, you can do it like this:

Sub MyNewProc
MyNewProcA
MyNewProcB
MyNewProcC
End Sub

Sub MyNewProcA
statementBlock1
End Sub

Sub MyNewProcB
statementBlock2
End Sub

Sub MyNewProcC
statementBlock3
End Sub


"DB74" wrote:

how do you split a large procedure into two or more smaller procedures? I
put an End Sub statement, but then I need to run two seperate macros...is
there a better way so I only have to run 1?

thanks, what is the proper syntax to use to call the next procedure?...I will
name it Sub B()


"ppsa" wrote:

I think I understand your question, now. You can use either approach I showed
you above. In either case, you just call MyNewProc, so you're just making 1
call. MyNewProc then either calls the other procs, or starts the call chain
so that the next one calls the next one, and that calls the next one, etc.

"ppsa" wrote:

Well, if you split a procedure up, you're going to have to run the new ones
if you want the same result! You can do it in two ways:

Let's say that MyOriginalProc is the original procedure

Sub MyOriginalProc
statementblock1
statementblock2
statementblock3
End Sub

You can split it up like this:

Sub MyNewProc
statementblock1
MyNewProcA
End Sub

Sub MyNewProcA
statementblock2
MyNewProcB
End Sub

Sub MyNewProcB
statementblock3
End Sub

Or, you can do it like this:

Sub MyNewProc
MyNewProcA
MyNewProcB
MyNewProcC
End Sub

Sub MyNewProcA
statementBlock1
End Sub

Sub MyNewProcB
statementBlock2
End Sub

Sub MyNewProcC
statementBlock3
End Sub


"DB74" wrote:

how do you split a large procedure into two or more smaller procedures? I
put an End Sub statement, but then I need to run two seperate macros...is
there a better way so I only have to run 1?

See my posts in "Branching Macro" just below

"DB74" wrote:

thanks, what is the proper syntax to use to call the next procedure?...I will
name it Sub B()


"ppsa" wrote:

I think I understand your question, now. You can use either approach I showed
you above. In either case, you just call MyNewProc, so you're just making 1
call. MyNewProc then either calls the other procs, or starts the call chain
so that the next one calls the next one, and that calls the next one, etc.

"ppsa" wrote:

Well, if you split a procedure up, you're going to have to run the new ones
if you want the same result! You can do it in two ways:

Let's say that MyOriginalProc is the original procedure

Sub MyOriginalProc
statementblock1
statementblock2
statementblock3
End Sub

You can split it up like this:

Sub MyNewProc
statementblock1
MyNewProcA
End Sub

Sub MyNewProcA
statementblock2
MyNewProcB
End Sub

Sub MyNewProcB
statementblock3
End Sub

Or, you can do it like this:

Sub MyNewProc
MyNewProcA
MyNewProcB
MyNewProcC
End Sub

Sub MyNewProcA
statementBlock1
End Sub

Sub MyNewProcB
statementBlock2
End Sub

Sub MyNewProcC
statementBlock3
End Sub


"DB74" wrote:

how do you split a large procedure into two or more smaller procedures? I
put an End Sub statement, but then I need to run two seperate macros...is
there a better way so I only have to run 1?

Let me make it easier for you: The way to do it is simply to use the name of
the macro. If the macro has arguments, jsut put in a space and list the
arguments. Like this:

Without argumtents:

Statements...
More Statements...
MyMacro <-- this is the call to your macro
More Statements...

With arguments:

Statements...
More statements...
MyMacro "Hello", 23 <--Call to your macro with two arguments (one is a
string, and the other is a numeric)
More statements...




"DB74" wrote:

thanks, what is the proper syntax to use to call the next procedure?...I will
name it Sub B()


"ppsa" wrote:

I think I understand your question, now. You can use either approach I showed
you above. In either case, you just call MyNewProc, so you're just making 1
call. MyNewProc then either calls the other procs, or starts the call chain
so that the next one calls the next one, and that calls the next one, etc.

"ppsa" wrote:

Well, if you split a procedure up, you're going to have to run the new ones
if you want the same result! You can do it in two ways:

Let's say that MyOriginalProc is the original procedure

Sub MyOriginalProc
statementblock1
statementblock2
statementblock3
End Sub

You can split it up like this:

Sub MyNewProc
statementblock1
MyNewProcA
End Sub

Sub MyNewProcA
statementblock2
MyNewProcB
End Sub

Sub MyNewProcB
statementblock3
End Sub

Or, you can do it like this:

Sub MyNewProc
MyNewProcA
MyNewProcB
MyNewProcC
End Sub

Sub MyNewProcA
statementBlock1
End Sub

Sub MyNewProcB
statementBlock2
End Sub

Sub MyNewProcC
statementBlock3
End Sub


"DB74" wrote:

how do you split a large procedure into two or more smaller procedures? I
put an End Sub statement, but then I need to run two seperate macros...is
there a better way so I only have to run 1?