| title | keywords | f1_keywords | ms.prod | ms.assetid | ms.date | ms.localizationpriority |
|---|---|---|---|---|---|---|
|
Out of stack space (Error 28) |
vblr6.chm1000028 |
vblr6.chm1000028 |
office |
ce345551-ad57-1120-546a-239d144c330a |
06/08/2017 |
medium |
Out of stack space (Error 28)
The stack is a working area of memory that grows and shrinks dynamically with the demands of your executing program. This error has the following causes and solutions:
-
You have too many active Function, Sub, or Property procedure calls. Check that procedures aren’t nested too deeply. This is especially true with recursive procedures, that is, procedures that call themselves. Make sure recursive procedures terminate properly. Use the Calls dialog box to view which procedures are active (on the stack).
-
Your local variables require more local variable space than is available.
Try declaring some variables at the module level instead. You can also declare all variables in the procedure static by preceding the Property, Sub, or Function keyword with Static. Or you can use the Static statement to declare individual Static variables within procedures.
-
You have too many fixed-length strings. Fixed-length strings in a procedure are more quickly accessed, but use more stack space than variable-length strings, because the string data itself is placed on the stack. Try redefining some of your fixed-length strings as variable-length strings. When you declare variable-length strings in a procedure, only the string descriptor (not the data itself) is placed on the stack. You can also define the string at module level where it requires no stack space. Variables declared at module level are Public by default, so the string is visible to all procedures in the module.
-
You have too many nested DoEvents function calls. Use the Calls dialog box to view which procedures are active on the stack.
-
Your code triggered an event cascade. An event cascade is caused by triggering an event that calls an event procedure that’s already on the stack. An event cascade is similar to an unterminated recursive procedure call, but it’s less obvious, since the call is made by Visual Basic rather than by an explicit call in your code. Use the Calls dialog box to view which procedures are active (on the stack).
To display the Calls dialog box, select the Calls button to the right of the Procedure box in the Debug window or choose the Calls command. For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).
[!includeSupport and feedback]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim b As Integer
b = 0
Dim cell As Range
Dim rgn As Range
Set rgn = Range("f2:f200")
For Each cell In rgn
If IsEmpty(cell) = False Then
b = b + 1
End If
Next
Range("d2").Value = b
End Sub
Hi, I met a problem when trying to run the following piece of Excel VBA code. A message box will pop out and say there is a
«out of stack space»
problem to line Set rgn = range("f2:f200"), then another message box will pop out and say
«method ‘value’ of object ‘range’ failed»
I don’t know what is wrong… Thank you very much for helping.
asked Jun 13, 2017 at 7:36
The problem is that you are changing cells in a Change event, which will trigger the event again, and again, and again…
You need to disable events temporarily:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim b As Integer
b = 0
Dim cell As Range
Dim rgn As Range
Set rgn = Range("f2:f200")
For Each cell In rgn
If IsEmpty(cell) = False Then
b = b + 1
End If
Next
Application.Enableevents = False
Range("d2").Value = b
Application.Enableevents = True
End Sub
answered Jun 13, 2017 at 7:42
RoryRory
32.5k5 gold badges32 silver badges34 bronze badges
2
|
denis76 Пользователь Сообщений: 22 |
#1 31.05.2016 15:23:02 Добрый день! Столкнулся неожиданно с такой ситуацией: при вызове вложенной процедуры в VBA появляется указанное сообщение об ошибке:
Собственно говоря, процедура довольно громоздкая, обрабатывает и пересчитывает массивы данных размером примерно 10000*200 ячеек… хотя работала до сего времени вполне нормально… Собственно говоря, в чем здесь может быть причина появления такого сообщения: Какие тут способы решения имеются — может, надо как-то уничтожать переменные после использования, очищать стек — как это сделать?… Может, какие-то параметры в реестре надо подправить или еще где?.. Что известно по данному вопросу уважаемым специалистам?.. Спасибо заранее… |
||
|
Sanja Пользователь Сообщений: 14853 |
Собственно говоря почему-бы не в ПОИСК ? Согласие есть продукт при полном непротивлении сторон. |
|
denis76 Пользователь Сообщений: 22 |
#3 31.05.2016 15:46:06 Хорошо… Вот, смотрю, там 5 случаев указано, к моему может, видимо, это относиться:
Все равно странно, больше всего ведь места массивы занимают, но не на них ошибка вылезает… Еще вот, смотрю в редакторе VBA — там такая закладка Call stack — почему-то в ней список в 50 с лишним процедур, когда у меня столько их одновременно не вызывается… и некоторые повторяются почему-то… |
||
|
Нет файла — нет идей, что тут не понятно. |
|
|
Игорь  Пользователь Сообщений: 3658 |
Причина — большой размер массивов Как вариант еще одной причины, — вызов функций с аргументами в виде больших массивов |
|
denis76 Пользователь Сообщений: 22 |
#6 31.05.2016 16:12:19
Глубоко сомневаюсь, что данный файл кому-то разбирать охота будет… но думаю, что основные варианты можно предположить и без его досконального исследования… |
||
|
denis76 Пользователь Сообщений: 22 |
#7 31.05.2016 16:14:51
Про массивы — да, все так, но ведь он не выдает Out of memory (это прошло с установкой 64-битной версии), а вот на стек именно ругается-то… А каков этот стек и его допустимый размер — мне пока неведомо… |
||
|
denis76 Пользователь Сообщений: 22 |
#8 31.05.2016 16:17:04
Так вроде бы по умолчанию они так и передаются, нет разве?.. И вроде как лишних копий не делается… Изменено: denis76 — 01.06.2016 22:06:07 |
||
|
Hugo Пользователь Сообщений: 23391 |
Мне стека не хватало пару раз из-за рекурсии. |
|
denis76 Пользователь Сообщений: 22 |
Разобрался, в чем дело… когда начал код улучшать, нашел место, где процедуры вызывают одна другую поочередно… |
|
Hugo Пользователь Сообщений: 23391 |
#11 01.06.2016 12:07:48 Т.е. тоже виновата рекурсия? |
I have a very simple macro in an Excel sheet, to allows users to recalculate.
I have no other macros/code in the workbook, and only this work book open
Sub Calculate()
Calculate
End Sub
This is activated by a button.
However, when pressed I get two error boxes, see image.
What does Out of stack space mean ?
And how do I resolve this issue ?
I have looked on this website:
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/out-of-stack-space-error-28
It says I may have too many funtions ??
This macro used to work fine, and it is hardly doing a lot so cannot understand the issue.
I am able to calculate the sheet using the option under the formulas tab.
asked Nov 29, 2018 at 10:49
PeterHPeterH
7,34720 gold badges53 silver badges81 bronze badges
4
The function you have define is recursive, calling itself unconditionally until the stack is filled with all the calls.
You should change the name of your subroutine, eg:-
Sub Calc()
Calculate
End Sub
If you link Calc() to the button, you avoid any recursion.
answered Nov 29, 2018 at 11:07
AFHAFH
17.2k3 gold badges31 silver badges48 bronze badges
4
You’re calling Calculate inside of Calculate. Every call to Calculate causes another call to Calculate, which will then call Calculate… Then eventually you get that error when the stack fills up.
answered Nov 29, 2018 at 11:07
EnigmanEnigman
7653 silver badges7 bronze badges
3
Dear reader,
I wonder if you could help me with a rather tenacious problem I am facing in an application I am developing in MS Access 2007 / VBA (OS is Windows 7 Enterprise 32 bits).
The application has been built using the client-server architecture. The server is a accdb on a network share, containing only tables. The client is a accdb on a workstation, and contains queries, forms and modules. In the client accdb I have built a form
containing a subform. The subform is displayed as a datagrid. The user can filter and sort the data in the grid, and has the possibility to save his ‘searches’ for reuse. This is done by saving the SQL for the filter and sort clauses in a table in the database.
When a (test)user cycles through the saved searches, the client application crashes after a couple of searches, producing Access error 28: Out of stack space. Of course, I have taken great care to follow the guidelines provided in the MS Access help file
for this error. When the application crashes, the call stack never counts more than three or four levels. There are no static variables. There are a number of fixed length string variables, but only in the declaration space of forms and modules, not in the
procedures themselves (and the screen is opened only once, so there seems to be little chance of filling the memory with the same variables more than once). DoEvents has not been used. And there is no recursion, nor event procedures being fired more than once
— I checked that by debugging. On the whole, the VBA code seems quite healthy, and in the code I do not see any indication for an abnormal use of stack space.
It is difficult to say what exactly causes the crashes, but when they come, it is always at one of the following points:
a) When leaving the event procedure Form_ApplyFilter of the sub form (i.e. at the line «End Sub»). (The form contains a label showing which columns are involved in the current filter. I need this event to discover when the label text has to be updated.)
b) At the line «Set Me.Recordset = Me.OpportunityRecords(selectieOpResources)» in a procedure in the sub form. (OpportunityRecords is a property of the same sub form, returning a recordset.)
c) At the line «Me.FilterOn = True» in the same procedure. (The Filter property, of course, is set according to the value of the saved search.)
The crashes at b) and c) seem to be related to a timing problem. When I add calls to a logging procedure in a general module, before these problematic lines, the frequency of the crashes at these lines is greatly diminished. Generally, timing problems may
be solved by adding DoEvents statements. But, sadly, when I try to do that, the crashes return — probably because DoEvents has a negative effect on the stack space.
The crashes at a) are not influenced by adding any logging calls. I have found no means of reducing these crashes, except by commenting out the entire event procedure.
I have the feeling that this whole situation — and especially the crashes in Form_ApplyFilter — seems to point in the direction of a bug in MS Access. Could you shed any light on this, and do you know of a way of circumventing this problem? If worst comes
to worst, I might do without the label displaying the filter columns. But the possibility of filtering the data on the basis of saved searches is crucial for the application.
Thanks for your help!
Walter