End Execution error

[Jedimaster]

Sub Test()

End

End Sub

Simple enough subroutine. When I run though debugger no problem. When I exicute it runs but always shows "Execution error". I know that it is because ending before the end of sub.

Thanks for any input.

[norman.yuan]

Simple enough, but bad code. The error message is expected.

If you really want to try simplest code to prove your VBA code runs, you can

Sub Test()

End Sub

Which, of course does nothing, but it runs!

"End" keyword is inherited from original BASIC language. "End" means unconditionally ends the code execution without taking care any potential environment issues. In "Visual" Basic era, one should never use "END". Something unexpected could happen, as you have seen from your simple code.

[Jedimaster]

What this is and END statement at the end of the routine to escape macro no mater what. If I use an END SUB the sub stops but the routine keeps on going. The users freak out because they think the routine has crashed. If the END statement is anywhre before the END SUB the error pops up. It does not show up in debugger just when you are running the macro free standing

Sub Test()
Ask = MsgBox("Are you sure you want to go.", vbYesNo, "Leaving")
If Ask = vbYes Then
End
End If
If Ask = vbNo Then
MsgBox "Glad to see you stay."
End If
End Sub

[RICVBA · Accepted solution]

maybe I'm missing some pieces but why can't you code like this

Sub Test()
    Ask = MsgBox("Are you sure you want to go.", vbYesNo, "Leaving")
    If Ask = vbYes Then Exit Sub
    MsgBox "Glad to see you stay."
End Sub

[norman.yuan]

To make it simple:

Where you use "End", you should use "Exit Sub" to exit the subroutine if the condition leads to early finish the subroutine. NEVER EVER use "END". If you claim you have to use it, or the rouine keeps going, then your code is wrong. You'd better fix your code, instead of using "END" to kill the entire VBA routine with "collateral damages", as you have been experienced.

[Jedimaster]

Have to have hard END to stop whole Function. WScript.Quit in scripting or (exit) in AutoLisp. The sub is a nested sub, being invoked from other subs. The END command works, it is just that outside of the debugger is comes back with the "Exeicution Error".

[RICVBA]

Never had such a need so I can't say how to properly handle an "end" instruction

but following your need to stop a main sub or function from within a nested one I could suggest a technique as follows

Sub main()
'this is the main external sub you're starting from, calling nested function

'do your stuff
'at some point you'll have a check like this
If Not functionOne(myInt, myObject, ...) Then ' functionOne is the first nested Function
    
    Exit Sub
End If

End Sub



Function functionOne(myInt As Integer, myObject As Object, ...) As Boolean

functionOne = True

'do your stuff
'at some point you'll have a check like this
If Not functionTwo(myDouble, myLong,...) Then ' functionTwo is the second nested Function

    functionOne = False
    Exit Function
End If

'go on with your function

End Function



Function functionTwo(myDouble As Double, myLong As Long,...) As Boolean

functionTwo = True

'do your stuff
'at some point you'll have a check like this
If Not functionThree(myString, myObject,...) Then ' functionThree is the last nested Function

    functionTwo = False
    Exit Function
End If

'go on with your function

End Function



Function functionThree(myString As String, myObject As Object,...) As Boolean

functionThree = True

'do your stuff
'at some point you'll have a check like this
If myCondition Then
    functionThree = False
    Exit Function
End If

'go on with your function

End Function

where each function parameters list is just an example, showing you don't have to pass the same parameters between functions

this way is safe and gives the result of stopping the entire process virtuallly in any nested function

you could also use nested subs in a similar way but with the following changes:

- pass the same boolean parameter from the main Sub down to each nested Sub that will set it to False or True

- have a check of this boolean parameter right after each nested sub call. on a negative result you'll have to Exit Sub

hope that helps

bye

[Jedimaster]

I found the problem with the using the "Exit Sub" it was the variable that I was using to escape with was not global so was not passing on to the other subs so I was not true escaping the loop. I have no need for a hard "END" now, although it would be nice to have a simple graceful exit to an entire routine if required.