Error Handling Techniques in VBA Programming

 
Error Handling
 
We already have seen type of errors and how to debug
our programs.
Especially the run time errors and logical errors are hard
to be found and estimated on design time.
No matter how carefully you design and code for all
conditions, there is always a user who does something
that you never thought of and effectively breaks the set
of rules that you have written.
So, in order to develop more reliable application, we
have to use error handling in our code.
 
2
 
Error Handling
 
Any run time error
 will stop execution of your
 
program.
The user will be very unhappy and will lose a great deal
of faith in your application.
The error needs to be trapped, and an appropriate
action needs to be taken from within the
 
VBA code
(Debugging)
.
Error handling
 techniques 
may are
 also 
be 
used 
for
capturing runtime errors that can halt the running
program.
 
3
 
Error Handling
 
On Error GoTo
 
statement
This command is used like exception handling command
of C, C++, C#, Java, JavaScrip (try … catch …)
Try this simple example without a disk in drive D.
Sub Test_Error()
    
temp = Dir("d:\*.*")
End Sub
This will produce an error message saying that the D drive is
not ready.
In normal terms, your
 
program has crashed and will not
work any further until this error is resolved.
 
4
 
Error Handling
 
On Error GoTo
 
statement
You can place a simple error-trapping routine as follows:
Sub Test_Error()
On Error GoTo err_handler
temp = Dir("d:\*.*")
Exit Sub
err_handler:
MsgBox "The D drive is not ready" & " " & Err.Description
End Sub
The first line sets up a routine to jump to when an error occurs
using the On Error statement.
 
5
 
Error Handling
 
It points to 
err_handler
, which is a label just below the 
Exit Sub
line further down that will
 
deal with any error condition.
The purpose of a label is to define a section of your code you
can jump to by using a 
GoTo
 statement.
The line to read the D: drive is the same as before, and then
there is an Exit Sub line,
 
because if all is well you do not want
the code continuing into the 
err_handler
 routine.
If an error happens at any point after the 
On Error
 line, the
code execution jumps to
 
err_handler
 and displays a message
box that says drive D is not ready.
 
6
 
Error Handling
 
However, you may
 
have noticed that the code execution
jumps to 
err_handler
 when any error occurs, not just
 
the drive
not ready error.
An error could occur because you made a mistake in typing
this
 
code in.
This could have unfortunate consequences in your code.
Fortunately, you can interrogate the error to find out what
went wrong
.
You can also use
 
the 
Err
 object to give the description of the error
and to concatenate it into your message so
 
that it also says “Drive
not ready.”
 
7
 
Error Handling
 
You do this using the 
Err
 function.
This will return the
 
number associated with the runtime error
that happened.
Sub Test_Error()
On Error GoTo err_handler
    temp = Dir("d:\*.*")
    Exit Sub
err_handler:
    If Err.Number = 71 Then
        MsgBox "The D drive is not ready"
    Else
        MsgBox "An error occurred"
    End If
   Resume
End Sub
 
8
 
Error Handling
 
You can also add the statement 
Next
 to 
Resume
.
This will skip over the statement that
 
created the error
and therefore ignore the error.
Sub Test_Error()
     
On Error Resume Next
     
temp = Dir("d:\*.*")
End Sub
If there is no disk in drive D, the code will still run perfectly
because of 
On Error Resume
 
Next
I
t skips over the line of code creating the error.
Resume Next can be useful for dealing with errors as they
occur, but it can make
 
debugging code very difficult.
 
9
 
Error Handling
 
In a later stage of your program, you may have incorrect
or
 
nonexistent data being produced due to the fact that
an error condition earlier was ignored.
You can end up with a program that appears to run
okay, but in fact does nothing because it
 
has some
hidden bugs or incomplete data.
This is because every time an error is encountered,
 
the
execution just skips over it.
This can give a false impression of what is actually
happening
.
 
10
 
Error Handling
 
S
o if you do use On Error Resume Next, make sure that
you check all inputs and outputs to
 
the code to ensure
that everything is working as it should be.
Make sure that your 
On Error
 
Resume Next 
statement
cannot cover up an error
 in a read from a spreadsheet
cell or from
 
a file.
This could cause disaster in your program because the
On Error Resume Next
 
statement would make it appear
to work perfectly.
 
11
 
Implications of Error Trapping
 
When you use an On Error statement in your code, that
error trap remains throughout the
 
procedure unless it is
disabled.
As you just saw, you can set up a routine to check
whether the
 
D: drive has a disk in it and take action
accordingly.
However, it is important to turn this off
 
once the possibility
of the error has taken place.
If you do not do this, then all subsequent
 
errors within
that procedure will use the same error-handling routine.
 
12
 
Implications of Error Trapping
 
This gives extremely
 
confusing results to the user, as the
error message is likely to be totally meaningless in
relation to the error generated.
A subsequent error could relate to division by zero, but
the
 
error message will come up saying “Drive D not
ready.”
If On Error Resume Next has been used and not switched
off, all sorts of errors could be
 
taking place without the
user being aware of them. You disable the error-trapping
routine as
 
follows:
On Error Resume Next
On Error GoTo 0
 
13
 
Implications of Error Trapping
 
On Error Resume Next
On Error GoTo 0
The On Error Resume Next statement that you saw
previously ignores all errors.
The On
 
Error GoTo 0 cancels any error handling and
allows all errors to be displayed as they
 
normally would.
This cancels out the On Error Resume Next and puts
everything back to
 
normal error handling.
 
14
 
Generating Your Own Errors
 
In order to detect and recover logical errors
;
 at the
beginning you may define your 
rules
 and anything else
as errors.
Then you can handle these 
with
 the same
 
way 
in the
error handling systems.
Example: if the legal working age should be anything
between 18 to 65, you may define any age less them 18
and greater then 65 is a user defined error.
To generate a run-time error you can use the Raise
method
 Err Object
Err.Raise(Number [, Source] [, Description])
 
15
 
Generating Your Own Errors
 
Err.Raise(Number [, Source] [, Description])
Number
Long, identifying the type of the error. Visual Basic errors range from 0 to
65535
The range 0 - 512 is reserved for system errors
The range 513 - 65535 is available for user defined errors
If (w_age<18) Or (w_age>65) 
Err.Raise 
1500, ,«Working age have to be (18-65)»
Source
String
If this is not specified then the programming ID of the current project is used.
Description
This the string that describes the error.
If the error can be mapped to a Visual Basic run-time error the string
corresponding to the Number is 
u
sed.
If there is no Visual Basic error corresponding to the number then
"Application-defined or object-defined error" message is used.
 
16
 
Generating Your Own Errors
 
Sometimes it is useful when you are testing your own applications or
when you want to treat a particular condition as being equivalent to a
runtime error.
You can generate an error in your code with the Error statement:
Sub Test_Error()
     Error 71
End Sub
This simulates the “Drive not ready” error. You could also use
Err.Raise(71) to do this.
In addition, you can regenerate the current error by using the following:
Error Err
The purpose of this is to raise the “Drive not ready” error message as if
the drive really was not ready because this is the current error found.
If another error message is then created, this becomes the current error.
 
17
Slide Note
Embed
Share

Error handling is crucial in VBA programming to catch and manage runtime errors effectively. This article explores various error handling techniques such as On Error GoTo statement to prevent program crashes and enhance the reliability of applications. By using structured error handling, developers can address unexpected user inputs and ensure smoother execution of their VBA code.


Uploaded on Sep 16, 2024 | 0 Views


Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

E N D

Presentation Transcript


  1. ITEC397 Macro Coding 08 ERRORS AND ERROR FUNCTION

  2. Error Handling 2 We already have seen type of errors and how to debug our programs. Especially the run time errors and logical errors are hard to be found and estimated on design time. No matter how carefully you design and code for all conditions, there is always a user who does something that you never thought of and effectively breaks the set of rules that you have written. So, in order to develop more reliable application, we have to use error handling in our code.

  3. Error Handling 3 Any run time error will stop execution of your program. The user will be very unhappy and will lose a great deal of faith in your application. The error needs to be trapped, and an appropriate action needs to be taken from within the VBA code (Debugging). Error handling techniques may are also be used for capturing runtime errors that can halt the running program.

  4. Error Handling 4 On Error GoTo statement This command is used like exception handling command of C, C++, C#, Java, JavaScrip (try catch ) Try this simple example without a disk in drive D. Sub Test_Error() temp = Dir("d:\*.*") End Sub This will produce an error message saying that the D drive is not ready. In normal terms, your program has crashed and will not work any further until this error is resolved.

  5. Error Handling 5 On Error GoTo statement You can place a simple error-trapping routine as follows: Sub Test_Error() On Error GoTo err_handler temp = Dir("d:\*.*") Exit Sub err_handler: MsgBox "The D drive is not ready" & " " & Err.Description End Sub The first line sets up a routine to jump to when an error occurs using the On Error statement.

  6. Error Handling 6 It points to err_handler, which is a label just below the Exit Sub line further down that will deal with any error condition. The purpose of a label is to define a section of your code you can jump to by using a GoTo statement. The line to read the D: drive is the same as before, and then there is an Exit Sub line, because if all is well you do not want the code continuing into the err_handler routine. If an error happens at any point after the On Error line, the code execution jumps to err_handler and displays a message box that says drive D is not ready.

  7. Error Handling 7 However, you may have noticed that the code execution jumps to err_handler when any error occurs, not just the drive not ready error. An error could occur because you made a mistake in typing this code in. This could have unfortunate consequences in your code. Fortunately, you can interrogate the error to find out what went wrong. You can also use the Err object to give the description of the error and to concatenate it into your message so that it also says Drive not ready.

  8. Error Handling 8 You do this using the Err function. This will return the number associated with the runtime error that happened. Sub Test_Error() On Error GoTo err_handler temp = Dir("d:\*.*") Exit Sub err_handler: If Err.Number = 71 Then MsgBox "The D drive is not ready" Else MsgBox "An error occurred" End If Resume End Sub

  9. Error Handling 9 You can also add the statement Next to Resume. This will skip over the statement that created the error and therefore ignore the error. Sub Test_Error() On Error Resume Next temp = Dir("d:\*.*") End Sub If there is no disk in drive D, the code will still run perfectly because of On Error Resume Next It skips over the line of code creating the error. Resume Next can be useful for dealing with errors as they occur, but it can make debugging code very difficult.

  10. Error Handling 10 In a later stage of your program, you may have incorrect or nonexistent data being produced due to the fact that an error condition earlier was ignored. You can end up with a program that appears to run okay, but in fact does nothing because it has some hidden bugs or incomplete data. This is because every time an error is encountered, the execution just skips over it. This can give a false impression of what is actually happening.

  11. Error Handling 11 So if you do use On Error Resume Next, make sure that you check all inputs and outputs to the code to ensure that everything is working as it should be. Make sure that your On Error Resume Next statement cannot cover up an error in a read from a spreadsheet cell or from a file. This could cause disaster in your program because the On Error Resume Next statement would make it appear to work perfectly.

  12. Implications of Error Trapping 12 When you use an On Error statement in your code, that error trap remains throughout the procedure unless it is disabled. As you just saw, you can set up a routine to check whether the D: drive has a disk in it and take action accordingly. However, it is important to turn this off once the possibility of the error has taken place. If you do not do this, then all subsequent errors within that procedure will use the same error-handling routine.

  13. Implications of Error Trapping 13 This gives extremely confusing results to the user, as the error message is likely to be totally meaningless in relation to the error generated. A subsequent error could relate to division by zero, but the error message will come up saying Drive D not ready. If On Error Resume Next has been used and not switched off, all sorts of errors could be taking place without the user being aware of them. You disable the error-trapping routine as follows: On Error Resume Next On Error GoTo 0

  14. Implications of Error Trapping 14 On Error Resume Next On Error GoTo 0 The On Error Resume Next statement that you saw previously ignores all errors. The On Error GoTo 0 cancels any error handling and allows all errors to be displayed as they normally would. This cancels out the On Error Resume Next and puts everything back to normal error handling.

  15. Generating Your Own Errors 15 In order to detect and recover logical errors; at the beginning you may define your rules and anything else as errors. Then you can handle these with the same way in the error handling systems. Example: if the legal working age should be anything between 18 to 65, you may define any age less them 18 and greater then 65 is a user defined error. To generate a run-time error you can use the Raise method Err Object Err.Raise(Number [, Source] [, Description])

  16. Generating Your Own Errors 16 Err.Raise(Number [, Source] [, Description]) Number Long, identifying the type of the error. Visual Basic errors range from 0 to 65535 The range 0 - 512 is reserved for system errors The range 513 - 65535 is available for user defined errors If (w_age<18) Or (w_age>65) Err.Raise 1500, , Working age have to be (18-65) Source String If this is not specified then the programming ID of the current project is used. Description This the string that describes the error. If the error can be mapped to a Visual Basic run-time error the string corresponding to the Number is used. If there is no Visual Basic error corresponding to the number then "Application-defined or object-defined error" message is used.

  17. Generating Your Own Errors 17 Sometimes it is useful when you are testing your own applications or when you want to treat a particular condition as being equivalent to a runtime error. You can generate an error in your code with the Error statement: Sub Test_Error() Error 71 End Sub This simulates the Drive not ready error. You could also use Err.Raise(71) to do this. In addition, you can regenerate the current error by using the following: Error Err The purpose of this is to raise the Drive not ready error message as if the drive really was not ready because this is the current error found. If another error message is then created, this becomes the current error.

More Related Content

giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#