swendell
asked on
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 ?
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 ?
since you anwered your own question, you can ask for a refund
see for more info https://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
FormatCurrency(D)
Else
MsgBox S
End If
End Sub
Function MyFunction(ByRef D As Double) As String
MyFunction = ""
If D = 0 Then
MyFunction = "My Error Message"
Else
D = D * 9.182
End If
End Function
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
FormatCurrency(D)
Else
MsgBox S
End If
End Sub
Function MyFunction(ByRef D As Double) As String
MyFunction = ""
If D = 0 Then
MyFunction = "My Error Message"
Else
D = D * 9.182
End If
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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