Out of Stack Space

**process** · 01-01-2012, 02:56 PM

I keep getting the error message "out of stack space" when the VB in the attached file runs. Somtimes Excel just crashes, other times I get the "out of stack space" message. I have tried to disable events, but then the VB doesn't run. I there a way to "clear the stack" after each write to the target cell so the stack doesn't fill? Or other way to keep the spreadsheet running?

**protonLeah** · 01-01-2012, 03:14 PM

Please explain what you are trying to do. I don't get your code.

**process** · 01-01-2012, 06:05 PM

I am entering the value of the selected cell into another cell. The value of this other cell is written to an OPC point with is inputed to a PLC that tracks changes. Normally the PLC is tracking real time data. I am writing values with this spreadsheet to capture historical data. I take data from a historian, copy it into this sheet. Then when I select a cell, the selected cell continuously selects the cell below it. Each time a cell is "selected" the data from the newly selected cell is written to the PLC that tracks the values. It is just a way to go back and gather information from past data. People can send me a file of old data. I run it through this "tracker/counter" and send them a report. The PLC is actually tracking the values, how many changes, how big of changes, etc... This spreadsheet is just a way to input the values to the PLC. There is an add-in that sends the value of cell E2 to the PLC. I deleted the cell that does this as I was troubleshooting to see if thiscaused it. When I deleted this cell then I received the out of stack space error.

**process** · 01-01-2012, 07:55 PM

Update - When I take out the line "ActiveCell.Offset(1, 0). Select" Then I do not get the Out if Stack Space Error. Although I have to advance the selected cell manually. Is there a way to put this back in, but limit the time it takes to advance to the next cell. (A time delay of say 1 second before selecting the next cell?).

**protonLeah** · 01-01-2012, 08:40 PM

You said: "... I am entering the value of the selected cell into another cell..."
But: Sheet1.Range("e2").Value = Sheet1.Range("e2").Value. Won't do that. All that can do is overwrite the data in E2 over itself (maybe it's just a type-o).

2. Every time: ActiveCell.Offset(1, 0).Select is executed it calls the selection change macro. This seems to be an endless loop since the macro never terminates but gets called repeatedly in a new instance (even as others are still running). Since a sheet can have 10^6+ rows that means Excel will try to run just as many instances of the macro until it runs out of stack space to keep track register data and flag status, etc.
You should have: Application.EnableEvents = False as the first line of code so that when/if the .Select statement is executed it won't trigger another instance of the macro when it moves down; and Application.EnableEvents = True as the very last line to catch further changes once the macro is exited back to the sheet.

**Jack in the UK** · 01-01-2012, 08:48 PM

You computer has run out of stack memory no shock ah?
It’s a run time error so is an error that pops up when You run the code
This is the area where Excel / M$ Windows stores temporary items during program run time
1) You need to look at the code and tweak it make it more efficient and runs smoothly
2) Reboot your computer and try again
With no other applications or as few as poss running at the same time thus giving Excel full beans (that’s not strictly true and excel manages its own memory quite unlike any other application that’s forced by windows, BUT Windows allocated Excel a chunk and that’s it Excel decides thus why from time to time we hit these errors)
And with as few other applications running as possible.

**process** · 01-01-2012, 10:13 PM

I want cell e2 to be overwritten by the next value. Even if I take out "Sheet1.Range("e2").Value = Sheet1.Range("e2").Value" and just leave in
"Sheet1.Range("e2")=ActiveCell.Value" I still get the error. I want the macro to scroll to the next cell down, and write that value into cell e2. Then scroll down to the next cell down and write that value into cell e2. And it can do that until I tell it to stop. Or we could put a stop value in like stop when it hits row 10000.

This sheet works fine as far as writing the value to the PLC except when I add the "ActiveCell.Offset(1, 0).Select". Then it works for a while and stops with an error.

**Leith Ross** · 01-02-2012, 12:50 AM

Hello Process,

Welcome to the Forum!

The Stack is a dedicated area of memory used to store the pointers to results of operations, values of variables and other Sub or Function calls. When data is added to the stack or "pushed" onto the stack, sometime later it will be removed or "popped" from the stack. Most of the time these operations are automatic and transparent to the programmer.

The exception to this rule is when writing code for the Worksheet_Change and Worksheet_SelectionChange events. You can easily create what is known as a Cascade event. This happens when your code triggers the event again. This places the address of the event procedure on the stack. The code calls the event again and the process repeats until there is no stack space left. In all other operating systems I have used, the stack has always been managed to prevent programs from writing into main memory and crashing the system. This is not true with Windows.

I have made a few changes to your code to prevent the cascade failure. The key is to disable and re-enable event handling.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.EnableEvents = False
    
        If ActiveCell = "" Then
           Sheet1.Range("E2") = Sheet1.Range("e2").Value
        Else
           Sheet1.Range("E2") = ActiveCell.Value
           Sheet1.Range("E1") = ActiveCell.Address
           ActiveCell.Offset(1, 0).Select
        End If
    
    Application.EnableEvents = True

End Sub

**process** · 01-02-2012, 11:31 AM

I added the Application.Enable.Events lines, this cured the out of stack space error but then the vb would stop. So then I added a Do Loop to run until it hits an empty cell. It seems to be working fine. Thank you Ben and Leith for your help!