21

I am new to VBA and want to return from a function when I see an error. Not able to do so. Any pointers?

Function GetEditboxValue(control As IRibbonControl, text As String) As String

    If Not IsMissing(text) Then
        If Not IsNumeric(text) Then
            MsgBox "Please enter numeric value only."
            ' I WANT TO RETURN HERE 
        End If
    End If


    If control.id = "xyz" Then
    spaceAboveTable = text
    End If


End Function
Improve this question
4
  • 1
    You do realize that your function doesn't return a value, right? In VBA, a function that doesn't return a value should be declared as a Sub. Otherwise, you'll want to assign a value to GetEditboxValue before exiting the function. (Obviously, this is in a case where you don't want to exit early without returning a value.) Commented Dec 29, 2010 at 12:03
  • 1
    Not to forget, this function looks very ugly. Instead of returning a value, from an editbox (what the name implies), the only thing it seems to do is making some nasty side effect - giving you a maintenance headache sooner or later. Instead of separating error detection from error handling, it handles errors itself with a MsgBox. Better separate these concerns, if text is not numeric, return an error value or raise an error event or something like this, but let the caller decide if he wants to show a message box or do some different error handling. Commented Dec 29, 2010 at 12:30
  • @Cody, Just to clarify your remark for @ydobonmai, all routines declared as functions will return a value. If you don't specify exactly what value with a line like 'GetEditboxValue = <something>' then your function will return whatever the default value for the type of the function is. In this case that's the empty string, but it could be 0, "", False, Empty, Nothing, etc. Commented Dec 29, 2010 at 15:53
  • @Doc, I agree. I am new to VBA and This is something I wrote very quicky and just didnt know about EXIT FUNCTION. The function is not even complete. I just hit a wall and asked here in SO. Thank you for your valuable comments and your time. Commented Dec 29, 2010 at 16:39

4 Answers 4

Reset to default
42

You need to put EXIT FUNCTION there to get out of further execution:

Function GetEditboxValue(control As IRibbonControl, text As String) As String

    If Not IsMissing(text) Then
        If Not IsNumeric(text) Then
            MsgBox "Please enter numeric value only."
            EXIT FUNCTION
        End If
    End If


    If control.id = "xyz" Then
    spaceAboveTable = text
    End If


End Function
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@Sarfraj, I have a VBA question here :- stackoverflow.com/questions/4565185/…. Could you please help.
Or EXIT SUB if it's a Subroutine, instead of a function.
6

In a generalized sense (I'm not actually saying anything that hasn't already been said):

Function Foo(inputVar As Double) As Double

    On Error GoTo ErrorHandler

    ' Code assigning something to returnValue and defaultOutput

    Foo = returnValue

    Exit Function

    ErrorHandler:

    Foo = defaultOutput

 End Function

No reason it has to be Double, of course.

(Edited because I had a second "Exit Function" instead of "End Function.")

Improve this answer

Comments

1

You can use Exit Function after your MsgBox statement

Improve this answer

Comments

0

Another option is to explicitly raise an error using Err.Raise. Since you have no error handler within this function (ie, no On Error ... line) the error will be propagated up through the call stack until it runs into an error handler.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.