# Please help with recursive sub!! Out of stack space problem...

#### slave_to_vba

##### New Member
Hi,

Every now and then I get a 'ran out-of-stack space' msg, and I know it's probably due to the fact that my routine (randomf) is calling itself several times, but I don't know any other way to code this!! So could someone please take a look at my code and make some recommendations?? maybe in terms of defining the variables, or whatever you can think of, thanks!! I appreciate it. I'm using VBA in Excel 2007

This randomf routine is simply called by a For loop N number of times (ex. 50) in another subroutine 'X'. Within the routine there are also two mini subs.

Dim m As Integer
Dim x As Integer
Dim y As Integer
Dim x1 As Integer
Dim y1 As Integer
Dim maxX As Integer
Dim maxY As Integer
Dim busy As Integer
Dim direction As Integer
Dim COORD As Variant
Dim crossindex As String
Dim coord_count As Integer
Public walk_history(1 To 30) As Variant

----------------------------------------------------------------------
Sub randomf(x, y, maxX, maxY, COORD, coord_count, m)

Dim coord_check As String
Dim rng As Range

Randomize
direction = Int((4 - 1 + 1) * Rnd + 1)

If m > 1 Then
Call opposite_direct(m)
End If

If m > 3 Then
If busy < 4 Then
Call linearcheck(m)
End If
End If

If direction = 1 Then
y1 = y + 2
x1 = x
ElseIf direction = 2 Then
x1 = x + 2
y1 = y
ElseIf direction = 3 Then
y1 = y - 2
x1 = x
ElseIf direction = 4 Then
x1 = x - 2
y1 = y
End If

If y1 >= 0 And x1 >= 0 And x1 <= maxX And y1 <= maxY Then

incoord = Empty
For i = 1 To UBound(COORD, 2)
If COORD(1, i) = x1 And COORD(2, i) = y1 Then
incoord = "found it"
End If
Next

If incoord <> Empty Then
busy = busy + 1
Call randomf(x, y, maxX, maxY, COORD, coord_count, m)
Else

x = x1
y = y1
coord_count = coord_count + 1
COORD(1, coord_count) = x
COORD(2, coord_count) = y

walk_history(m) = direction

End If

Else 'if x1 and y1 are outside of matrix limits
Call randomf(x, y, maxX, maxY, COORD, coord_count, m) 'again
End If

End Sub
-----------------------------------------------------------------------
Sub linearcheck(m)

If direction = walk_history(m - 1) And direction = walk_history(m - 2) And direction = walk_history(m - 3) Then

Do Until direction <> walk_history(m - 1)
Randomize
direction = Int((4 - 1 + 1) * Rnd + 1)
Call opposite_direct(m)
Loop

End If

End Sub
-------------------------------------------------------------------------
Sub opposite_direct(m)

crossindex = direction & walk_history(m - 1)

If crossindex = "24" Or crossindex = "42" Or crossindex = "31" Or crossindex = "13" Then

Do Until crossindex <> "24" And crossindex <> "42" And crossindex <> "31" And crossindex <> "13"
Randomize
direction = Int((4 - 1 + 1) * Rnd + 1)
crossindex = direction & walk_history(m - 1)
Loop

End If

End Sub
------------------------------------------------------------------------

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Replies
1
Views
305
Replies
1
Views
162
Replies
0
Views
444
Replies
7
Views
141
Replies
5
Views
169

Threads
1,133,382
Messages
5,658,492
Members
418,447
Latest member
AdamAl

### Share this page ### 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