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
        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:  04/06/2017 20:32
This page added:  29 August 1998