Output from a procedure

[Bob]

Hi everyone:

I am writing a VBA code as a function in excel. The function has two
arguments, Arg1 which is the input to the function, and Arg2 which is an
output from the function along with the function name. In excel, I call the
function in the formula bar as:
=MyFunct(Arg1, Arg2)
Now, in the cell that contains the function, the value of the function is
returned. However, I was wondering if there is any way of also getting the
output value from Arg2. Of course, one way is to have the user send in a
cell number, and inside the function to return that value. But, I was
wondering may be there is a better way.

I appreciate all your help.

Bob

 

[Karl E. Peterson]

I am writing a VBA code as a function in excel. The function has two
arguments, Arg1 which is the input to the function, and Arg2 which is an
output from the function along with the function name. In excel, I call the
function in the formula bar as:
=MyFunct(Arg1, Arg2)
Now, in the cell that contains the function, the value of the function is
returned. However, I was wondering if there is any way of also getting the
output value from Arg2. Of course, one way is to have the user send in a
cell number, and inside the function to return that value. But, I was
wondering may be there is a better way.

To clarify, you want to put two values in one cell?

 

[Gary''s Student]

If your function is called from VBA then your solution makes sense. If your
function is called from the worksheet, then there is a better way.

In the worksheet, functions usually can only put a value in a single cell.
However, we can create a UDF that returns an array. By entering the UDF as
an array on the worksheet, we can get each output in a separate cell. Here
is an example:

Function AnyThing(r As Range) As Variant
Dim v() As Variant
ReDim v(0 To 2)
v(0) = "qwerty"
v(1) = "asdfg"
v(2) = "poiuy"
AnyThing = v
End Function

In the worksheet select A1 thru C1 and enter:
=AnyThing(B9)

If we install with CNTRL-SHFT-ENTER rather than just ENTER, we see:
qwerty asdfg poiuy

For a better discussion of this topic see:

http://www.cpearson.com/excel/returningarraysfromvba.aspx

 

[Bob]

Hi Karl:

No, I want to put two values in two different cells. One value is returned
by the function, and the second is returned by an argument.

Bob

 

[Bob]

Thanks Gary. I will try your solution. However, I was hoping to stay away
from array.

Bob

 

[Karl E. Peterson]

No, I want to put two values in two different cells. One value is returned
by the function, and the second is returned by an argument.

I'm not aware of any "practical" solutions for such a problem.

Not sure the array reply addresses it very well, but if it sounds like it'll meet
the needs, cool!

Sorta sounds to me like two distinct functions are called for.