Recursion Procedures in Excel VBA. Recursion technique in coding

[DocAElstein]

All Sub Folder and File List from VBA Recursion routine. Explanation review of simplified routines.

Main Initial starting routine

Private Sub EFDoStuffInFoldersInFolderRecursion()
                                                                                                               Cells.Clear:                                                                                                  Range("C1:F1").Value = Array("Copy1", "Copy2", "Copy3", "Copy4")
Dim myFolder
    With CreateObject("Scripting.FileSystemObject")
     Set myFolder = .GetFolder("C:\Users\Elston\EileensFldr\") ' ----- change to suit where you have the sample Folder, or Folder that you wish to use.
    End With

Dim CopyNumber As Long:  CopyNumber = 1
Range("A1").Value = myFolder.Name: 'Columns("A:C").AutoFit
Call EFLoopThroughEachFolderAndItsFile(myFolder, 1, CopyNumber)
Columns("A:H").AutoFit
End Sub

Called recursion routine

' Called Recursion routine:
Private Sub EFLoopThroughEachFolderAndItsFile(ByVal fldFldr As Object, ByRef rCnt As Long, ByVal CopyNumberFroNxtLvl As Long)
Dim CopyNumber As Long: Let CopyNumber = CopyNumberFroNxtLvl:                                                  Debug.Print fldFldr.Name & "   " & CopyNumber
                                                                                                                                                                 'If CopyNumber = 0 Then CopyNumber = CopyNumberFroNxtLvl
Dim myFldrs As Object
    For Each myFldrs In fldFldr.SubFolders '==========================================
    ''''''''Doing stuff for each Folder
    rCnt = rCnt + 2:                                                                                           Debug.Print myFldrs.Name & "   " & CopyNumber
    Range("A1").Cells(rCnt, 1).Value = myFldrs.Path: Range("A1").Cells(rCnt, CopyNumber).Offset(0, 2).Value = myFldrs.Name:
    ''''''''End doing stuff for each Folder
    Dim oFile As Object
        For Each oFile In myFldrs.Files ' --------------------------------
        ''''''''Doing Stuff for Each File
         rCnt = rCnt + 1
        Range("A1").Cells(rCnt, CopyNumber).Offset(0, 2).Value = oFile.Name
        ''''''''End Doing Sttuff for Each File
        Next '------------------------------------------------------------
    Call EFLoopThroughEachFolderAndItsFile(myFldrs, rCnt, CopyNumber + 1)
    Next '=============================================================================
 'MsgBox prompt:="Ending Copy  " & CopyNumber
End Sub

Recursion techniques are well suited to situations like these. In simple terms what does situations like these mean?: We want to do to the bits of something, and that something may have similar bits of something that we want to do things to… Like we want to do something to everyone's children. Which will then mean the children, the children's children, the children's children's children, the children's children's children's children, … etc.
In our example we want to do something to the Sub Folders, the Sub Folders's Sub Folders, the Sub Folders's Sub Folders's Sub Folders, the Sub Folders's Sub Folders's Sub Folders's Sub Folders, … etc
We have no idea how many Sub Folders or children are at each level.
It would not be impossible to write a simple single routine to do all we want.
But a recursion technique is very efficient, at least in the amount of code writing necessary.
The strategy is to do something For Each of the Sub Folders in the initial given main Folder, the last thing being to Call the recursion routine again so as to do something For Each of the Sub Folders in the current Subfolder
Each Call of the recursion routine we go down a level . As any Copy or a recursion routine Ends , that causes us to "come back up a level ". Typically this "come back up a level " is what goes unnoticed, as rarely we have anything there. ( For demo purposes we have sometimes shown a Message box there, 'MsgBox prompt:="Ending Copy " & CopyNumber)

Summarised Coding Progression
Basic strategy
The recursion routine takes as the main argument, at the signature line, a Folder. This will be initially given as your main chosen Folder. In addition we take the variable for the copy number of the routine, initially given as 1 and subsequently given as the value of that variable in the current CopyNumber+1 when the current copy Calls the recursion routine
In the simple example of printing out an explorer type view, the use of the CopyNumber variable allows us to determine the horizontal position across.
One other variable is passed , rCnt. This is always incremented anytime we do anything. This is used to allow us to add things neatly positioned down the spreadsheet. This variable is always taken As Referred to. Effectively we can then consider that variable as being passed as a variable, so that variable is always added to, so it will always be the same variable in any copy of the recursion routine, unlike the CopyNumber variable which will be unique for each copy of the recursion routine
So we have a continual going down the spreadsheet output as we do things, and we go back and forth as we go up and down "level" in a typical Folder explorer type view. ( We only go back and forth once with the sample data Folder, because it proved difficult to upload more complicated Folders. If you run the code selecting a more typical Folder, then this back and forth should be more easily visible )
Walkthrough
Calling routine , Sub EFDoStuffInFoldersInFolderRecursion()
The chosen Folder is obtained, and the Name pasted out. (It is often the case that the actions that will be repeatedly done with the recursion routine will be done the first time in then main Calling routine)
The Folder, As a Folder object is passed to the recursion routine along with an initial value of 1 for the "row" , (rCnt), and "column" , (CopyNumber) , coordinates, that is to say variables for spreadsheet progression vertically down and horizontally back and forth

Sub EFLoopThroughEachFolderAndItsFile(ByVal fldFldr As Object, ByRef rCnt As Long, ByVal CopyNumberFroNxtLvl As Long)
The unique variable for each Copy Number of the routine is given the passed number, initially 1.
__Immediately after we have the main For Each loop which will do for every subfolder. '===============
The rCnt is incremented by 2. One is required to ensure all outputs are pasted on a new line, and the extra is done at this point to give an extra space between Folders
The Folder full path and name and name is then pasted out.
____A second Inner loop, ------- , then pastes out the File name for all files in the current Folder.
After the last File name is pasted out , and just before the end of the main loop, the routine is Called again, this time "by itself". Important to remember always is that this does not mean that the current routine starts gain: It means the current routine pauses: It stops: "It freezes". Whilst it is "frozen" a new independent copy is started.

Note: Every Folder is entered into the signature line of a starting copy of the recursion routine.
But, if it has no Sub Folders in it then nothing is done with that Folder when it is passed as a Folder, and the routine Ends. If for example, the given main Folder has no Sub Folders in it, then the Called routine ends at the first copy, and nothing is done. ( if it was required to look for Files in the main Folder, then that would have to be done in the first routine)
The recursion routine will keep "going down" subfolders until there are no more, so in effect every Sub Folder will eventually go through a
Being passed to a new copy, without doing anything. When each of the Sub Folders in a Folder is gone through, then the main Loop ends causing the recursion routine to end.
So the recursion routine can end either within the main loop or by bypassing that loop.
The end by bypassing is an "empty" run which prevents further "tunnelling down".
The end from within the Loop , that is to say at the Next'=== , point is when all Sub Folders in a Folder have been gone through.


_.________________________________________________ ________


The next posts do another more detailed analysis, and use a larger full 'commented coding. The 'commented lines also show possible other code lines for doing things other than just pasting File or Folder names out.
Intentionally, some things will be repeated.