Excel VBA

css navigation by Css3Menu.com

Trap Those Errors

This is the conventional way of trapping errors as your VBA code runs. This method always displays a dialog box with the error message. As I run my code and find out how to handle each error, I change the code to tell me what I should have done.

See also my example for skipping past errors and simply writing them to a log for later retrieval.

Module is already in progress
    On Error GoTo ErrHandler	
'Tell VBA to use your trapping routine
Module continues then
    Selection.Value = 123
    Exit Sub		'You must exit the sub or the 
			'Error Handler is invoked
			'everytime you come to the end of the macro
    
ErrHandler:
        Select Case Err	
'Err is already defined in Excel to hold the 
			'numeric code for errors
            Case 91
                MsgBox "There is no active cell"
            Case 1000
                MsgBox "Cannot assign a value to the selection."
            Case 1004
                MsgBox Err & " " & Error(Err) & Chr(13) & Chr(13) _
		& "The method you specified cannot be used on the object."
            Case 1005
                MsgBox "The worksheet is protected."
            Case Else
                MsgBox Error(Err) & err.number & err.description 
        End Select
End Sub


© 1998-2017

Updated:  12/10/2017 15:42
This page added:  29 August 1998