All Sub Folder and File List from VBA Recursion routine. Explanation and Method Comparisons. Dec 2017.

Like a lot of us, I have answered a Thread request to do the above quite a few times. I need a routine myself now to help search my own stuff, and I am learning Python, and am doing a Python code alternative in parallel, so I thought It would do no harm to share here an explanation of the most commonly code version done in VBA, ( Recursion type code ) , and welcome any comments or alternatives… The code discussed here makes use of the FileSystemObject Object, to get access to a computers File system

Recursion Process , a quick alternative simple explanation.
I like to recap and explain this in simple terms as I think its complexity is over rated due to over complicated words and explanations…
Recursion is often described as ..”.. a routine that calls itself..”..
Before I sussed out what that was about, it sounded like a very confusing and clever thing. I find it very simple now, thinking about it in a different way. The following may not be a truly technically correct description of what is going on, but it helps me to understand it, (and I haven’t met anyone yet that fully understands what the internal workings are doing anyway in a recursion process anyway)..
So here you go:
Two things I find are good to bear in mind initially
_ (i) I would forget initially the ..”.. a routine that calls itself..”.. stuff. That can be misleading on my opinion. I think it is more helpful to say “…A routine that pauses, whilst another copy of the routine is done”….
_ (ii) Be careful if you use the Debug mode ( F8 when looking at the code in the VB developer environment ) to step through a recursion routine. That can be misleading as well.
( The output that my code example produces, if looked at whilst stepping through the code in Debug mode, can help make it a bit clearer what is going on. The key to both understanding and not getting confused when in Debug mode is that typically you are actually stepping through many separate copies of a routine. What is actually going on does not show up very well in Debug mode as it appears when stepping through a code in Debug mode as though the same routine is done many times. That is not the case. By following in the DeBug mode in the VB Editor it is easy to overlook that there will typically be many separate variables for each copy of a the routine which ,although they have the same name, are independent.

Any code is just a master set of instructions and a run follows copy of that instructions
Think of a code as just a written set of instructions and when a code “runs” a copy of the instructions are made and followed in order to do something. Usually the copy is trashed when the code stops, ( Ends ) , but there may sometimes be something left over, or changed or made at the End
Broadly speaking we can think about 3 main sort of code situations, and the third one is recursion
_1) A simple single code, a Sub( ) is a Master copy of a set of instructions. You make a copy of the instructions, follow them and do it. Then stop / End. To a first approximation, usually, but not always, everything is over with at the End and gone. Usually the ( ) is empty , - the ( ) is meaningless in such a code case.
_2) Codes where an initial simple code ( calling ) a Function (oftenSomethingHere) or another Sub( oftenSomethingHere) . In a simple example of this situation, it is a code , often referred to a the main (Sub ( ) ) or main routine, that “Calls” a Function( oftenSomethingHere)or another Sub( oftenSomethingHere) . In simple terms this second routine is just another master copy of a set of instructions. The main difference is that as there is “( oftenSomethingHere)” that means that something is “taken into it” from the Calling main routine.
So just to be clear: We have 2 routines: We have a main routine and a second Called routine. The second routine cannot usually be run on its own, - it needs another routine to Call it, as it usually is given something. ( VBA syntax does not usually let you set off such a routine on its own with “(SomethingInHere)”. **You have to give the “SomethingInHere” at the “Call” code line which “calls” or “sets off” a copy of the second routine instructions.
So typically in this second code situation you initially start doing 1) , a simple Sub ( ) , as before, but before you are finished, you pause what you are doing ( “freeze” the situation, not stop / End , so you may still have stuff temporarily stored or hanging around somewhere ). This pausing happens at the “Call” line. You then make a copy of the second routine instructions. Usually , but not always, you may take something with you from the Sub, ( ** in the “(SomethingInHere)” bit ) , then Follow that next set of ( second routine ) instructions. Usually that will involve doing something with what you took with you. You do and finish / End that. Often , but not always, you may take something back which you did or made in the second routine, and then use that when you then “unfreeze” and carry on the initial (Sub) set of instructions from the point where you left them, ( at the “Call” line ). Then stop / End. To a first approximation, usually, but not always, everything is over with at the End and everything is gone.
_3) Recursion situation: This is not really much different to 2). In fact it is almost the same. The situation is that you are in the second set of instructions ( second routine ). But before you are finished , you once again pause. You make a second copy of the second routine instruction. Once again, you freeze the situation of the fist copy of the second routine instruction, ( and remember, you have already previously frozen / paused the initial main Sub instructions. So a couple of code instruction copies are hanging around, frozen, on hold, and waiting to restart from where they paused).
So you now do again what you just started but you do it this second time completely first before you go back and finished doing it the first time… Sounds a bit pointless initially. But the point is that usually what you take with you and consequently what you bring back is different. So you do different things using the same set of instructions, and/ or what you end up will be different each time as it depends on what you take with you when you go through each copy of the ( second routine ) instructions . You can keep pausing the current copy of the second routine instructions and starting again with a new copy set of the second routine instructions, at least until something gets overfilled.
Some process/place in the computer has to keep a record / track of all the copies of the second routine instructions currently not finished along with any “frozen” values of variables locally held in those copies , which would have been also frozen at the values had at the pause, and which will be needed for when each copy restarts. That process/place is usually referred to as a stack. So when that gets overfilled you get the famous “Stack overflow” error. In the practice you would usually have some condition checked which needs to be satisfied before you start following a new copy of the second routine instructions. In the following example, that condition to be satisfied will be if there are Sub Folders in a current Folder that is being searched for Excel Files.


