XL: "Out of Stack Space" Error Running Sub Procedure

ID: Q126090

5.00 5.00c 7.00 7.00a | 5.00 5.00a

WINDOWS               | MACINTOSH
kbprg kbcode

The information in this article applies to:

SYMPTOMS

In Microsoft Excel, when you run a Visual Basic Sub procedure that calls another procedure, you may receive the following error message:

   Run-time error '28':
   Out of stack space

CAUSE

This error message appears when one procedure in your module calls another procedure, and that procedure calls another procedure, and so on. The error message occurs in this case because when you call one

sub procedure from another procedure, the first procedure is not
unloaded from memory until the procedure that it called is finished. Because of this behavior, if you continue to call a procedure from another procedure, you may run out of stack space, or memory allotted for the macro, before the last procedure is called.

WORKAROUND

To work around this problem, call individual sub procedures from one main procedure, instead of calling one sub procedure from another sub procedure, and so on. For example, if you are calling sub procedures as in the following example

   Sub one()
      two
   End Sub

   Sub two()
      three
   End Sub

   Sub three()
      four
   End Sub

you can call these sub procedures from the first procedure as in the following example:

   Sub Main()
      one
      two
      three
      four
   End Sub

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support professionals can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

MORE INFORMATION

The Visual Basic Reference indicates that the "Out of stack space" error may be caused by too many active Function or Sub calls. To correct this problem, Help suggests that you verify that general recursive procedures are not nested too deeply and that they terminate properly.

REFERENCES

For more information about Trappable Errors, choose the Search button in the Visual Basic Reference and type:

   error messages

Additional reference words: 5.00 5.00c visual basic applications
Keywords          : kbprg 
Version           : 5.00 5.00c 7.00
Platform          : WINDOWS

Last Reviewed: May 17, 1999