Creating Recursive Procedures
Last modified: December 06, 2011
Applies to: Office 2013 | VBA
Procedures have a limited amount of space for variables. Each time a procedure calls itself, more of that space is used. A procedure that calls itself is a recursive procedure. A recursive procedure that continuously calls itself eventually causes an error. For example:
Function RunOut(Maximum) RunOut = RunOut(Maximum) End Function
This error may be less obvious when two procedures call each other indefinitely, or when some condition that limits the recursion is never met. Recursion does have its uses. For example, the following procedure uses a recursive function to calculate factorials:
Function Factorial (N) If N <= 1 Then ' Reached end of recursive calls. Factorial = 1 ' (N = 0) so climb back out of calls. Else ' Call Factorial again if N > 0. Factorial = Factorial(N - 1) * N End If End Function
You should test your recursive procedure to make sure it does not call itself so many times that you run out of memory. If you get an error, make sure your procedure is not calling itself indefinitely. After that, try to conserve memory by:
Eliminating unnecessary variables.
Using data types other than Variant.
Re-evaluating the logic of the procedure. You can often substitute nested loops for recursion.