Function to return two values

J

Jac Tremblay

Hi,
Is it possible to create a function that will return two values, a boolean
and a string?
I need to exit the main procedure if the data checked by the function is not
valid and also display a message according to the particular validation.
For example:
If Not DataIsValid Then
Msgbox strErrorMsg ' Returned from the function
Exit Sub
end IF
Thanks
 
J

Jim Cone

Maybe...
'--
Function IcelandBanking(ByRef strMsg As String) As Boolean
Dim WontPay As Long
Dim NoReserves As Boolean

'Code here to calculate WontPay and NoReserves
WontPay = 0
NoReserves = True

If WontPay > 0 Then
strMsg = "Small Withdrawals Only"
Else
strMsg = "You are out of luck"
End If
IcelandBanking = NoReserves
End Function
'--
Sub FinancialStatus()
Dim strText As String
MsgBox IcelandBanking(strText) & vbCr & strText
End Sub
--
Jim Cone
Portland, Oregon USA



"Jac Tremblay"
<[email protected]>
wrote in message
Hi,
Is it possible to create a function that will return two values, a boolean
and a string?
I need to exit the main procedure if the data checked by the function is not
valid and also display a message according to the particular validation.
For example:
If Not DataIsValid Then
Msgbox strErrorMsg ' Returned from the function
Exit Sub
end IF
Thanks
 
P

Peter T

A function can return an array of values so the direct answer to your
question is yes. However, unless your function is a UDF (for use in cell
formulas), it would probably be better to pass an additional argument
'ByRef', eg

Sub Test
dim v as variant
Dim bResult as boolean
sMsg as string

v = 123
bResult = foo(v, sMsg)
if not bResult then
msgbox sMsg
Else
msgbox bResult
End if

End Sub

Function foo(ByVal data as Variant, ByRef strErrorMsg as String) as Boolean
' code to check data
If Not DataIsValid then
strErrorMsg = "this data not right right for this reason"
Else
foo = True
End If
End function

Regards,
Peter T
 
J

Jac Tremblay

Hi Jim,
When I first read your answer, I thought that wouldn't work. But I tried it
out and found it is interesting. I changed it a bit so I could get the two
values in two separated variables. Here is what I came up with:
Function IcelandBanking(ByRef strMsg As String) As Boolean
Dim WontPay As Long
Dim NoReserves As Boolean

'Code here to calculate WontPay and NoReserves
WontPay = 1
NoReserves = False

If WontPay > 0 Then
strMsg = "Small Withdrawals Only"
Else
strMsg = "You are out of luck"
End If
IcelandBanking = NoReserves
End Function
'--
Sub FinancialStatus()
Dim strText As String
Dim strTrueOrFalse As String
Dim strMsg As String
Dim strResult As String
Dim intPosVbCr As Integer
' MsgBox IcelandBanking(strText) & vbCr & strText
strResult = IcelandBanking(strText) & vbCr & strText
intPosVbCr = InStr(1, strResult, vbCr)
strTrueOrFalse = Mid(strResult, 1, intPosVbCr - 1)
strMsg = Mid(strResult, intPosVbCr + 1, Len(strResult) - intPosVbCr)
MsgBox "strTrueOrFalse = " & strTrueOrFalse & vbCrLf & _
"strMsg = " & strMsg
End Sub
It works OK, but I am not sure I will use it.
My problem is that I have to check 4 different information from a
transaction. I want to group the 4 validations in a separate procedure or
function. If an error occurs, I want to display a pertinent message and exit
the main proc. Here is another way I figured out to solve my problem:
Option Explicit
Public strMessage As String
' ---
Function fbooDataIsValid(ByVal pstrParam1 As String, _
ByVal pintParam2 As Integer) As Boolean
If pstrParam1 = "Hi Jim" Then
strMessage = "Error, my friend. You should have said Hello!."
fbooDataIsValid = False
Exit Function
End If
If pintParam2 < 100 Then
strMessage = "Error, your number is < 100."
fbooDataIsValid = False
Exit Function
End If
fbooDataIsValid = True
End Function
' ---
Sub TestDeJac()
Dim strText As String
strText = "Hi Jim"
If Not fbooDataIsValid(strText, 36) Then
MsgBox strMessage, vbCritical, "Sofica"
Exit Sub
End If
MsgBox "It's OK...", vbInformation, "Sofica"
End Sub
Thank you for your original comment and good night.
 
J

Jac Tremblay

Hi Peter,
I tried your code but it does not return two values at the same time. It
returns one or the other. See my answer to Jim's comment.
Thank you for your time.
 
P

Peter T

Jac,

I can only repeat what I tried to explain previously, perhaps in different
words. A function can only return one 'thing'. This 'thing' can be a value,
an object or an array of values or objects. IOW, a function can return
multiple values in an array, indeed virtually unlimited subject resources.

