Procedure loop limitation?
 

Hi,

I have created a macro (vba procedure) which runs when a command button is
pressed on a spreadsheet. The procedure performs a certain task then calls a
second procedure which runs in a continuous loop until it reaches the end of
a series of active work sheets. At this point the second procedure exits and
returns control to the first procedure.

An example

Sub Procedure_Name1()
Do something
Call Procedure_Name2
End Sub

Sub Procedure_Name2()
On Error Goto Err_Procedure_Name2
Do some other stuff 'Perform an action on the active sheet
then move to next sheet
Call Procedure_Name2 'Loop until error
Err_Procedure_Name2:
Exit Sub
End Sub


This process works perfect but for some reason it stops after looping 23
times i.e. 23 sheets are updated when the loop stops with no error message.
Is there some restriction in VBA that prevents a procedure from looping more
than 23 times.

Any help would be appreciated.

...pc

Procedure loop limitation?
 

No limitation on loops. What you have is a recursive
subroutine. You've run out of stack space.
Re-write Procedure_Name2 to something like

Sub Procedure_Name2()
On Error Goto Err_Procedure_Name2
Do
'Perform an action on the active sheet then move to
next sheet
Do some other stuff
'Loop until error
Loop
err_Exit:
Exit Sub

Err_Procedure_Name2:
Resume err_Exit
End Sub

Be prepared to use the <Ctrl<Break keys in case it stays
in the loop

Kevin Beckham

-----Original Message-----
Hi,

I have created a macro (vba procedure) which runs when a
command button is
pressed on a spreadsheet. The procedure performs a
certain task then calls a
second procedure which runs in a continuous loop until it
reaches the end of
a series of active work sheets. At this point the second
procedure exits and
returns control to the first procedure.

An example

Sub Procedure_Name1()
Do something
Call Procedure_Name2
End Sub

Sub Procedure_Name2()
On Error Goto Err_Procedure_Name2
Do some other stuff 'Perform an action on
the active sheet
then move to next sheet
Call Procedure_Name2 'Loop until error
Err_Procedure_Name2:
Exit Sub
End Sub


This process works perfect but for some reason it stops
after looping 23
times i.e. 23 sheets are updated when the loop stops with
no error message.
Is there some restriction in VBA that prevents a
procedure from looping more
than 23 times.

Any help would be appreciated.

...pc


.

Procedure loop limitation?
 

PC,

Not sure why you get the problem, but why are you using recursive code? Why
not just loop through the sheets all in one routine?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"PC" <paulm DOT c at iol DOT ie wrote in message
...
Hi,

I have created a macro (vba procedure) which runs when a command button is
pressed on a spreadsheet. The procedure performs a certain task then calls
a
second procedure which runs in a continuous loop until it reaches the end
of
a series of active work sheets. At this point the second procedure exits
and
returns control to the first procedure.

An example

Sub Procedure_Name1()
Do something
Call Procedure_Name2
End Sub

Sub Procedure_Name2()
On Error Goto Err_Procedure_Name2
Do some other stuff 'Perform an action on the active sheet
then move to next sheet
Call Procedure_Name2 'Loop until error
Err_Procedure_Name2:
Exit Sub
End Sub


This process works perfect but for some reason it stops after looping 23
times i.e. 23 sheets are updated when the loop stops with no error
message.
Is there some restriction in VBA that prevents a procedure from looping
more
than 23 times.

Any help would be appreciated.

..pc

Procedure loop limitation?
 

While you are showing pseudocode, it appears you are doing a recursive call
to procedure2. If this were the source of the problem, however, I would
expect to get an out of stack space error.

Based on the set up of your error handling, I suspect you get an error in
the processing of the 23rd sheet and this exits your procedure.

Try commenting out your error handler and see if you don't get an error (or
set the option to break on all errors in the VBE).

--
Regards,
Tom Ogilvy

"PC" <paulm DOT c at iol DOT ie wrote in message
...
Hi,

I have created a macro (vba procedure) which runs when a command button is
pressed on a spreadsheet. The procedure performs a certain task then calls
a
second procedure which runs in a continuous loop until it reaches the end
of
a series of active work sheets. At this point the second procedure exits
and
returns control to the first procedure.

An example

Sub Procedure_Name1()
Do something
Call Procedure_Name2
End Sub

Sub Procedure_Name2()
On Error Goto Err_Procedure_Name2
Do some other stuff 'Perform an action on the active sheet
then move to next sheet
Call Procedure_Name2 'Loop until error
Err_Procedure_Name2:
Exit Sub
End Sub


This process works perfect but for some reason it stops after looping 23
times i.e. 23 sheets are updated when the loop stops with no error
message.
Is there some restriction in VBA that prevents a procedure from looping
more
than 23 times.

Any help would be appreciated.

..pc

Procedure loop limitation?
 

Thanks for your help Guys - got it working


"PC" <paulm DOT c at iol DOT ie wrote in message
...
Hi,

I have created a macro (vba procedure) which runs when a command button is
pressed on a spreadsheet. The procedure performs a certain task then calls
a
second procedure which runs in a continuous loop until it reaches the end
of
a series of active work sheets. At this point the second procedure exits
and
returns control to the first procedure.

An example

Sub Procedure_Name1()
Do something
Call Procedure_Name2
End Sub

Sub Procedure_Name2()
On Error Goto Err_Procedure_Name2
Do some other stuff 'Perform an action on the active sheet
then move to next sheet
Call Procedure_Name2 'Loop until error
Err_Procedure_Name2:
Exit Sub
End Sub


This process works perfect but for some reason it stops after looping 23
times i.e. 23 sheets are updated when the loop stops with no error
message.
Is there some restriction in VBA that prevents a procedure from looping
more
than 23 times.

Any help would be appreciated.

..pc