Can subroutine return multiple values?

[Jennifer]

I am trying to write a macro (Sub) that takes a fairly complex parameter string via InputBox. The results of the parameters will set 6 variables. To make the Sub code simpler, I'd like to pass the work of obtaining and processing the parameter string to another Sub and have it just return the results in the 6 variables. I can only return a single result in the Sub name. Is there another way that I can set 6 variables in the calling Sub?

I know this is a rookie question, but I have always been able to do whatever work I needed to do in the main Sub or with simple called Subs that return a single value.

Thanks

[gmayor]

A 'Function' can return a value. You can call a function multiple times as required or you can return an array or a string that can be split, whichever is more convenient. Without more detail it is pointless speculating on what approach you should take.

[gmaxey]

As Graham advises, a Function returns a value. In addition to returning an array or string that can be split, it can also return a UserDefinedType variable which can in effect appear as multiple values:


Private Type typEmpData
Name As String
Title As String
Phone As String
Email As String
ID As String
BarNumber As String
End Type

Sub DemoType()
Dim typEmp As typEmpData
typEmp = fcnDemoType("Mickey Mouse")
MsgBox typEmp.Name
MsgBox typEmp.BarNumber
End Sub
Private Function fcnDemoType(strName As String) As typEmpData
Select Case strName
Case "Mickey Mouse"
fcnDemoType.Name = strName
fcnDemoType.Title = "Head Mouse"
fcnDemoType.Phone = "8637-5309"
fcnDemoType.Email = "mmouse@disney.com"
fcnDemoType.ID = "1"
fcnDemoType.BarNumber = "12345"
End Select
End Function

[Jennifer]

As Graham advises, a Function returns a value. In addition to returning an array or string that can be split, it can also return a UserDefinedType variable which can in effect appear as multiple values:

Wow. That is very helpful. Thanks so much. I decided to return a string and then split it, but I will now look into your UserDefinedType, which I didn't know about. I can see how that could be very useful.

[Paul_Hossler]

I believe that you really mean a 'Function' and not a 'Sub' -- There is a difference


A sub ('SubCalled') can take a parameter (or 6) as input, process them, and return the result to the to the calling program (sub 'Six') in the parameters. If you pass a non-parameter (the second call to 'SubCalled') they don't get updated

A function ('FunctionCalled') can take parameters and return the result (sort of) as the function

A function ('FunctionCalled2') can do both, but as an aside I personally don't do that very often






Option Explicit

Sub Six()
Dim a As Long, b As Long, c As Long, d As Long, e As Long, f As Long

a = 1
b = 2
c = 3
d = 5
e = 7
f = 11

Call SubCalled(a, b, c, d, e, f)

MsgBox a
MsgBox b
MsgBox c
MsgBox d
MsgBox e
MsgBox f

Call SubCalled(a, b, c, 3, 4, 5)

MsgBox a
MsgBox b
MsgBox c
MsgBox d ' not updated so remains = what it was = 50
MsgBox e
MsgBox f
MsgBox FunctionCalled(a, b, c, 1, 2, -3)
a = 1
b = 2
c = 3
d = 5
e = 7
f = 11
MsgBox FunctionCalled2(a, b, c, d, e, f)

MsgBox a

End Sub


'Default call type is ByRef so it's the ADDRESS of the variable a1 that gets passed, and it's the same a1 that is updated
Sub SubCalled(a1 As Long, b1 As Long, c1 As Long, d1 As Long, e1 As Long, f1 As Long)
a1 = 10 * a1
b1 = 10 * b1
c1 = 10 * c1
d1 = 10 * d1
e1 = 10 * e1
f1 = 10 * f1
End Sub


Function FunctionCalled(a1 As Long, b1 As Long, c1 As Long, d1 As Long, e1 As Long, f1 As Long) As Double
FunctionCalled = a1 * b1 * c1 * d1 * e1 * f1
End Function


Function FunctionCalled2(a1 As Long, b1 As Long, c1 As Long, d1 As Long, e1 As Long, f1 As Long) As Double
FunctionCalled2 = a1 * b1 * c1 * d1 * e1 * f1
a1 = -12345

End Function





So as example of processing an input string into 6 output strings




Sub test()
Dim s As String
Dim a As String, b As String, c As String, d As String, e As String, f As String

s = InputBox("string at least 6 char", "demo", "ABCDEF")

