VBA - Exit All Methods, Leave UserForm Running

[higgsch]

I am running a project from a UserForm. Several methods deep I have an error checking function. Upon receiving an error, I would like to show an msgBox then return to the UserForm without continuing the parent methods. I tried 'End' but this ends the UserForm itself. Could someone point me in the right direction? (I really don't want to have to add booleans to flag errors all the way through my methods)

 

[Hermanito]

Are you using any error handling code? Like "On Error GoTo ErrDBConnection" and stuff like that?
Because imho, that is the way you should be handling this...

Also, if you get an error a few methods deep... I think it's strange to want to skip any further handling in parent methods...
You need a way for the error to 'bubble up' to where you want to handle it. And when you handle it, all parent methods of that location should be able to continue their work, otherwise that error was not really handled.

Basically, the simplest way to achieve what you want, would be like this:
- add a catch-all errorhandler in your userform event handlers (for click handlers etcetera) where you call the next level of code.
- don't use any error handler in the lower levels of code.
- if any error pops up at any depth level , it bubbles up right to the handler in the userform event, where you can show the messagebox and leave it at that.

It might look like this:

Public Sub btnSomeOperation_Click()
   On Error GoTo ErrUnexpected
   Call TheNextLevel
ExitSub:
   Exit Sub
ErrUnexpected:
   MsgBox "Something unexpected happened, sorry, please try again...", vbOKOnly + vbExclamation
   Resume ExitSub
End Sub

 

[mikerickson]

You can use Err.Raise to pass errors upwards

Sub One()
    On Error GoTo EHandle1
    Call Two(0)
    MsgBox "end of one"
    Exit Sub
EHandle1:
    MsgBox "error in one" & vbCr & Error
    Err.Clear
    Resume Next
End Sub

Sub Two(n As Long)
    On Error GoTo EHandle2
    MsgBox 1 / n
    MsgBox "end of two"
    Exit Sub
EHandle2:
    'MsgBox "error in two" & vbCr & Error
    Err.Clear
    Err.Raise 27 + vbObjectError, Description:="my Error"
End Sub

 

[higgsch]

I'm not receiving any error, so using an OnError would not work. My Error checking method is checking to make sure the data is of the expected data type from my programming logic. This helps me remove Logic Errors. (i.e. If I ask the user for a number to add to a constant variable, and the user enters a string with no numbers, I wouldn't want to continue the parent methods' Logic with the bad data.) Does this make any sense to anyone besides myself or is this just such an abstract and/or round-about way of testing for proper data input?

 

[Hermanito]

You're talking about basic data validation... imho, that should not be a few methods deep like you described at first...

You can perfectly combine the example I gave with the technique mikerickson gave:

Use my errorhandling template in your first level userform event handlers.
Use the Err.Raise method wherever you feel the need to jump out of your validation and back to the userform method that made the first call down. You don't need to use errorhandling in your validation methods, you can simply raise your own error, which will then bubble upwards to the first errorhandler it finds, which will be the one in the form eventhandler.

I hope that's clear, feel free to ask if it's not...

 

[higgsch]

Oh! I get it. Unfortunately, this project requires the user to enter data throughout several levels of methods. I didn't realize that using Err.Raise you could 'create your own error.' It makes sense though. Thanks, I got it working.

 

[higgsch]

Scratch that. I thought it was working.
I added the Err.Raise to my lower functions, and the errorhandling template to my top calling method. It shows my error, but never enters the code under the errorhandling label.

My lowest function Err.Raise statement:

Err.Raise 665 + vbObjectError, Description:="DatSheet is corrupt"

My top calling method:

Private Sub UserForm_Activate()
     On Error GoTo DataSheetCorrupt
     ...
     GetData 'my lower method call
     ...
     Exit Sub
DataSheetCorrupt:
     If Err.Number = 665 + vbObjectError then
           MsgBox Err.Description
     End If
End Sub

I even tested with the code you gave me (the division by zero) running from excel (not in debug mode), and it doesn't catch the error, it just brings up the run-time error dialog box (and the break in debug).
"Run-Time Error "11" Division by zero" options: End, Debug, or Help

What's going on? The code you gave me should work.

 

[higgsch]

Well I feel sheepish, I found out what was wrong...
I had 'Break on all Errors' selected instead of 'Break on all Unhandled Errors' in Tools-->Options...-->General-->Error Trapping

Both are working, Ignore my previous post.

 

[Hermanito]

Glad you got it working... That 'Break on all errors' can be a tough one if you don't know about it or forget to look