When multiple values are required back from a function the alternative to an
array is to pass arguments ByRef. Such arguments can be assigned or changed
in the function and are returned to the calling procedure. Obviously, as I
mentioned previously, this approach is of no use if your function is
intended as a UDF (although a UDF can return a 1 or 2D array of values for
use in an array entered function).

Both Jim's and my examples demonstrated the ByRef approach to give you your
second value, the first being returned by the function itself. Jim's example
was rather more prescient, mine included a typo -
should of course have read
Dim sMsg as string


I do not agree with Bernd's recommendation not to change ByRef variables,
it's exactly what ByRef is intended for in this context (and assuming not a
UDF).

Regards,
Peter T

Jim -
Q. What's the capital of Iceland
A. About 25 dollars, a bit less by the time you read this
 
J

Jim Cone

Hi Peter,
re: "Q. What's the capital of Iceland
A. About 25 dollars, a bit less by the time you read this"

That's a good one, I can't top that.
Regards,
Jim Cone
 
P

Peter T

Hi Bernd,

I take it you are referring to this comment -

"
Limit the number of parameters passed by reference (var parameters in
Pascal, pointers passed in C). There are only two ways that a subroutine can
screw up a variable in another subroutine: global variables and variables
passed such that they can be changed. The problem in both cases is that you
increase the chance that something will be changed in a way that you didn't
expect.
"

The paper is all about style, by definition that's subjective albeit based
on hard learnt experience. As a general comment I do not entirely disagree,
however ByRef has a valid and useful purpose. Indeed in our example the only
reason for the existence of the argument is to be able to pass it ByRef, for
the sole purpose of returning it with a new value.

For other purposes, where there is no deliberate intention to change the
ByRef argument, it can still be worth sending ByRef where performance is an
issue. It depends slightly on the data type but in general it's faster to
send a pointer to the variable (ByRef) rather than make and send a copy of
the variable (ByVal).
Then let's agree to disagree.
Click to expand...

No problem :)

Regards,
Peter T

PS in VB/A ByRef is the default


Bernd P said:
Hello Peter,

[Not OT - w.r.t ByRef]
Then let's agree to disagree.

My basis is:
http://ei.cs.vt.edu/~cs2604/Standards/Standards.html

[Search for the first two occurances of "reference".]

Regards,
Bernd
Click to expand...
 
J

Jac Tremblay

Hi Peter,
Thanks again for your time and your quick answer.
I did understand what you explained and am now more aware of the utility of
ByRef. Thanks to Bernd and Jim who posted comments as well.
But, as I wrote in the previous answer (to Jim), I found a simple way to
solve my problem and posted the code that goes with it. It works fine and I
decided to use that solution because it is simple and sweet.
Your post was very helpful and informative.
Thanks again and have a good night.
 
K

keiji kounoike

You seem to have already solved your problem. so, you maybe don't need
this one. but just for a reference. I would use a user defined variable
instead of using global variable. Using this way, your sample code looks
like this.

Type fboo
msg As String
state As Boolean
End Type

Function fbooDataIsValid(ByVal pstrParam1 As String, _
ByVal pintParam2 As Integer) As fboo

Dim tmp As fboo

If pstrParam1 = "Hi Jim" Then
tmp.msg = "Error, my friend. You should have said Hello!."
tmp.state = False
fbooDataIsValid = tmp
Exit Function
End If

If pintParam2 < 100 Then
tmp.msg = "Error, your number is < 100."
tmp.state = False
fbooDataIsValid = tmp
Exit Function
End If

tmp.msg = "Suceed"
tmp.state = True

fbooDataIsValid = tmp

End Function

' ---
Sub TestDeJac()
Dim strText As String
Dim result As fboo

strText = "Hi Jim"
result = fbooDataIsValid(strText, 136)
If Not result.state Then
MsgBox result.msg, vbCritical, "Sofica"
Else
MsgBox "It's OK...", vbInformation, "Sofica"
End If
End Sub

keiji
 
J

Jac Tremblay

Hi Keiji,
I reread your post and found it very interesting. I think I read it too fast
the first time.
I will try some version of your code and reply later on, when a get some
pertinent code to post.
Thank you for your answer.
It is never too late to act correctly...
 
K

keiji kounoike

Hi Jac

I've completely forgotten about my post. but thanks for your having an
interest in my post. I hope you would get a useful code.

keiji
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
type mismatch error in macro in excel 0
VBA - exporting serial letters to single documents 0
Splash screen not working in hidden workbook 2
How to make a VBA Function return a value in percent format 8
Fool Proof Price Validation Custom Function 0
Convert code to lookup three criteria return fourth 15
Unexplained failure of wdFindStop at end of search 0

Top