[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

Function does not return to calling subroutine

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 ?
1 Solution
swendellAuthor Commented:
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

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

see for more info http://www.experts-exchange.com/help.jsp#hi70

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
PAQed with points refunded (500)

Community Support Moderator

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now