Stack overflow (literally) - Error 28 out of stack space

[iliace]

Hi Everyone,

I wrote a VBA script about 10 years ago, which worked fine when I first built it (so long ago - the file name is still ".xls") I tried to use it recently in a modern version of Excel, and I am getting "Error 28 out of stack space" in certain situations. This was never an issue during the original development.

Specifically: the VBA is an algorithm that solves Sudoku puzzles, by trial and error, and with visualization. The algorithm is recursive in nature, and the stack overflow tends to occur just above ~50 levels of recursion. I can post the code, but I don't think that would be very informative; this is more of a conceptual question.

Does anyone have any insight into how stack limitations have changed in VBA in recent versions of Excel? I am trying to wrap my mind around the fact that it worked in year 2007, in Excel 2003/2007, but now throws this error for exact same scenarios in Office 365, which did not happen previously.

Windows 10 Professional (Version 1803) OS Build 17134.765, Excel 1906, Build 11708.20006.

 

[OldGuy69]

Given your post is a year old I'm guessing you found the info you wanted? This may or may not be relevant now. I just joined this site to mention something about the Stack Overflow error in my VBA macro that used to drive me to drink, and every site I looked at for a solution was far too complicated for me as a minor user.

Anyway, I solved the problem myself by including a counter (e.g. A1 = A1+1) in the code which incremented 1 each time it ran the tests in the Macro (which aren't relevant to this post). I took note of the number of steps it counted when it got the error. In my case it was 92. I then added a line in the code that checked if A1 was 90. If it wasn't 90 it kept working as normal. If it was 90, it zeroed the counter, exited the sub, went to the top and started running again. For some reason this must clear the stack or buffer or whatever memory problem excel has when running looping macros.

No doubt the boffins will scorn this method, but it works for me.
Incidentally, the [br1] = [br1] + 1 is the counter which resets to zero at 90
The line below it simply keeps count of how many steps altogether (for interest's sake)

Cheers
OldGuy69

Sub Macro44()
'
' Macro44 Macro
'

'
TopLine:
Range("M2:BE3").Select
ActiveWorkbook.Worksheets("monSpec").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("monSpec").Sort.SortFields.Add Key:=Range("M3:BE3") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("monSpec").Sort
.SetRange Range("M2:BE3")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
[br1] = [br1] + 1
[bs1] = [bs1] + 1
If [BR15] > [BR13] Then Exit Sub
If [BS15] > [BS13] Then Exit Sub
If [BT15] > [BT13] Then Exit Sub
If [BU15] > [BU13] Then Exit Sub
If [BV15] > [BV13] Then Exit Sub
If [BW15] > [BW13] Then Exit Sub
If [BX15] > [BX13] Then Exit Sub
If [BY15] > [BY13] Then Exit Sub
If [BZ15] > [BZ13] Then Exit Sub
If [Ca15] > [CA13] Then Exit Sub
If [br1] = 90 Then [br1] = 0 Else GoTo TopLine
End With
Application.Run "MACRO44"
End Sub