Recursion example

Purpose of the code:
The aim is to produce a Tree root sort of a sketch in a worksheet to show all the Folders, Sub Folders and files starting within an initial Folder. That is what I need, for example , to get finally a list of Titles to help me search for a particular subject area, to see if I have a code already to solve a particular problem . ( I don’t really need a worksheet full of the titles, but it looks nice and does demo very well the recursion process .. in particular the “up and down” nature of the process, which is very difficult to explain in words. It is also handy to have a worksheet saved showing pictorially all the files and where they are. It ends up looking like a classic Explorer Window)

General Code Form and strategy.
The code is a shortened version of this one Recursion version:
https://www.excelforum.com/excel-pro...ml#post4348630
various code variations in VBA and full code descriptions https://www.excelforum.com/developme...directory.html .
To simplify for demo purposes, assume a main Folder is known and so is hard coded in this example.
( I supply the demo Folder with my code example. You will need to download it and place it, unzipped , in the same Folder in which the Excel File is in which you place and run my code )

Following on from the idea explained in the first post.. it follows that the main Sub will pass over to a second routine ( or put another way the second routine will take (here) ) a single Folder which may have **Excel Files and / or Sub Folders. Those Sub Folders could themselves have Excel Files and/ or Sub Folders. Those Sub Folders could themselves have Excel Files and/ or Sub Folders. Those Sub Folders could themselves have Excel Files and/ or Sub Folders. Those Sub Folders could themselves have Excel Files and/ or Sub Folders. …etc.
So the second routine would usually “take in” just a Folder. It then does For all the Sub Folders in it the following:
_ Increments the row count by 1+1, then writes the folder name. ( 1 is for a new line for the next Folder name entry, and the extra 1 is so that an extra empty line is made for clarity before each Folder name. )
and
_ writes the name of any contained excel File File names before checking for Sub Folders, and if so it would, For Each of these Sub Folders´, “take” each folder in a run of a new copy of the second routine
Generally , in most seen examples of this code type, we do not need to take anything “out” at the End, as more usually the code is intended just to “do something” to Folders and/ or Files, or look in them for something.
However, in the case of this particular code example, some variables are passed as they keep track of where we are vertically and horizontally in the root structure which will be. The vertical is simply incremented by one every time the second routine is used. The Horizontal is adjusted appropriately to keep track as we “go up and down” ( shown pictorially as right and left )

In the many published examples of such codes, the main code will always be a simple Sub( )
The second routine would normally have a form something of this form: Function(FolderToLookIn) or Sub(FolderToLookIn)
The difference in these versions of the second routine is usually said to be that you are able to return something with the Function and not the Sub, as it would often be seen in this form,
Variable = Function(OneOrMoreThingsTakenInhere) , whereas the Sub would just be Called, like
Call _ TheSubName _ (FolderToLookIn)
That is Bollox actually!!!, but would make no difference anyway in the usual form as you do not want anything back, so just Calling either ( VBA allows you also to do that to the Function also ) in these forms sets them off, (which is all that would normally be done):
Call _ TheSubName _ (FolderToLookIn)
or
Call _ TheFunctionName _ (FolderToLookIn)

In my example I need to use the Sub(SomeThingsInHere) because in addition to “taking in” the FolderToLookIn , I want to also:
Give a cell as Top Left of where I want my output to start
and
pass a variable to be used By Referring to it ( giving it ) a count of the number of times a copy of the second routine instructions is run through. This will be used to increment the row number that each Folder name or Excel File name is written in. ( This By Ref method effectively “returns” a value !!!)
and
pass a Value to it ( giving it ) a number for the column number that is used for output. This effectively gives an indication of the Copy Number of the second routine copy being used. This is also an indication of how far “down” or “to the right” we are in the Folder system

The nice thing about the final output is that as you work down you can see what is going on, as different copies of the second routine start and pause or stop.
The next post works through the code in detail.
The over next post gives an example, using a supplied Main Folder which contains Folders and Files. I give a screenshot of what you should be able to reproduce , and on that screenshot have added some notes in orangeto show what is actually going on ( https://imgur.com/FjjUMYz )

( ** To simplify initially the comparison of this code with a Python program alternative, all Files are examined and printed out into the worksheet “explorer” output example, but there is a simple few lines ( ' ##### commented out ) which can be used to select Excel Files only. )