dreid1011
Well-known Member
- Joined
- Jun 4, 2015
- Messages
- 3,604
- Office Version
- 365
- Platform
- 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:
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.
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)
Anyway, I am curious if there is something I am missing to help make this more efficient before it overflows.
Then after some searching I came across a test to see what my limits might be for recursive calls:
Is there a call stack level limit?
I have a couple of colleagues looking at some bad code in Excel VBA, wondering is there a limit to the number of levels in a call stack
stackoverflow.com
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.