Recurse Me

Q: What is recursion, and how can it be used?

Recursion occurs when a subroutine or function "calls itself". This is most useful for situations where the same code must be applied multiple times to a number of different data objects.

The classic example is to solve the problem y=n! (read y equals n factorial). The solution is to mutliply n first by (n-1), then multiply the result by (n-2) and so on until only "1" is left as a factor. One can readily see that the first factor is to be multiplied by (n-1)!  This can be accomplished with recursive code outlined below:

Sub TryNfactorial()
n = 3
y = Nfactorial(n)
MsgBox (y)
End Sub

Function Nfactorial(n)
If n > 1 Then Nfactorial = n * Nfactorial(n - 1) Else Nfactorial = 1 ' while n > 1 - keep going deeper
End Function

A powerful additional use of recursion is to create a recursive VBA function which deals with stored files in multiple folders in a folder hierarchy. For example - say we wanted to add a plot to all .xls files which had "DataToPlot" in the filename. Below is an outline of this code.

1) Use MsofilePicker to ask the user for the first node of the structure of interest
2) Use the filescripting object to create the folder, sub-folder and files objects for the current node
3) If there are more folders at this node - call the same (now recursive) routine again to shift the node to a new (deeper) location
4) When there are no more folders (only files) - process the files - check if they are .xls and have "DataToPlot" in the name (with the "Like" statement) - if so open, create a plot, and save (and return)

We have applied this approach to several different Windows-based file modifications successfully. See our website for more information:

http://www.TechnicalAutomationServices.com/