This documentation is archived and is not being maintained.

Creating Recursive Procedures

This page is specific to the Visual Basic for Applications (VBA) Language Reference for Office 2010.

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.