What this macro does:

Imagine that you have a folder on your computer that you created. Imagine that you use this folder very often, and that over time the contents of the folder have become quite complex. Your folder now contains many subfolders at many different "depths" or layers, and each subfolder contains anywhere from 0 to 100 files or more.

How can you quickly and accurately survey the entire contents of the folder, including all subfolders? For starters, you could right-click the folder and choose "Properties," where you will then see information including the quantity of files and subfolders in your folder. Unfortunately, this will not give you any information about what types of files lie within. Likewise, you will not discover the names of the many files.

Well, thank goodness for this handy macro! After running this macro, you will have a list of all the files in your chosen folder, along with the specific file address or precise location of each folder.

For example, if you have a folder called "My Stuff," with contents similar to those shown in the following picture:

...then you would change one line of the macro to obtain a listing of the entire contents of "My Stuff." You would change the following line (line #11 of code, but actually only the eighth line containing text)

so that the line would read:

Then run the macro. Assuming that the structure of "My Stuff" corresponds to the picture above (the picture showing subfolders such as "Music"), then the output from the macro would look something like this:

important_numbers.txt   in   C:\My Stuff\
reminders.txt   in   C:\My Stuff\
dad.jpg   in   C:\My Stuff\Some Pictures\
mom.jpg   in   C:\My Stuff\Some Pictures\
bach.wav   in   C:\My Stuff\Music\Classical\
debussy.wav   in   C:\My Stuff\Music\Classical\
elvis_presley.wav   in   C:\My Stuff\Music\Rock\

Some useful notes:

A note about the output/results: when the macro is finished, you may have anywhere from 1 to 50 pages of results, or possibly even more. This will vary according to your chosen value for the StartFolder variable, and according to the contents of the folder specified by that variable. You may find that your results will be easier to read if you highlight (select) all the results, and change the font size to 10 point or 8 point instead of the default (the default is usually 12).

A note about subroutines: you may have noticed that the macro code on this page actually consists of three subroutines. Their names are Sub CallEm, Sub GenerateListing, and Sub Strike. "CallEm" is the main procedure which will call upon the other subroutines several times during its execution. ("CallEm," obviously, is a play on words with "call them," which in casual speech is pronounced "call em.") Therefore, CallEm is actually the only macro you will run. You, the user, will run CallEm, and from then on, the macro code itself will be in charge of automatically causing the other subs to run.

A note about file attributes: currently, this macro is not written to handle hidden folders. In other words, if the folder you wish to search contains any hidden folders, then the hidden folders will not turn up in the results. If you wish to change the macro to include hidden folders, then you will need to change the arguments passed to the "Dir" function anywhere the Dir function is called. The file attribute constants in VBA (Visual Basic for Applications) are: vbNormal, vbReadOnly, vbHidden, vbSystem, vbVolume, vbDirectory, and vbArchive.

A note about errors: if Microsoft Visual Basic displays a run-time error that gives you the option to either "End" or to "Debug," then you may wish to choose "Debug," and investigate the values of WhereAt, Result, and FollowTheFolders(0). You may find that a certain file or folder is causing problems due to strange properties or characteristics. For example, I encountered a run-time error when the macro arrived at a file named "??.??" (or something like that) in "My Documents." I have no idea what that file is or how it got into "My Documents," so I just deleted it, and then re-ran my macro. Once "??.??" was gone, the macro ran beautifully.

A note about passing variables as arguments: if you are still learning to program macros using Microsoft VBA (Visual Basic for Applications), then take a look at how the FollowTheFolders array gets passed to the Strike subroutine. Within the Strike subroutine, the array is referred to or accessed under the "alias" of IncomingArray. However, despite this "alias," the original FollowTheFolders array is actually being altered every time IncomingArray is altered!

A note about GetAttr and the bit-wise operator "And:" at one point in this macro, I use the GetAttr function to return a binary value indicating the file attributes for a file (or folder). This binary value could look something like "00000111" for example. After I call this function to get the binary value, I compare it to the value of vbDirectory to find out whether the file (or folder) is a directory. (If it is a directory, then I will know it is a folder rather than a file.) Now, why would (00000111 And vbDirectory) tell me what I want to know? Here's how: the "And" is a binary operator that specifies the following type of combination:

    00010000
And 00000111
____________

This is different from "normal" addition. If a 0 is above a 0, then we place a 0 in that column in the result. If a 0 is above a 1, or a 1 above a 0, then we still place a 0 in that column in the result. The only way for a 1 to appear in the result is if a 1 is above a 1. So in this case, the result is:

    00010000
And 00000111
____________
 
    00000000

If the result is 00000000, then the boolean value of the result equals False. Now, to connect this concept back to the idea of the vbDirectory attribute. As it turns out, vbDirectory is actually a constant that represents the binary value "00010000." So, if we do a "binary And" operation with vbDirectory and any other binary value, then the only way we will ever avoid a "False" result (00000000) is if the value for comparison has a 1 in the same column that vbDirectory has a 1. For example:

    00010000 vbDirectory
And 11101111
____________
 
    00000000 False

 
    00010000 vbDirectory
And 10010001
____________
 
    00010000 True

This is why the statement If CBool(GetAttr(WhereAt & Result) And vbDirectory) = True is a valid way of determining whether a file (the file found at the path equal to "WhereAt & Result") bears the vbDirectory attribute.

Thanks for reading about my macro!
-Kelly