Call SplitString(s, a, b, c, d, e, f)

MsgBox a
MsgBox b
MsgBox c
MsgBox d
MsgBox e
MsgBox f

End Sub

Sub SplitString(s As String, a As String, b As String, c As String, d As String, e As String, f As String)
a = Mid(s, 1, 1)
b = Mid(s, 2, 1)
c = Mid(s, 3, 1)
d = Mid(s, 4, 1)
e = Mid(s, 5, 1)
f = Mid(s, 6, 1)

End Sub

[Jennifer]

I believe that you really mean a 'Function' and not a 'Sub' -- There is a difference
Yes, I meant a function. That was a careless mistake. I do know the difference.



Default call type is ByRef so it's the ADDRESS of the variable a1 that gets passed, and it's the same a1 that is updated
Sub SubCalled(a1 As Long, b1 As Long, c1 As Long, d1 As Long, e1 As Long, f1 As Long)
a1 = 10 * a1
b1 = 10 * b1
c1 = 10 * c1
d1 = 10 * d1
e1 = 10 * e1
f1 = 10 * f1
End Sub

Thanks for reminding me of that. And I believe that is why most good programming books recommend passing parameters with the "ByVal" option to avoid unintended changes to the variables in the calling routine, right?

I decided to go with having the function return a string that the calling routine can split. It's cleaner.

[gmaxey]

... most good programming books..." Personally, I wouldn't know because I've never read one good o bad ;-). I rarely use ByVal and since ByRef is the default, it would seem that it is the most used. Personal preference I suppose.

In Paul's function example maybe a ParamArray has a home:


Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim a As Long, b As Long, c As Long, d As Long, e As Long, f As Long
a = 1: b = 2: c = 3: d = 5: e = 7: f = 11
MsgBox fcnBasicCalc("A", a, b, c, d, e, f)
lbl_Exit:
Exit Sub
End Sub
Function fcnBasicCalc(strRouter As String, ParamArray varTerms() As Variant) As Single
Dim lngIndex As Long
For lngIndex = 0 To UBound(varTerms)
Select Case strRouter
Case "A": fcnBasicCalc = fcnBasicCalc + varTerms(lngIndex)
Case "M"
If lngIndex = 0 Then
fcnBasicCalc = varTerms(lngIndex)
Else
fcnBasicCalc = fcnBasicCalc * varTerms(lngIndex)
End If
End Select
Next
lbl_Exit:
Exit Sub
End Function

[Paul_Hossler]

Thanks for reminding me of that. And I believe that is why most good programming books recommend passing parameters with the "ByVal" option to avoid unintended changes to the variables in the calling routine, right?

I decided to go with having the function return a string that the calling routine can split. It's cleaner.


ByVal does eliminate the hard to debug risk of a sub changing something farther up the calling chain

However, ByVal does make a copy of the parameters to the call stack and that could take time if it's done a lot or the passed structures are large

My personal preference is to pass strings and objects and UDTs ByRef to a Sub if I'm operating on them in the , or pass them ByRef to a function if I'm creating another.



https://msdn.microsoft.com/en-us/library/office/gg278504.aspx





ByVal

Optional. Indicates that the argument is passed by value (https://msdn.microsoft.com/en-us/library/office/gg264568.aspx).



ByRef

Optional. Indicates that the argument is passed by reference (https://msdn.microsoft.com/en-us/library/office/gg264568.aspx). ByRef is the default in Visual Basic.





I like Greg's UDT in #3 since it put all the input string processing inside a sub, and returns processed results in a UDT variable with each component identified. To me, (and very much it's only my opinion) that's cleaner than returning a string that the calling prog has to split

[Jennifer]

These are all good points to consider. Thanks for the information. Hey, you guys should write a book! :yes

[gmaxey]

Paul,

I believe that you really meant "since it put all the input string processing inside a 'function'" and not a 'sub' -- There is a difference. See post #5

[Paul_Hossler]

Paul,

I believe that you really meant "since it put all the input string processing inside a 'function'" and not a 'sub' -- There is a difference. See post #5

Ahh, what the heck does he know :devil2:

But you're right of course -- I was a tad sloppy in my terms

[gmaxey]

He knows very little. That's what he knows!

Thanks for your input. Interesting and I believe a helpful thread.

[Paul_Hossler]

'he' = 'me'