Recursive Sub Calls and Stack Overflow

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
3,604
Office Version
  1. 365
Platform
  1. Windows
I've been playing around with some maze generating VBA and I run out of space about 3/4 of the way through the maze at 10x20 size. It will finish 10x10 maze just fine.

Then after some searching I came across a test to see what my limits might be for recursive calls:

VBA Code:
Dim count As Integer

Sub Rec()
    count = count + 1
    Cells(1, 1) = count
    Call Rec
End Sub

With this I was able to get about 37k loops before the overflow. I then tested it without the variable and wrote directly to the cell and I had to stop it over 2B loops because I had other work to do.

VBA Code:
Cells(1, 1) = 0
Sub Rec()
    Repeat:
    Cells(1, 1) = Cells(1, 1) + 1
    GoTo Repeat
End Sub

So now I tried to eliminate all the variables in the code from the youtube link above in favor of writing directly to the sheet, but that didn't seem to help much. (code below before eliminating all variables)

VBA Code:
Option Explicit
Dim r As Integer, c As Integer
Sub InitMaze()
With Range("B2:U11")
    .Borders.Weight = xlThick
    .Interior.Color = vbBlack
End With
r = 2: c = 2
Call CaveMaze
End Sub

Sub CaveMaze()
Repeat:
Dim direc As Integer, LoopCount As Integer

If Cells(r, c).Interior.Color = vbBlack Then
    Cells(r, c).Interior.Color = vbBlue
End If

LoopCount = 0

DoEvents

Do
    Randomize
    direc = Int((Rnd * 4) + 1)
    If direc = 1 And Cells(r + 1, c).Interior.Color = vbBlack Then
        Cells(r, c).Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
        r = r + 1
        Exit Do
    ElseIf direc = 2 And Cells(r, c + 1).Interior.Color = vbBlack Then
        Cells(r, c).Borders(xlEdgeRight).LineStyle = xlLineStyleNone
        c = c + 1
        Exit Do
    ElseIf direc = 3 And Cells(r - 1, c).Interior.Color = vbBlack Then
        Cells(r, c).Borders(xlEdgeTop).LineStyle = xlLineStyleNone
        r = r - 1
        Exit Do
    ElseIf direc = 4 And Cells(r, c - 1).Interior.Color = vbBlack Then
        Cells(r, c).Borders(xlEdgeLeft).LineStyle = xlLineStyleNone
        c = c - 1
        Exit Do
    End If

    LoopCount = LoopCount + 1

    If LoopCount > 9 Then
        Call BackTrack
        Exit Sub
    End If

Loop

GoTo Repeat

End Sub

Sub BackTrack()
Cells(r, c).Interior.Color = vbWhite

If Cells(r + 1, c).Interior.Color <> vbWhite And Cells(r, c).Borders(xlEdgeBottom).LineStyle = xlLineStyleNone Then
    r = r + 1
ElseIf Cells(r, c + 1).Interior.Color <> vbWhite And Cells(r, c).Borders(xlEdgeRight).LineStyle = xlLineStyleNone Then
    c = c + 1
ElseIf Cells(r - 1, c).Interior.Color <> vbWhite And Cells(r, c).Borders(xlEdgeTop).LineStyle = xlLineStyleNone Then
    r = r - 1
ElseIf Cells(r, c - 1).Interior.Color <> vbWhite And Cells(r, c).Borders(xlEdgeLeft).LineStyle = xlLineStyleNone Then
    c = c - 1
Else
    Exit Sub
End If

Call CaveMaze

End Sub

Anyway, I am curious if there is something I am missing to help make this more efficient before it overflows.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
You're welcome. I thought the recursive vs. iterative angle was interesting. I hadn't done one like that since I was in college.
 
Upvote 0

Forum statistics

Threads
1,223,101
Messages
6,170,116
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top