Just testing a before a possible Thread post. No reply needed

[DocAElstein]

Recursion Procedures in Excel VBA. Recursion technique in coding

Procedures Calling others
I think this can be very easy to understand. I think it has been made unnecessary hard to understand by two things:
_ (i) The usual initial explanation or definition : …. ..” A procedure that calls itself is a recursive procedure…” – forget that it is rubbish
_ (i) if you are familiar with stepping through a code in the VBEditor using F8 debug mode, ( http://www.eileenslounge.com/viewtop...247121#p247121 ) , then you might be confused into believing _ (i) if you step through coding which goes into recursion.

Procedures Calling others
A procedure ( also known as code , coding , or routine, or program ) is just a set of instruction which are followed when it “runs”
A procedure Calling another procedure is quite common in even simple programming. This just means that a running procedure has a code line that instructs another procedure to start. The technical term is that the original procedure Calls another.
What may not be immediately obvious, is that
_ a) generally VBA coding can only do one procedure at a time.
_ b) for a simple Calling of a procedure from another, the procedure doing the Calling will pause , whilst the Called procedure runs. When that Called procedure Ends , then the original paused procedure will carry on from the point where it paused, which will necessarily be the Call point

These points might not be so obvious by looking at the coding in the VB Editor window. For example, below is an initial code passing some information to a second procedure which it Calls
Those actual “procedures” are just instructions. You could consider those as your master copy, or on the other hand you might just consider them as one of many possible copies: There is nothing to stop you storing copies of all that all over the place, on computers, on paper etc… in any case, VBA will have made a note of where these instructions are. When you start the first code running, VBA will make a copy of the instructions ( Sub1 ) and run that. When VBA then reaches the code line Call Sub2(CpyNo:=StrtCpyNo, Msg:="Initial Message") , it will pause Sub1 , while it makes a copy of the second procedure instructions, Sub2 , and it will run through those instructions until they are finished, at which point it resumes Sub1 at the Call code line.

If you go unto the VBEditor ( Via keys Alt+F11 from Excel ) , then click anywhere in Sub1 and hit key F8 , then you will proceed in step mode through the coding. If you bear in mind the points _ a) and _ b) , then you might be able to follow and understand what is going on.

Option Explicit
Sub Sub1()
Dim StrtCpyNo As Long ' To count copy number of code instructions being run
 Let StrtCpyNo = 1
 
 
 
 Call Sub2(CpyNo:=StrtCpyNo, Msg:="Initial Message")
 
 MsgBox Prompt:="Ending main procedure"
End Sub
' Code above is Main calling procedure_________________________________________________________________________________

' Code below is called procedure
Sub Sub2(ByVal CpyNo As Long, ByVal Msg As String)
Dim CopyNo As Long ' This is to indicate which copy of Sub2 is currently running
 Let CopyNo = CpyNo
 MsgBox Prompt:="You are Starting Sub2 , copy  " & CopyNo
 
 MsgBox Prompt:="You are Ending Sub2 , copy  " & CopyNo
End Sub

Because the first routine pauses whilst the second routine is done, we could attempt to write the equivalent single routine, which progresses through the same steps, in the same order as when the above coding is run by starting Sub 1

Sub Sub1Sub2()
Dim StrtCpyNo As Long
 Let StrtCpyNo = 1
 
  
'Call Sub2( CpyNo:=StrtCpyNo,                    Msg:="Initial Message")
' Sub 2
Dim CpyNo As Long: Let CpyNo = StrtCpyNo: Dim Msg As String: Let Msg = "Initial Message"
Dim CopyNo As Long
 Let CopyNo = CpyNo
 MsgBox Prompt:="You are Starting Sub2 , copy  " & CopyNo
   
 MsgBox Prompt:="You are Ending Sub2 , copy  " & CopyNo
' End Sub2
 
 MsgBox Prompt:="Ending main procedure"
End Sub

Some things to note, before moving onto recursion things.. :
_ It is very easy to loose track of where you are when procedures Call others. It is particularly difficult to keep track when you are stepping through coding experiencing recursion processes. The reason for this is the following: As mentioned, the written coding is just copies of instructions. If you are doing any sort of debugging, such as the step F8 mode, then Microsoft has decided to indicate for you where you are in the copies shown in the VB Editor. That is not too bad for the coding shown so far. The problem comes in a recursion process, where, in this case, our Sub2 “Calls itself”.
What would be sensible for Microsoft to do in that case would be to show you a new copy of Sub2 , and step you through that second copy. Unfortunately that does not occur. Instead it shows you going through the same copy of Sub2
Therefore as you go through such a recursion process you will never know in which copy you are running
_ Following on from the last point: I don’t think I have ever experienced using recursion routines where it has not been useful to have an integer variable which keeps track of what copy I am in. I would personally recommend always to have such a variable
In the coding shown so far, this is my variables CopyNo and CpyNo
So far they have always been 1, as I have only been running 1 copy of Sub2
As we proceed, it should be clear how we can arrange that the variable always has a number equal to the copy number of the second routine, Sub2 , which is currently running