Microsoft KB Archive/111867

= "Not Enough Stack Space to Run Macro" =

Article ID: 111867

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0a for Macintosh

-



This article was previously published under Q111867





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.</li> In the Format Object dialog box, Select the Cancel check box on the Control tab.</li></ol> </li> For an additional workaround, please see the following article(s) in the Microsoft Knowledge Base:

125805 XL: Displaying Several Dialog Boxes Without Looping

</li></ul>

<div class="moreinformation_section">

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. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. 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: <pre class="fixed_text">     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                        </li> Set the dismiss property for each button by doing the following:

 Select the button on the dialog sheet.</li> From the Format menu, choose Object. Select the Control tab, and select the Dismiss check box. Choose OK.</li></ol> </li>  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 </li> 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.</li> 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.</li></ol>

<div class="references_section">