|Let's Get Recursive|
|Download a copy of the code that accompanies this article.|
|While some would probably debate the "advancedness" of this week's topic, I certainly think it's one worth covering because it's a tool that should be in every VBers toolbox. Plus, you never know when you might be able to impress someone at a party with a line like "Sorry I was late. I was just putting the finishing touches on a recursive function at work." Sounds good eh? You can probably tell I don't go to many parties.|
I think recursive functions are a very elegant little gem. They typically get a lot done in a very small amount of code. The way I see it, the number of bugs in a program are in direct proportion to the number of lines of code so typically, the fewer the lines, the better. There is a potential problem, however, with recursive functions that I should probably go ahead and point out. A recursive function is probably more likely to generate a stack overflow than other types of subroutines or functions. This is because each time the routine calls itself. The parameters being passed are pushed onto the stack where the called routine (itself in this case) can pull/pop them from to work with them. As you will see after stepping through this code, a recursive function continues pushing parameters on the stack until it eventually gets to the end of its task and begins popping them off. If the number of calls to itself is high or the number/size of parameters is high (or both), there's a real chance of blowing the stack and generating a run-time error 28, out of stack space.
Keeping that in mind, let's look at an example of a recursive function. We've probably all used the function InStr to determine whether or not there is an occurrence of one string within another. Something like
If InStr(sInputVal, "|") Then
' we know there's at least one | in sInputVal
But what if you need to know not just whether or not there is a pipe in the variable, but how many total pipes there are? Granted, there are probably many ways to get the answer to this question, but let's examine one and learn about recursive functions in the process.
Simply stated, a recursive function (or subroutine) is one that calls itself with a value it typically generates itself over and over again until the ultimate result is achieved. I think they're a great example of the old saying "good things come in small packages". I wish I could impress you with all the code it took to write this function, but instead I hope you'll be impressed by how little it took. Here it is:
Private Function InStrCount(SearchString As String, FindString As String) As Integer
Dim lPos As Long
lPos = InStr(SearchString, FindString)
If lPos Then _
InStrCount = InStrCount + InStrCount(Mid$(SearchString, lPos + Len(FindString)), FindString) + 1
To start off, we pass the function the string we're looking for and the string we're looking in. Let's assume SearchString is "asdadsadsadsadsf" (without the quotes) and FindString is "ad". On the first call, the result of the InStr is 4. Since 4 evaluates to True on the next line, InStrCount is called again, looking only in the portion of the original SearchString that occurs AFTER the first occurrence of FindString. Each time this operation repeats, the original value of InStrCount is incremented by one. When lPos is 0, it means there are no instances of FindString remaining in SearchString. Eventually, the code works its way back out to the original instance of InStrCount you called and the total count is returned.
I wish I could say there was more to it than that, but that's the beauty of recursive functions. They typically accomplish a lot in a very small, elegant package. Some of the more common uses of recursive functions I've seen are to search for files on a hard disk or load folders into a TreeView control. Just be mindful of the amount of stack space you're taking up, and you can accomplish some pretty cool things with these compact little gems.