XL Err Msg: "Not Enough Stack Space to Run Macro"
ID: Q111867
|
The information in this article applies to:
-
Microsoft Excel for Windows, versions 5.0, 5.0c
-
Microsoft Excel for Windows 95, version 7.0
-
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
SYMPTOMS
In Microsoft Excel, when you choose a control in a dialog box that is
assigned to an event macro when there are a total of three dialog boxes on
the screen that have not been dismissed, you may receive the following
error message(s):
Not Enough Stack Space to Run Macro
-or-
Error 28: Out of Stack Space
-or-
Run-time error '28':
Out of stack space
WORKAROUND
To avoid receiving either of these error messages when you call nested
dialog boxes, do not assign a dialog control (such as a button or a check
box) to a macro event that calls another dialog box. Instead, assign the
control to first dismiss the active dialog box, then call the desired
dialog box from the same the macro that called the first dialog box. To
dismiss the active dialog box, do any of the following:
- Format the control with the Dismiss property:
- Select the control and choose Object from Format menu.
- In the Format Object dialog box, select the Dismiss check box on the
Control tab.
-or-
- Assign the control to a macro that contains the following command:
ActiveDialog.Hide
-or-
- Format the control with the Cancel property:
- Select the control and choose Object from Format menu.
- In the Format Object dialog box, Select the Cancel check box on the
Control tab.
- For an additional workaround, please see the following article(s) in the
Microsoft Knowledge Base:
Q125805 XL: Displaying Several Dialog Boxes Without Looping
MORE INFORMATION
A dialog box is not updated until after the event macro has finished. The
event macro is the code that is run when an action, such as choosing a
button, is taken in the dialog box. Because the dialog box is not updated,
if you call a dialog from another dialog, the first dialog is still loaded
(stacked) and is not released until the code that it ran (calling the
second dialog) has completed. This condition exists even if the property of
the button that called the second dialog is set to dismiss.
In Microsoft Excel 5.0, you can stack dialog boxes (that is, display more
than one dialog box on the screen at one time) with the top dialog box
active. You can stack two dialog boxes, and still run code assigned to
controls on the second dialog box. For example, you can call Dialog Two
from Dialog One, and then run a macro assigned to a control on Dialog Two
by choosing the control. In addition, you can call Dialog Three from Dialog
Two, but you cannot run any event macros from this top level (Dialog Three)
dialog box.
Note that you cannot hide a dialog box until the macro that hides the
dialog box ends.
Visual Basic Example
Microsoft provides programming examples 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. This article assumes that you are familiar with the programming
language being demonstrated and the tools used to create and debug
procedures. Microsoft support professionals can help explain the functionality
of a particular procedure, but they will not modify these examples to
provide added functionality or construct procedures to meet your specific
needs. If you have limited programming experience, you may want to contact
the Microsoft fee-based consulting line at (800) 936-5200. For more
information about the support options available from Microsoft, please see
the following page on the World Wide Web:
http://www.microsoft.com/support/supportnet/overview/overview.asp
In the following example, there are three dialog boxes. Each dialog
box can call either of the other two dialog boxes. Each button is
assigned to a macro that sets the value of a variable. The value of
the variable determines which dialog box to display next.
- Create three dialog sheets. Modify each dialog sheet to contain the
following:
Dialog Sheet
Buttons Dialog1 Dialog2 Dialog3
----------------------------------------
OK yes yes yes
Cancel yes yes yes
Go_To_Dialog1 no yes yes
Go_To_Dialog2 yes no yes
Go_To_Dialog3 yes yes no
- Set the dismiss property for each button by doing the following:
- Select the button on the dialog sheet.
- From the Format menu, choose Object. Select the Control tab, and
select the Dismiss check box. Choose OK.
- In a new module in the same workbook, type the following:
' Define the variable as Integer type
Dim dialog_number As Integer
Sub Main()
dialog_number = 1 'initialize the variable
DialogSheets("Dialog1").Show 'display the first dialog sheet
While dialog_number > 0 'while variable is greater than 0
Select Case dialog_number 'display a dialog based on the
'value of dialog_number
Case 1
'dialog_number is 1
DialogSheets("Dialog1").Show 'display dialog1
Case 2
'dialog_number is 2
DialogSheets("Dialog2").Show 'display dialog2
Case 3
'dialog_number is 3
DialogSheets("Dialog3").Show 'display dialog3
End Select
Wend
End Sub
'The following code sets the value of the dialog_number variable
Sub Go_To_Dialog1()
dialog_number = 1
End Sub
Sub Go_To_Dialog2()
dialog_number = 2
End Sub
Sub Go_To_Dialog3()
dialog_number = 3
End Sub
Sub OK_Or_Cancel()
dialog_number = 0
End Sub
- Select the Dialog1 sheet tab. Select the Go_To_Dialog2 button, and
choose Assign Macro from the Tools menu. From the Macro Name/Reference
list, select Go_To_Dialog2() and choose OK.
- Repeat step 4 for each button, assigning the corresponding macro to each
button on each dialog sheet. Assign the OK_Or_Cancel macro to each OK
and Cancel button.
REFERENCES
"Visual Basic User's Guide," version 5.0, page 219-239
Additional query words:
5.0 overflow
Keywords : kbcode kbprg PgmOthr
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0
Platform : MACINTOSH WINDOWS
Issue type : kbprb
|