Events

1
Events

• A button is clicked
• An error occurs
• A worksheet is activated
• Data in a cell is edited
• A workbook is saved
• A cell is double-clicked
• A workbook is opened

2
Automatic Events

• Identify the event to trap
• Runs when the workbook opens
• Runs when the worksheet tab is clicked
• Choose the appropriate object
• ThisWorkbook
• Sheet1

• Select event in the Code Window
• Open
• Before Close
• Before Print

3
Other Events

• OnTime
• Causes a procedure to run at a given time
• Application.OnTime time, procedure
• Application.OnTime TimeValue(2100), GoHome
• Application.OnTime Now 1/24, DoLater
• OnKey
• Runs a procedure when a specific key is pressed
• Application.OnKey(Key, procedure)
• Application.OnKey g, UseGreek
• Application.OnKey "", Super"

4
Error Handling

• Identify Potential Errors
• Decide on Response
• Head them off before they occur?
• Ignore and recover?
• Warn user and quit?

5
Error Trapping

• Type Mismatches
• Case Mismatches
• Values that Cause Overflow
• Bad Input

6
Potential Error - Now What?

• Type Mismatch
• Use Format( ), Val( ), DateValue( )
• Case Mismatch
• Use Ucase( )
• Use Boolean?
• Bad Data
• No Data, Data Out of Range
• Use Do Loop, Ask for New Data
• Data Causes Divide Overflow
• Use If-Then to Bypass

7
Input Error Trap

• 1. Use InputBox to get data

2. Check data
3. If the data is bad - Warn the user he/she will
have to re-enter the data - Loop back to step 1
8
Input Error Trap

• myData InputBox(Enter Data)
• MsgPrompt Is this your data myData ?
• DataOK MsgBox(MsgPrompt, vbYesNo)
• Do While DataOK vbNo
• myData InputBox(Try again Enter Data)
• DataOK MsgBox(MsgPrompt, vbYesNo)
• Loop

9
Bad Data Trap

• Some data causes errors
• Divide by zero
• Range.Offset(i, j) moves off sheet
• Set If-Then-Else trap
• Example Surveying
• Latitude LengthCos(Azimuth)
• Departure LengthSin(Azimuth)
• When Azimuth 0 (North), VBA crashes

10
Bad Data Trap

• Set trap to catch Azimuth 0
• If vAz 0 Then
• vLat vLength
• vDep 0
• Else
• vLat vLengthCos(vAz)
• vDep vLengthSin(vAz)
• End If

11
Recovering from Errors

• On Error Resume Next
• Ignores step, moves on
• Use if error is not critical
• On Error GoTo
• Data Labels
• Ends with a colon
• Not read
• Warning Message
• Exit Sub

Sub MySub1() On Error Goto DoThis ltother code
linesgt Exit Sub DoThis MsgBox WarnMessage End Sub
12
Debugging

• Debug Toolbar
• Breakpoints
• Observed Values
• Watches
• Step Into
• Step Over

13
Debugging