title: excel vba recursive function
body: <p>I am trying to show a path of related project activities. Basically you can think of it as a directed graph. I made an adjacency matrix of it.</p>
  
  <pre><code>      STA A1.1 A1.2 ...
  STA    0   1    0   ... 
  A1.1   0   0    1   ...
  A1.2   0   0    0   ...
  ...   ... ...  ...  ...
  </code></pre>
  
  <p>Then I wrote a subroutine to find the predecessors of a selected activity but what I would really need is to show all related activities from the start. For the example if A1.2 is selected it should print out [STA, A1.1, A1.2]. If the end result is selected where all activities lead too all activities should be printed out in the correct order. The different paths could be separated like this [STA, A1.1, A1.2, ... END],[STA, A2.1, A2.2, ... END],[STA, A3.1, ...]
  My code so far which prints out only the predecesoors of a chosen activity:</p>
  
  <pre><code>'---------------------------------
  Sub RunThings()
  
  Application.ScreenUpdating = False
  
   Call UserInput
  
  Application.ScreenUpdating = True
  
  End Sub
  '---------------------------------
  Sub UserInput()
  
  Dim iReply As Variant
  
  iReply = Application.InputBox(Prompt:="Please enter activity name", Title:="FIND     ACTIVITY PATH", Type:=2)
  
  'MsgBox (iReply)
  
  If iReply = False Then
      Exit Sub
  Else 'They cancelled (VbCancel)
      If iReply &lt;&gt; "" Then
          Call Findpath(CStr(iReply))
      End If
  End If
  
  Exit Sub
  
  End Sub
  
  '---------------------------------
  
  Function FindRowCol(term As String, row As Boolean)
  
  Dim SearchRange As Range
  Dim FindRC As Range
  
  If row = False Then
      Set SearchRange = Range("A1", Range("T1").End(xlUp))
  Else
      Set SearchRange = Range("A1", Range("A65536").End(xlUp))
  End If
  
  Set FindRC = SearchRange.Find(term, LookIn:=xlValues, lookat:=xlWhole)
  
  If row = False Then
      FindRowCol = FindRC.Column
  Else
      FindRowCol = FindRC.row
  End If
  
  End Function
  '---------------------------------
  
  Sub Findpath(activity As String)
  
  Application.ScreenUpdating = False
  
  
  ActCol = FindRowCol(activity, False)
  
  
  For i = 2 To 65536
      If Cells(i, 1).Value = "" Then
          LastRow = Cells(i, 1).row - 1
          Exit For
      End If
  Next i
  
  Dim Predecessors() As Variant
  Dim Counter As Integer
  Counter = 0
  
  For j = 1 To LastRow
      If Cells(j, ActCol).Value = 1 Then
         Counter = Counter + 1
  
      End If
  Next j
  
  ReDim Predecessors(1 To Counter)
  
  Insert = 1
  
  For j = 1 To LastRow
      If Cells(j, ActCol).Value = 1 Then
         Predecessors(Insert) = Cells(j, 1).Value
         Insert = Insert + 1
      End If
  Next j
  
  Dim CurrAct As String
  
  For k = LBound(Predecessors) To UBound(Predecessors)
  
      CurrAct = CStr(Predecessors(k))
      MsgBox (CurrAct)
  
  Next k
  
  Application.ScreenUpdating = True
  
  End Sub
  '---------------------------------
  </code></pre>
  
  <p>My question would be is it possible to change the subroutine Findpath into a recursive function to print out all related activities?</p>
  
  <p>This is the complete adjacency matrix:</p>
  
  <p>STA A1.1    A1.2    A1.3    A1.4    A1.5    A2.1    A2.2    A2.3    A2.4 A2.5   A3.1    A4.1    A4.2    A4.3    A4.4    A4.5    A5.1    END
  STA 0   1   0   0   0   0   1   0   0   0   0   1   1   0   0   0   0   0   0
  A1.1    0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
  A1.2    0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
  A1.3    0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0
  A1.4    0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0
  A1.5    0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
  A2.1    0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0
  A2.2    0   0   0   0   0   0   0   0   1   0   0   0   0   0   1   0   0   0   0
  A2.3    0   0   0   0   0   0   0   0   0   1   1   0   0   0   0   0   0   0   0
  A2.4    0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
  A2.5    0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
  A3.1    0   0   0   0   0   0   0   1   0   0   0   0   0   1   0   0   0   0   0
  A4.1    0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0
  A4.2    0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0
  A4.3    0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   1   0   0
  A4.4    0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
  A4.5    0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
  A5.1    0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1
  END 0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0</p>