Visual Basic for Applications/Errors

This lesson introduces debugging and error handling.

Objectives and Skills

Objectives and skills for debugging and error handling include:

• Using debugging tools to suspend and step through code
• Using debugging tools to examine code while it is running
• Incorporating error-handling code to respond to run-time errors

Readings

1. Wikipedia: Debugging
2. Wikipedia: Error handling
3. Microsoft: On Error Statement

Examples

'This macro demonstrates error handling.

Option Explicit

Sub ErrorHandling()
    Const Title = "Error Handling"
    Dim Result As Single
  
    On Error GoTo ErrorHandler
    
    Result = 1 / 0
    MsgBox "Result = " & Result, vbOKOnly + vbInformation, Title
Exit Sub

ErrorHandler:
    Select Case MsgBox(Err.Description, vbAbortRetryIgnore + vbExclamation, Err.Source & " Error " & Err.Number)
        Case vbAbort
            End
        Case vbRetry
            Resume
        Case vbIgnore
            Resume Next
    End Select
End Sub

Activities

In these activities you will create macros that use input validation and error handling to catch and respond to runtime errors. Your macros should include Option Explicit, Dim, InputBox, MsgBox, titles on the dialog boxes, an icon on the message box, and use appropriate data types for your variables.

1. Age Calculations
   1. Create a macro that asks the user how old they are in years. Then ask the user if they would like to know how old they are in months, days, hours, or seconds. Use an If/ElseIf/Else or Select Case statement to display their approximate age in the selected time frame.
   2. Use the IsNumeric() function to validate user input for the numeric entry.
   3. Use Else or Case Else to validate user input for the selected time frame.
   4. Add error handling to handle any runtime errors that could occur in the macro.
2. Temperature Conversion
   1. Create a macro that asks the user if they would like to convert Fahrenheit to Celsius or Celsius to Fahrenheit. Use an If/ElseIf/Else or Select Case statement to determine their selection and then gather the appropriate input and calculate and display the converted temperature.
   2. Use Else or Case Else to validate user input for the selected conversion type.
   3. Use the IsNumeric() function to validate user input for the numeric entry.
   4. Add error handling to handle any runtime errors that could occur in the macro.
3. Area Calculations
   1. Create a macro that asks the user what shape they would like to calculate the area for. Use an If/ElseIf/Else or Select Case statement to determine their selection and then gather the appropriate input and calculate and display the area of the shape.
   2. Use Else or Case Else to validate user input for the selected shape type.
   3. Use the IsNumeric() function to validate user input for any numeric entries.
   4. Add error handling to handle any runtime errors that could occur in the macro.
4. Average Calculation
   1. Create a macro that asks the user to enter grade scores. Start by asking the user how many scores they would like to enter. Then use a loop to request each score and add it to a total. Finally, calculate and display the average for the entered scores.
   2. Use the IsNumeric() function to validate user input for each numeric entry.
   3. Add error handling to handle any runtime errors that could occur during the calculations.

References

Type classification: this is a lesson resource.

← Arrays

Visual Basic for Applications

Procedures →