How to Terminate VBA Sub from within called Function

Question

How do I get a VBA sub to exit from within a called function if an error occurs?

I've tried placing Exit Sub statements within the relevant functions, but whenever I save and re-open the file, VBA automatically changes it to Exit Function.

For example, how do I get the code below

Sub foo()
    userNum = userNum()

    MsgBox(var)
End Sub

Function userNum() As Integer
    userNum = InputBox("Enter your positive number")

    If var < 0 Then
        MsgBox("Invalid: your number must be positive.")
        Exit Sub
    End If
End Function

to not execute MsgBox(var)?

userNum() knows nothing about the callee foo(), so generally the only way to control the flow is to return some value and build a conditional statement within the sub. In this partucular case you may just use End instead of Exit Sub. — omegastripes
– omegastripes, Commented Jul 9, 2019 at 9:03
@omegastripes does using End by itself that work? I've searched the VBA dpcumentation and I can't find any mention of End without another keyword immediately after it. Source: learn.microsoft.com/en-us/dotnet/visual-basic/… — HarrisonO
– HarrisonO, Commented Jul 9, 2019 at 13:46
End just terminates code execution. Like you press "stop" button in debugger. — omegastripes
– omegastripes, Commented Jul 9, 2019 at 19:09

Siddharth Rout · Accepted Answer · 2019-07-09 07:15:02Z

1

Declare variables.
I would recommend using Application.InputBox with Type:=1 to accept numbers as input.
Declare the result as Variant so that you can handle the the Cancel button in the InputBox. Also use that to decide whether you want to show the number or not.

Is this what you are tying? (Untested)

Option Explicit

Sub foo()
    Dim numb As Variant
    numb = userNum()

    If numb <> False Then MsgBox numb
End Sub

Function userNum() As Variant
    Dim Ret As Variant

    Ret = Application.InputBox(Prompt:="Enter you number", Type:=1)

    If Ret = False Then
        userNum = False
        Exit Function
    ElseIf Ret < 0 Then
        MsgBox ("Invalid: your number must be positive.")
        userNum = False
        Exit Function
    End If

    userNum = Ret
End Function

answered Jul 9, 2019 at 7:15

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HarrisonO Over a year ago

Is there a more succint way of doing it that doesn't involve having the If numb <> False Then ... lines in the sub? Similar to what I have in my question.

Siddharth Rout Over a year ago

If numb <> False handles both the situation when the user presses cancel as well as any negative numbers. If you wanted number greater than 0 then it would have been a much simpler solution :)

Collectives™ on Stack Overflow

How to Terminate VBA Sub from within called Function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related