Procedural Programming

Procedural programming is a programming paradigm in which a program is constructed of small procedures that are linked together to perform a given task. One school of thought regarding procedural programming is that procedures have one and only one exit point. In Listing 6.1, the use of the Exit statement in the event that an array is not returned would violate this guideline.

The alternative is to embed nearly the entire remaining block of statements inside a giant If.. .Then statement. I used to follow this practice so that my procedures would adhere to the one and only one exit point guideline. However, it seems to me that it is more difficult to follow and maintain procedures that use many nested If.. .Then statements than it is to check for a terminating condition and use an Exit statement if a terminating condition is found. For example, Listing 6.1 could be rewritten to follow the one and only one exit point guideline as shown here (many lines omitted for brevity):

Sub ProcessFileBatch()

1 Make sure the dialog wasn't cancelled - in which case 1 vFiles would equal False and therefore wouldn't be an array. If Not IsArray(vFiles) Then

Debug.Print "No files selected."


Application.ScreenUpdating = False

' OK - loop through the filenames For nIndex = 1 To UBound(vFiles)

Next nIndex End If


Application.StatusBar = False Application.ScreenUpdating = True End Sub

Omitting lines for brevity's sake doesn't help illustrate the main problem created by long nested If.. .Then statements or Loops—they are much harder to read. This is especially true when you need to check for multiple terminating conditions, because this creates deeply nested structures that are nearly impossible to read.

Prior to analyzing this procedure, I've a couple of thoughts I'd like to share. First, before you can use this procedure, you'll need to add a few more procedures you haven't seen yet. I'll get to them after we analyze this listing. Second, this procedure is a good example of a long procedure. I generally don't like to see procedures of this length or longer. Usually such procedures can be factored, or broken into smaller, discrete procedures that work together. The benefit of using smaller procedures is that they are usually easier to understand and therefore easier to debug and maintain. Further, smaller procedures generally offer greater potential for reuse. In this case, the procedure is reasonably factored and logically laid out to the point that I am comfortable with it.

OK, so let's take a look at this procedure. First, after you declare the variables, notice the On Error statement. This statement directs program execution to the ErrHandler label near the end of the procedure in the event of an error. Any time you are opening or saving files, the probability that an error will occur increases, so it is a good idea to use some sort of error handling. Because you'll be using the status bar and turning screen updating off, you need to be certain that these properties will be reset to their original settings no matter what. You need to use error handling here, if for no other reason than to guarantee that these properties get reset. You may add additional error-handling code here as your needs dictate.

After error handling is turned on, this procedure calls the GetExcelFiles function that was listed in Chapter 5 (Listing 5.6) to obtain a batch of files from the user. Next, you need to check the result of GetExcelFiles to make sure it's an array. If it isn't, the user didn't select any files. If files weren't selected, there is no point in continuing the procedure, so you use the Exit statement to end the routine.

The next order of business is to construct a loop that will loop through every filename returned from the GetExcelFiles function. As we discussed in the last chapter, the GetOpenFilename method used within the GetExcelFiles function returns a one-based array rather than a zero-based array, which is the conventional way to work with arrays.

Inside the loop, you need to assign the workbook referred to by the filename to a workbook variable (named wb). In order to do this properly, you need to take into account the possibility that the workbook is already open. I've created a function called IsWorkbookOpen that checks to see if a given workbook is open or not. We will take a look at that after I finish this analysis. If the workbook is open, you just need to set a reference to the open workbook (with help from the GetShortName procedure from Listing 5.8); otherwise you need to use the Open method of the Workbooks object.

In order to leave the environment as it was found, the procedure remembers whether or not each workbook was open. That way, after you finish doing work on the workbook, you can close it if it was closed or leave it open if it was originally open.

At this point in the procedure, you have a reference to the desired workbook and could do any processing on the workbook that you desired. Ideally, you'd create a specialized procedure to perform the processing that takes a workbook parameter as input. For example, you could create a procedure such as this:

Sub ProcessWorkbook(wb As Workbook) ' do some work on the ' wb here End Sub

As you loop through the workbooks, you could simply call the ProcessWorkbook routine such as

' Code to process the file goes here ProcessWorkbook wb

After doing any desired processing on the workbook, you need to save any changes and close the workbook if it was originally closed. Then move on to the next workbook.

Finally, after you've looped through all of the workbooks that the user selected, you come to the last couple of lines that reset the status bar and turn on screen updating. The ErrHandler label doesn't have any effect on program execution other than to provide a bookmark, so to speak, that instructs the computer where to go in the event of a run-time error.

0 0


  • Pervinca
    Is VBA procedural programming?
    8 years ago

Post a comment