Passing Values Out of Subroutine?

  • Thread starter Thread starter Joe Delphi
  • Start date Start date
J

Joe Delphi

Hi,

I want to implement a subroutine that calculates and returns 5 values,
but I don't know the syntax for passing data OUT of the subroutine.

I want to do something like this:

Private Sub Calc5Things(InputValue As Integer, Output1 As Currency,
Output2 As Integer, Output3 As Double, Output4 As Double, Output5 As Double)
...

but I don't know how to tell VBA that the last five parameters are
output parameters as opposed to input parameters.

How to do this?


JD
 
By default, your example as written also means that the input values can be
modified, and if they are, the returning program will see this.

Eg:

InputValue = 5

Call Calc5Things(InputValue As Integer, Output1 As Currency,
Output2 As Integer, Output3 As Double, Output4 As Double,
Output5 As Double)

msgbox "input vlaue now is = " & InputValue

......
......

sub Private Sub Calc5Things(InputValue As Integer, Output1 As Currency,
Output2 As Integer, Output3 As Double, Output4 As Double, Output5 As
Double)


InputValue = 6

end sub

If you note the example code snip above, you will notice that I changed the
value of inputvalue to 6, and calling code value passed WOULD reflect this,
and would be modified..

So, by default, all parameters passed to a sub routine are by ref
(references, and a COPY of the variable is NOT made).

You can EXPLICIT declare values as passed only

Eg:

sub Private Sub Calc5Things(ByVal InputValue As Integer, Output1 As
Currency,
Output2 As Integer, Output3 As Double, Output4 As Double, Output5 As
Double)

InputValue = 6

end sub


If you declare any parramter as ByValue, then if it is modefied in the
subroutine code, it will NOT be refleced, or retunred back to the calling
code.

So, in your original example, all of the values, including the InputValue
can be modified, and IF THEY are modified, this will be reflected in the
variables used by the code that calls the sub.

For read ability, I suppose then your example, here what you actually
wanted, and this is more "documentation" approach to the code:


sub Private Sub Calc5Things(ByVal InputValue As Integer, _
ByRef Output1 As Currency, _
ByRef Output2 As Integer, _
ByRef Output3 As Double,
ByRef Output4 As Double,
ByRef Output5 As Double)

However, since EVERYONE knows that ByRef is the default, then likey it is
better (and less work) to explicit use the ByVal when you need to...and not
worry using ByRef, which is used most/rest of the time....
 
Hi Joe

You don't need to do anything :-)

There are two ways to pass arguments to a procedure in Visual Basic: ByVal
and ByRef. The default in VB6 and VBA is ByRef. (Incidentally, this
changed with VB.Net, but that's another story)

With ByVal, a copy of the argument *value* is passed, so if you change the
value of the argument inside the procedure then that change won't affect the
value that was passed in the calling procedure.

With ByRef, a *reference* to the actual argument (the memory address) is
passed, so any change within the procedure is seen back in the calling code.

(Note that is you pass a constant or an expression to a ByRef argument then
a reference to a copy of the value is actually passed)

So, provided you pass variable names for your output arguments, the values
that are assigned to those arguments inside your procedure will be in the
respective variables once the procedure returns.
 
Joe said:
Hi,

I want to implement a subroutine that calculates and returns 5 values,
but I don't know the syntax for passing data OUT of the subroutine.

I want to do something like this:

Private Sub Calc5Things(InputValue As Integer, Output1 As Currency,
Output2 As Integer, Output3 As Double, Output4 As Double, Output5 As Double)
...

but I don't know how to tell VBA that the last five parameters are
output parameters as opposed to input parameters.

How to do this?


JD
Click to expand...

Here's one way to do it:

'-----Begin Module Code-----
Public Function Return5Results(InputValue As Integer) As Variant
Dim varTemp As Variant

varTemp = Array("0", "1", "2", "3", "4")
Select Case InputValue
Case 1:
varTemp(0) = 1
varTemp(1) = "Two"
varTemp(2) = "3"
varTemp(3) = 4
varTemp(4) = "6 - 1"
Case 2:
varTemp(0) = 2
varTemp(1) = "Three"
varTemp(2) = "4"
varTemp(3) = 5
varTemp(4) = "7 - 1"
End Select
Return5Results = varTemp
End Function
'-------End Module Code-----

'---Begin Test Form Code-----
Dim varOutput As Variant

varOutput = Return5Results(1)
varOutput = Return5Results(1)
MsgBox (Eval(varOutput(4)))
varOutput = Return5Results(2)
MsgBox (varOutput(1))
MsgBox (Format(varOutput(0), "$#0.00"))
'-----End Test Form Code-----

Result:
5
Three
$2.00

James A. Fortune
(e-mail address removed)

Arkansas produces more than twice the rice of any other state:
http://usda.mannlib.cornell.edu/usda/current/RCS-yearbook/RCS-yearbook-11-28-2005.pdf
 
Back
Top