VBA prevent closing of UserForm with "End" in code

Question

In my spreadsheet, I have a UserForm that is supposed to be open at all times.

Once in a while, my code will contain an "End" where I exit the code based on some if statement.

The problem is that this closes the UserForm, is there a way to prevent this from happening?

EDIT:

Sub Test1()
    'Random code       
    Call Test2(Variable)
    'Random code           
End Sub

Sub Test2(ByVal Variable as Double)
    If Variable = 0 then
        'Random code
    End If
    If Variable = 1 then
        Call Test3
        End 'Original placement of End 
    End If
End Sub

Sub Test3()
    'Random code
End Sub

This is a rough example of how the code is build (its rather long at this point). So depending on the "variable" different things happen in Test2. But if the Variable is 1, then the "random code" back in Test1 can't be executed thus, so I have to stop the code. I tried replace "End" with "Exit Sub" this only stops the code in Test2 from running, is it will give me an error when it goes back to Test1.

EDIT2: Test1() is actually four different subs (at this point, more will be added) that all call Test2(). That is why I choose to split it up into so many subs and call them from within the subs.

Perhaps amend your code to `Exit Sub` instead of `End`? – Serveira Nov 14 '17 at 10:22 — Serveira, Nov 14 '17 at 10:22

Jakob Busk Sørensen · Answer 1 · 2017-11-14T12:15:26.840

2

No, not if you insist on using End. This will essentially have the same effect as clicking the "Stop" button in the developer window. You should (most likely) not be using End. I cannot tell you what you should be using, since I do not know what you are trying to achieve.

Update:

Based on your code, I don't see any reason for Test3() to be nested within Test2(), since it runs either the random code or Test3() (never both). Is there anything preventing you from splitting all the different cases into different subs, and then doing the If statement in the main sub?

Sub Main()
    If Variable = 0 Then
        'Random code from before Test2()
        'Random code from Test2()
        'Random code from after Test2()

    ElseIf Variable = 1 Then
        Call Test3()

    Else
        MsgBox "Variable must be 0 or 1!"

End Sub

edited Nov 14 '17 at 12:15

answered Nov 14 '17 at 10:24

Jakob Busk Sørensen

5,599
7
44
96

Thank you! How else would you stop the code? I tried add an example of what I'm trying to do. – Dennis Christiansen Nov 14 '17 at 11:49
Have a look at the update. I don't know exactly what you are trying to do, so I cannot make it very specific. But it should probably be something like what I posted. It is also a lot easier to read, than your original code. As you can see the `Variable = 0` case contains three lines. This is to indicate that you can (and probably should) split the code into multiple `Subs`. – Jakob Busk Sørensen Nov 14 '17 at 11:58
Yeah, I know. The problem is that its is bit hard to explain without knowing all the code and its around 1000 lines at this point. The reason I chose to split up the code within multiple subs, is that Test1 is actually 4 different subs, that will then call Test2, which would make the code very long if I followed your suggestin. Test3 could easily be embedded within Test2. – Dennis Christiansen Nov 14 '17 at 12:04
The thing is, the more you nest, the harder it becomes to read. If you start using `End` deep inside the nesting, it becomes even worse. Can't you create all the subs you need, and then make an `If` statement which calls the desired subs, in the desired order, based in some condition (like my examle)? – Jakob Busk Sørensen Nov 14 '17 at 12:08

score 1 · Answer 2 · answered Nov 14 '17 at 10:44

1

End closes anything and kills all the variables and objects that you have.

This is probably the worst way to end any sub and most probably you do not need it.

What's the deference between "end" and "exit sub" in VBA?

https://msdn.microsoft.com/VBA/Language-Reference-VBA/articles/end-statement

answered Nov 14 '17 at 10:44

Vityata

42,633
8
55
100

1

I was not aware of what exactly End did and this is just the first time I've had a problem like this. – Dennis Christiansen Nov 14 '17 at 11:28
How else would you stop the code? I tried add an example of how my code works? Replacing End with Exit Sub didn't work. – Dennis Christiansen Nov 14 '17 at 11:49

score 1 · Accepted Answer · answered Nov 14 '17 at 12:22

1

You somehow need to tell Test1 that it needs to stop. One approach to this problem is to change your subs to functions and return a value indicating status. Something like this would work:

Function Test1() As Integer
    Dim i As Integer

    'Random code
    i = Test2(Variable)
    If i = 1 Then Exit Function
    'Random code
End Function

Function Test2(ByVal Variable As Double) As Integer
    Test2 = 0

    If Variable = 0 Then
        'Random code
    End If
    If Variable = 1 Then
        Call Test3
        Test2 = 1
        Exit Function
    End If
End Function

Function Test3() As Integer
    'Random code
End Function

answered Nov 14 '17 at 12:22

Brian M Stafford

8,483
2
16
25

While that could work, I do see a risk of ending up with some very confusing code. Also, in that specific example, the `Exit Function` has no effect at all. – Jakob Busk Sørensen Nov 14 '17 at 12:48
True, the Exit Function has no effect here, but in order to continuing with the many nested subs, this would solve the problem I realize that it might make it a little more confusing to read, but when the Test2 sub is used in so many previous subs (and still not done) this would be a temporary solution. Thank you all for the help! – Dennis Christiansen Nov 16 '17 at 13:27

score 0 · Answer 4 · answered May 27 '23 at 22:54

I was just faced with a similar problem. Rather then rewrite all my code to keep track of a global variable I chose to use a user defined error. I already had an error handler at the end of my code so rewrite was minimal. Something like this;

Sub Main_Loop ()
On Error goto EHandler
Call Sub1
Call Sub2
Function X
Ehandler:
If err.number = user_error then clean_up _
   else msgbox ("OOPS!")
End sub

In my case I have a userform that through winapi calls is made a child window of the desktop and topmost so I can use it on all open windows of the office application to enter form data onto the mainframe terminal. When the form validation fails, I want code execution to continue but skip the data entry. I also don't want to close the userform.

Bonus is this also makes testing faster.

Basically this is a goto statement but being an error it works even in nested sub/function calls. Guess I couldn't get away from GOTO after all. Oh well. Will I goto hell for using just one goto? I'll update this post when I find out. :P

VBA prevent closing of UserForm with "End" in code

4 Answers4