How do I perform error handling? 2

[gizzy17]

I have a macro in Excel that references various sheets, but if the user renames a sheet, they get an out of range error. I would like to use something like try catch block in c++ or javascript to provide a usefull error message.

 

[RobBroekhuis]

Look up help on the "on error" statement in VBA. You can either choose to ignore errors (on error resume next), which is not typically advisable, or divert program flow to a separate error handler:

sub ...
on error goto MyErrorHandler
sheets("ThisOne&quot.activate
...
...
exit sub
MyErrorHandler:
msgbox "Could not find sheet ThisOne"
end sub


Rob

 

[SBendBuckeye]

Instead of using Name, use CodeName which is a property created when the sheet is created and unchanged thereafter. It will actually be Sheet1, Sheet2, etc.

If you want to see how they get out of sync insert some sheets, delete a few, etc and then look at the values. What happens is that if you delete Sheet4 and then insert a new sheet it will be added as Sheet4. However, codename will be Sheet5 or whatever depending on what was done in the interim.

NOTE: One gotcha, if you are using Sheets("YourSheetName&quot in your code then you cannot use CodeName. In that case you would have to spin through the Sheets collection and manually test for CodeName.

VBA currently has no Try / Catch block. There are 2 ways you can deal with errors. One way is to use labels.

Sub WithLabels()
On Error GoTo TrapError:

ExitHere:
Exit Sub

TrapError:
Select Case Err.Number
Case 13 'Description
Resume Next
Case Else
MsgBox "Error: " & Err.Number & vbNewLine & _
Err.Description, vbCritical, "Your Title"
Resume ExitHere
End Select
End Sub

You could let it drop out the bottom but resume exit label preserves one entry point and one exit point coding conventions. Your first On Error GoTo statement can occur anywhere in the code.

This is essentially what happens with a Try / Catch block except it doesn't use GoTo logic.

The second way is to use an On Error Resume Next / On Error GoTo 0 pair. Then you can test as much as is required.

Sub WithNoLabels()
On Error Resume Next 'Handle errors here
If Err.Number Then
Err.Clear 'Clear error
'Do processing
Exit Sub
End If

On Error GoTo 0 'Reset error handling
End Sub

Note: If you set On Error Resume Next it will remain in effect until it is reset. I try to always pair them in every procedure. It is very disconcerting to be debugging code and suddenly end up 2 or 3 procedures up in the calling chain because you set it and didn't change it for any of the intervening procedures.

Hope this helps and Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[SBendBuckeye]

Hey Rob,

Good to see you back and doing what you do with the best of them which is being a helpful contributor to the Tek-Tips forum.


Have a great day!

j2consulting@yahoo.com