VBA function returning multiple values?

[DM]

Hi,

I need a way to trick a function to return more than one value. Basically I
have a function which works conceptually like this (of course, my function
is more complicated):

=====================================================
Function My_Function (var1 as Double, var2 as Double, var3 as Double)

(code1)

Calc1=var1*var2
Calc2=var1*var2

(code2)

End Function
=====================================================

I need a way to "extract" both Calc1 and Calc2 in Excel. One way I was
thinking was to create two functions, but this would be a waste of
resources, since there are a lot of calculations in (code1). One the other
hand, for any updates, there are two functions to change, instead of one.
Any ideas? Thanks.

 

[Rocky McKinley]

Something like this perhaps...I wasn't sure if you wanted the "var's"
optional or not but I thought I'd leave you the option.

Function My_Function(CalcNum123 As Byte, var1 As Double, Optional var2 As
Double, Optional var3 As Double)
If CalcNum123 = 1 Then
My_Function = var1 * var2
ElseIf CalcNum123 = 2 Then
My_Function = var2 * var3
ElseIf CalcNum123 = 3 Then
My_Function = var1 * var3
End If
End Function

Sub TestIt()
MsgBox My_Function(1, 1, 2, 3)
MsgBox My_Function(2, 1, 2, 3)
MsgBox My_Function(3, 1, 2, 3)
End Sub

 

[Colo]

Hi DM,

How about this way? Using Type statment as follows...


Code
-------------------

Type MultipleValues
Calc1 As Double
Calc2 As Double
End Type

Sub Test()
With My_Function(1, 2, 3)
Debug.Print .Calc1
Debug.Print .Calc2
End With
End Sub

Function My_Function( _
var1 As Double, _
var2 As Double, _
var3 As Double _
) As MultipleValues
'(code1)
My_Function.Calc1 = var1 * var2
My_Function.Calc2 = var1 * var3
'(code2)
End Function

 

[Bob Phillips]

Here's one way

Function DoubleFunction(ByVal v1, ByRef v2)

v2 = v2 * 3
DoubleFunction = v1 + 10

End Function

Sub TestIt()
Dim var1, var2

var1 = 17
var2 = 18
MsgBox "Before: var1 = " & var1 & ", var2 = " & var2

var1 = DoubleFunction(var1, var2)
MsgBox "Before: var1 = " & var1 & ", var2 = " & var2

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Paul Robinson]

Hi DM,
You need to return a variant array. Functions can only create single
outputs, but that output can have multiple components. Modified code
would then be;

Function My_Function (var1 as Double, var2 as Double, var3 as Double)
as Variant
Dim ArrayValues(1 to 2) as Double
(code1)

ArrayValues(1)=var1*var2
ArrayValues(2)=var1*var2 'same??
MyFunction = ArrayValues 'one output
(code2)

End Function

If your var1, var2 and var3 have the values 1, 2 and 3 respectively
then the two components you require are
MyFunction(1,2,3)(1)=2
MyFunction(1,2,3)(2)=2

Note: the two other replies to your post have taken a different view
of your question and hence a different solution.

regards
Paul

 

[onedaywhen]

Bob,
Similar to a post of yours yesterday, your DoubleFunction doesn't
return a value so I'm left confused by the following constructs:

DoubleFunction = v1 + 10

var1 = DoubleFunction(var1, var2)

Can you please explain?

--

 

[Bob Phillips]

I don't really understand the question, so I will explain what is happening
and see if that helps.

First the calling routine


Basic setting variables and showing their values before being passed to my
function

Call my function with both variables as arguments

This is just to prove that both variables got changed by the function, var1
as it picks up the function's return value, var2 as it is modified within
the function.

Now the function itself

The function takes 2 arguments, v1 is passed By Value, v2 is passed By
Reference. ByVal means that a copy is passed, so that even if it is changed
within the function, when the function exits that change is lost. When you
pass a variable by reference the address of the variable is passed to the
procedure, the "actual" variable from the calling procedure, and if the
function changes it, that change remains when the function is exited. v1
and v2 here are the variables var1 and var2 in the calling procedure.
..
Simply change v2 to prove the change

Assign the return value to v1 + 10

Run it and see, you should see values of 17 and 18 before, 27 and 54
afterwards.

Does this help?

Bob

 

[DM]

Thanks.
There was a type in my message. Calc2 should have been different from Calc1
(=var1* var3 for example...)
I am a finance person, and do not have much experience withVBA, so I'm not
sure I know how to implement your suggestion.
However, I thought of a workaround which seems to work for my purpose:


Function My_Function (var1 as Double, var2 as Double, var3 as Double, var4
as integer)

(some code)

Calc1=var1*var2
Calc2=var1*var3

if var4 =1 then
My_Function=Calc1
else if var4 = 2 then
My_Function=Calc2
endif
endif
End Function


I can call the function specifying var4 as1 or 2. It may not be as efficient
since (Some code) is performed twice. I do not notice any delay though.
However, I get to maintain one function, which saves a lot of trouble.

 

[onedaywhen]

I get it. It's not that

Function DoubleFunction(ByVal v1, ByRef v2)

returns no value, as I assumed. Rather your declaration is a contraction of

Public Function DoubleFunction( _
ByVal v1 As Variant, ByRef v2 As Variant) As Variant


... looking out across the Exe towards Dartmoor.

--

 

[Bob Phillips]

I get it. It's not that

Function DoubleFunction(ByVal v1, ByRef v2)

returns no value, as I assumed. Rather your declaration is a contraction of >
Public Function DoubleFunction( _
ByVal v1 As Variant, ByRef v2 As Variant) As Variant

All functions return a value, which is whatever is loaded in to the function
name variable. If nothing is loaded, it returns an initialised value (0, "",
or null). A calling routine doesn't have to retrieve that value either.
Variant is the default.

... looking out across the Exe towards Dartmoor.

Nice!