In the Real World

The factorial function can also be written as a recursive procedure. A recursive procedure is one that calls itself.

Public Function Factorial(N As Integer) As Integer If N <= 1 Then Factorial = 1


Factorial = Factorial(N - 1) * N End If End Function

Although the factorial example above is a nice illustration of recursion, it is not a practical example. Recursive procedures can be very demanding on system resources and they must contain logic that will eventually stop the procedure from calling itself.

Recursive procedures are most often and most effectively applied to tree-like data structures such as the file system on a computer.

The For/Next loop is a natural choice, because you need the looping variable to increment by one with each iteration until it reaches the value of the integer passed into the function. Each iteration through the For/Next loop multiplies the next factor by the previous result, effectively producing the factorial of the value stored in the variable myValue. For example, if myValue is 5 then the variable factorialValue will be calculated as 1*2*3*4*5.

Finally, consider the most obvious example of looping in spreadsheet applications, which is looping through a range of cells in a worksheet. For now, I will illustrate looping through a worksheet range using a For/Next loop.

The Looping structures discussed so far are not the best choice for Looping through a range of ceLLs—even though doing so is a simple enough task. A better Looping structure for handling this task is the For/Each Loop discussed in Chapter 5, "Basic ExceL Objects."

The example above uses one For/Next loop nested inside another For/Next loop to loop through the worksheet range D1:G10. The nested (inside) loop will execute 4 iterations with each iteration of the outer loop. In the example just given, the value of J iterates from 4 through 7 for each value of I. The code loops through the range by rows, as the variable used for the row index (I) is also the counting variable for the outer loop. The Chr() function is used to convert a numerical input representing an ASCII (American Standard Code for Information Interchange) value to its corresponding keyboard character; in this case the values 68 through 71 will be converted to the uppercase letters D through G. The Chr() function in VBA works with values 0-255. Table 4.2 lists a few of the more common characters in the set. Alternatively, you could replace the Chr() function with the looping variable J; which, in this case, would make for easier and cleaner code; however, I wanted to introduce the Chr() function since it can be quite useful when working with the Cells and Range properties.

Table 4.2 Selected ASCII Conversion Characters r

Table 4.2 Selected ASCII Conversion Characters


Keyboard Character






Line feed


carriage return









Was this article helpful?

0 0
Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment