Saturday, May 4, 2013

Avoiding On Error Resume next statements in VBA what is the road ahead then?

Avoiding Error statements in VBA what is the road ahead then?



Blank vs "" vs 0
Keeping there nothing
Keeping there ""
Formula giving "" or 0

NA is error
Div/0

All of them have their importance and implcation.

0 is different from isblank ... iszero.... is function... is date ... etc


These might become useful at times...

Also you can use 0 1 + etc while dealing with boolean which means true false 1 0 & and 



Explicitly handling error and not using error handlers is the key..



Sub errorp1()

Dim i, j As Double
For i = 1 To 5 Step 1
j = 1 / (i - 2)
On Error Resume Next
MsgBox j
'ignore the error and move ahead keeping last value
Next i
End Sub

Sub errorp2()

Dim i, j As Variant
For i = 1 To 5 Step 1
j = 1 / ((i - 2) * (i - 3))
MsgBox j
On Error GoTo ER
GoTo ER2
ER:
On Error GoTo 0
MsgBox "error"
Resume Next

ER2:
Next i

End Sub

'Notes
'http://stackoverflow.com/questions/...tween-on-error-goto-0-and-on-error-goto-1-vba
'http://www.excelfox.com/forum/f23/difference-between-on-error-goto-0-and-on-error-goto-1-a-894/
'On Error GoTo 0 disables any error trapping currently present in the procedure.
'On Error GoTo -1 clears the error handling and sets it to nothing which allows you to create another error trap.

Sub ErrorTest()

Dim dblValue As Double

On Error GoTo ErrHandler1
dblValue = 1 / 0
ErrHandler1:
MsgBox "Exception Caught"
On Error GoTo -1 'Comment this line to check the effect
On Error GoTo ErrHandler2
dblValue = 1 / 0
ErrHandler2:
MsgBox "Again caught it."

End Sub
Sub ErrorTestUserHandler()

Dim dblValue As Double

For lngloop = 1 To 10
On Error GoTo ErrHandler
dblValue = 1 / 0
ErrHandler:
MsgBox "Caught it."

On Error GoTo -1 'Comment this line and run it again to see the effect
Next

End Sub


Join our VBA for financial engineering course (http://www.wiziq.com/course/19620-vba-for-financial-engineering-and-modeling) & get 15% discount. Ask for discount code, email - info@qcfinance.in.

No comments:

Post a Comment