Automating Microsoft Access with VBA

 < Day Day Up > 

Using GoTo

There's one final flow-of-control statement that you should know about: the GoTo statement. GoTo produces an unconditional change in the flow of your program. Here's an example of the GoTo statement in action:

Sub CalculateBill(curRate As Currency, intHours As Integer) ' Calculate the bill for this line item If intHours > 100 Then GoTo ExitHere End If Debug.Print curRate * intHours ExitHere: End Sub

In this code, ExitHere is an example of a label. A label is not executed by VBA. Rather, it is a bookmark within the code that VBA can refer to by name.

The GoTo statement transfers the program flow to the specified label. So in this particular procedure, the effect is to exit from the entire procedure if the intHours variable has a value of more than 100.

The GoTo statement has been a subject of debate for many years. Most professional developers agree that using GoTo can make your code harder to read and understand, because you have to jump around to follow the code when you're reading it. And in most cases, you can rewrite code to avoid the GoTo statement. For example, here's another version of the CalculateBill procedure:

Sub CalculateBill(curRate As Currency, intHours As Integer) ' Calculate the bill for this line item If intHours <= 100 Then Debug.Print curRate * intHours End If End Sub

This version has exactly the same effect as the first version, but it does not use the GoTo statement.

On the whole, I agree with those who feel that the GoTo statement should be avoided. You might occasionally find that using GoTo makes your code seem more clear, and in those cases, there's nothing wrong with using GoTo. For example, if there are many places within a complex procedure where you want to execute cleanup code and then terminate the procedure, you might use GoTo statements to avoid having to repeat the cleanup code. But don't leap to use GoTo as your first tool in all cases.

The GoTo statement is necessary as a part of the On Error GoTo error-trapping statement, and you shouldnt avoid using that statement just because it includes a GoTo. See "Using On Error GoTo," on p. 53.

CASE STUDY: Billing for Work in Progress

One thing that consultants tend to be concerned with is the amount of revenue that they're generating. Often, one consultant is juggling multiple projects, working a few hours per day on each one. In these cases, it's useful to be able to generate a chart showing the amount to be billed for, say, three hours a day across four days at a particular rate.

To generate such a chart, we've developed the procedure named PrintBillingChart. This procedure puts together several of the flow-of-control statements that you've seen in this chapter. Here's the text of the procedure:

Sub PrintBillingChart(curBaseRate As Currency, _ intMaxDays As Integer) ' Show hourly billings for up to the ' specified number of days Dim intDays As Integer Dim intHours As Integer If intMaxDays > 6 Then Debug.Print "This procedure is limited to 6 days" Else ' Create the chart title Debug.Print "Billing Chart for " & _ Format(curBaseRate, "Currency") ' Create the chart header Debug.Print vbTab; intDays = 0 Do Until intDays = intMaxDays intDays = intDays + 1 Debug.Print CStr(intDays) & " days" & vbTab; Loop Debug.Print ' Create the chart body For intHours = 1 To 8 Debug.Print CStr(intHours) & vbTab; intDays = 0 Do Until intDays = intMaxDays intDays = intDays + 1 Debug.Print Format(intDays * intHours * curBaseRate, _ "Currency") & vbTab; Loop Debug.Print Next intHours End If End Sub

Before dissecting this code, let's look at the output. Here's what you see in the Immediate window if you execute PrintBillingChart 25, 4:

Billing Chart for $25.00 1 days 2 days 3 days 4 days 1 $25.00 $50.00 $75.00 $100.00 2 $50.00 $100.00 $150.00 $200.00 3 $75.00 $150.00 $225.00 $300.00 4 $100.00 $200.00 $300.00 $400.00 5 $125.00 $250.00 $375.00 $500.00 6 $150.00 $300.00 $450.00 $600.00 7 $175.00 $350.00 $525.00 $700.00 8 $200.00 $400.00 $600.00 $800.00

So, for example, if you bill three hours a day for three days at the specified rate of $25, the total bill is $225.

The procedure starts by declaring the variables that it will use (remember, it's a good idea to declare all variables in one spot so they're easy to find). Then it checks to make sure it's not being asked to print a chart that's too wide. The If…Else…End If structure provides a handy way to abort the procedure if the arguments are unreasonable.

Within this structure, there are three major pieces of code. Note that I've used comments to make it easier to see what's going on. Printing the chart title is easy; this just requires executing a single Debug.Print statement.

Printing the chart header uses one bit of code that you haven't seen before. Note that some of the Debug.Print statements end with a semicolon. This tells VBA to continue printing on the same line, rather than always returning to the next line. So to print the chart header, it prints each separate column header, and then executes a blank Debug.Print statement to return to the first column of the next line of the output. Also note the use of the vbTab constant. This is a built-in constant that represents the Tab character.

Printing the chart body nests a Do loop within a For…Next loop. Thus, the Do loop is executed eight times, once for each row of the chart. Within the Do loop, VBA does the work of formatting the amount to be billed, and then prints it.

Although this code is more complex than the procedures you've seen up to this point in the book, it makes use of the same building blocks. If you look at each block in turn, you can see how it all fits together. If you have any doubts, you can set a breakpoint and single step through the procedure.

     < Day Day Up > 
    Related

    Категории