Function does not return to calling subroutine

Posted on 2006-05-18
Last Modified: 2008-03-03
I call a VBA function from a VBA subroutine.
Here is what I observer as I debug and step into the code.

The subroutine properly starts, transfer properly to the function code.
The code in the function runs just fine but when it is time for the function to end or exit, all processing stops. Meaning, it does not reurn to the calling subroutine. Almost like it forgot is was originally called from another routine...

If you don't know the answer, do you know how I would even debug this ?
Question by:swendell

    Author Comment

    I was able to figure this out on my own.
    Very obscure and hopefully someone may find this valuable.
    One would think VB would throw some type of error rather then just stopping but maybe this is one for Microsoft ?
    Here we go:

    Calling Subroutine ()
    FormatCurrency (myfunction() )
    end sub

    Function myfunction ()
    myfunction = compute some value. Usually a number, otherwise a text error message
    end function  'If it was text, it would just stop here and not return to the calling subroutine, otherwise it would return properly

    I called my function wrapped in the FormatCurrency function from a standard sub routine as illustrated above.
    When my function did not return a numeric value; my function would not properly return to the calling subroutine.
    Removing the FormatCurrency function fixed the issue
    LVL 13

    Expert Comment


    since you anwered your own question, you can ask for a refund

    see for more info

    LVL 12

    Expert Comment

    i have a few ideas why you run into that behaviour. first of all: your code is a bit washy - you dont use datatypes at all, so everything you do and return is a variant. im not really sure, but FormatCurrency() seems to not accept variants. of course, VB itself always tries to cast the variant into the datatype which is needed. but when it cant cast your text to an integer, it just will fail. on top of that: not all errors are catched and stated to you - im not fully into this until today, but ive been in several scenarios where i surely had to step every line of code in debug mode just to see where the code "dies". and mostly it was because of variables which had an unexpected value, like "empty" orso.

    you could make your life easier with simply not provoking such errors. use datatypes, use error handlers and in most cases let a function return wheather it succeeded or not. do it like this:

    Sub MyOne ()
      Dim D as Double
      Dim S as String

      S = myFunction(D)
      If S = "" then
        MsgBox S
      End If
    End Sub

    Function MyFunction(ByRef D As Double) As String
      MyFunction = ""
      If D = 0 Then
        MyFunction = "My Error Message"
        D = D * 9.182
      End If
    End Function

    Accepted Solution

    PAQed with points refunded (500)

    Community Support Moderator

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Suggested Solutions

    Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now