Error Handling in VBA with Custom Messages

[AQ_Awan]

What are some best practices for implementing error handling in VBA? I'd like to show custom error messages based on different types of errors that might occur in my code.

 

[Marc L]

A VBA help must see : On Error … Like its sample as well.​

​

 

[Logit]

ERROR NUMBERS & DESCRIPTIONS
Error #	Error Message	Description
3	Return without GoSub	Occurs when a Return statement is encountered outside of a subroutine that was called using GoSub.
5	Invalid procedure call	Occurs when a procedure is called with an invalid argument.
6	Overflow	Happens when a numeric expression exceeds the range of its data type.
7	Out of memory	Occurs when the system has insufficient memory to execute a procedure.
9	Subscript out of range	Happens when trying to access an array element that doesn't exist (index out of bounds).
10	Array is fixed or temporarily locked	Occurs when trying to modify an array that is fixed or locked.
11	Division by zero	Happens when attempting to divide by zero.
13	Type mismatch	Occurs when a value doesn't match the expected data type.
14	Out of string space	Happens when there isn't enough memory to perform a string operation.
16	Expression too complex	Occurs when an expression is too complex to be evaluated.
17	Can't perform requested operation	Occurs when attempting to perform an operation that is not allowed in the current context.
28	Out of stack space	Happens when the call stack exceeds its limit.
35	Sub, Function, or Property not defined	Occurs when trying to call a subroutine, function, or property that doesn't exist.
47	Too many DLL application clients	Occurs when the application tries to open more DLLs than it can handle.
48	Error in loading DLL	Happens when a DLL file that's required by the application is missing or corrupted.
49	Bad DLL calling convention	Occurs when a DLL function is called with the wrong calling convention.
51	Internal error	Indicates an internal error in VBA runtime.
52	Bad file name or number	Occurs when trying to perform file I/O operations with an invalid file name or number.
53	File not found	Happens when trying to access a file that doesn't exist.
54	Bad file mode	Occurs when trying to perform file I/O operations with an invalid file mode.
55	File already open	Happens when trying to open a file that's already open in another process.
57	Device I/O error	Indicates an error with the input/output (I/O) operation.
58	File already exists	Happens when trying to create a file that already exists.
59	Bad record length	Occurs when trying to read or write records with an invalid length.
61	Disk full	Happens when the disk is full and no more data can be written to it.
62	Input past end of file	Occurs when trying to read past the end of a file.
63	Bad record number	Happens when trying to read or write records at an invalid record number.
64	Bad file name	Occurs when trying to perform file I/O operations with an invalid file name.
67	Too many files	Indicates that the application has exceeded the maximum number of open files.
68	Device unavailable	Happens when the device requested for an I/O operation is not available.
70	Permission denied	Occurs when the application doesn't have the necessary permissions to perform an operation.
71	Disk not ready	Happens when trying to perform file I/O operations on a disk that is not ready.
74	Can't rename with different drive	Occurs when trying to rename a file with a different drive.
75	Path/File access error	Indicates an error when trying to access a file or path.
76	Path not found	Happens when trying to access a path that doesn't exist.
91	Object variable not set	Occurs when an object variable isn't initialized (set to an object).
92	For loop not initialized	Happens when trying to use a For loop that hasn't been initialized.
94	Invalid use of Null	Occurs when trying to use a Null value in a context where it's not allowed.
95	Invalid use of Null	Occurs when trying to use a Null value in a context where it's not allowed.
96	Unable to sink events of object because the object is already firing events	Occurs when trying to sink events of an object that is already firing events.
97	Can't call Friend procedure on an object that is not an instance of the defining class	Occurs when trying to call a Friend procedure on an object that is not an instance of the defining class.
98	A property or method call cannot include a reference to a private object, either as an argument or as a return value	Occurs when trying to reference a private object in a property or method call.
99	For Each...Next not initialized	Happens when trying to use a For Each loop that hasn't been initialized.
100	Cannot create AutoRedraw image	Occurs when trying to create an AutoRedraw image.
101	Application-defined or object-defined error	General error message often seen when working with Excel objects.
32767	User-defined error	Used to indicate user-defined errors.