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

slave_to_vba

New Member
Joined
Aug 2, 2010
Messages
5
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!! :confused:

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
------------------------------------------------------------------------
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,413
Messages
5,511,189
Members
408,829
Latest member
sheshe123

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top