Trapping errors

Many programmers make the mistake of assuming that the user will always do what they are expected to do. However, this is a false assumption, for no matter what effort programmers make to provide explanations, users are still liable to carry out erroneous actions. The programmer therefore needs to anticipate user errors and handle them appropriately. The On Error GoTo Label statement provides one such way of doing this. It tells VBA what to do when a run-time error occurs. When you use an On Error Goto statement the normal program statements are completed with an Exit Sub statement before the error handling routine b egins. On completion of the error handling routine, the program is then terminated as normal with the End Sub command. An example of the general syntax for using this in a VBA program is given below:

Sub progName()

On Error GoTo ErrorHandler

Exit Sub ErrorHandler:

End Sub

To see how these statements could b e used, consider the following examples.

This first takes the VBA macro that we developed in Chapter 3 which sets up the monthly changes. That is, it copies end of month sales to sales to date, then clears the Weekly sales ready for the next month's incoming sales, and finally increments the month number for the following month's data.

Listing 8.3

Sub updateSalesErrorhandling()

On Error GoTo SheetMissingHandler Worksheets("Weeklysal").Select ActiveSheet.Unprotect Range("end_month_sales").Copy Range("sales_to_date").PasteSpecial xlValues Range("Week_Sales").ClearContents Range("Month_No") = Range("Month_No") + 1 If (Range("Month_No").Value > 12) Then

MsgBox "Please Start A New Annual Sheet" End If

ActiveSheet.Protect Exit Sub

SheetMissingHandler: 'error handling routine - sheet does not exist MsgBox prompt:="Entry Error- the weeklysal sheet does not exist" End Sub

Figure 8.10 Error dialog box from Listing 8.3

Figure 8.10 Error dialog box from Listing 8.3

The second example uses the On Error Goto statement to trap errors. The macro transfers to the QuotientErrHandler label if an attempt is made to divide by zero. On passing control to this section, the program will then display a MsgBox alerting the user to the fact that y ou cannot divide by zero. The code will input two integer v ariables called first and second. It will then divide first by second and output the result. However, if the user attempts to divide by zero, a run-time error will occur, which is the purpose of using the error trapping routine. The procedure is called calQuotient and is displayed in Listing 8.4.

Listing 8.4 Division by zero error trapping routine

Sub calQuotient()


On Error GoTo quotientErrHandler

Dim first_number As Integer

Dim second_number As Integer

Dim quotient As Integer

Dim Result As Range

first_number = InputBox(prc>mpt:="enter first number")

second_number = InputBox(prompt:="

enter second number")

quotient = first number / second number

Columns("B:B").ColumnWidth = 18

Range("B1").Font.Bold = True

Range("B1").Value = "Division Program"


Range("B3").Value = "First Number ="

Range("B4").Value = "Second Number ="

Range("B5").Value = "Quotient ="


Range("C3").Value = first_number

Range("C4").Value = second number

Set Result = Range("C5")

Result.Value = quotient

Result.Font.Bold = True

Result.Borders(xlBottom).Weight = xlMedium

Result.Borders(xlTop).Weight = xlMedium

Exit Sub


MsgBox prompt:="Entry Error- you cannot divide by 0!"

'division by zero error

End Sub

Figure 8.11 The run-time error message following an attempt to divide by 0

Microsoft Excel


enter second number

QK Cancel


Figure 8.12 InputBox receiving second integer

This example procedure examines the weeklysales worksheet in the workbook SALESMAN.XLS and checks each of the cells using the UsedRange property to see if it contains a formula. If it does have a formula, then the contents of the cell will be emboldened, otherwise it will be left alone. The error trapping routine in this procedure is used to capture run-time errors, which in this procedure could only arise from the worksheet named weeklySales not existing. Thus, on entry to the error handling routine, see

Listing 8.5

Sub boldFormulaCells() On Error GoTo errorHandler Dim cell As Range Worksheets("weeklysales").Select Activesheet.UnProtect For Each cell In ActiveSheet.UsedRange If cell.HasFormula Then cell.Font.bold = true End If Next Exit Sub errorHandler:

MsgBox "Sheet does not Exist" End Sub

Listing 8.5, a message box containing "Sheet does not exist" is displayed. Figure 8.13 displays the output screenshot when no error is encountered. Figure 8.14 displays the message on entry to the error handling routine.

Figure 8.13 Listing 8.4 output when worksheet exists

Figure 8.14 MsgBox on entry to error handling routine

Figure 8.14 MsgBox on entry to error handling routine

0 0

Post a comment