Microsoft KB Archive/71946

{|
 * width="100%"|

Returning Multiple Values from an Excel Function Macro

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for OS/2, versions 2.2, 2.21, 3.0

-

SUMMARY
There are two different methods of returning multiple values from an Excel function macro. One works for returning a variable-sized array and the other works for returning a fixed-size array.

Method 1: Variable Size Result Array
This first method returns an array of values based on an argument range. The resulting array will fluctuate in size depending on the number of elements in the argument array. For example, assume that you want to create a function macro that will multiply each value in a range by 100. The following function macro will accept an array (a range of values) as its argument:

  A1:     Multiply_Function A2:    =RESULT(64) A3:    =ARGUMENT(&quot;range&quot;,64) A4:    {=SET.NAME(&quot;result&quot;,range*100)} A5:    =RETURN(result)

To return a variable length array from a function macro, the array must be given a name. The SET.NAME function names the resulting array &quot;result&quot; (be sure to enter the SET.NAME function with CTRL+SHIFT+ENTER.) The Result command establishes that the function macro will return an array (type 64). The name of the result array, &quot;result,&quot; is used in the RETURN function.

To use the function macro, assume you have the following values on a worksheet in column A and that you want to place the formula for your new function macro in column B. Highlight cells B1:B4 and enter the following formula (be sure to enter the formula with CTRL+SHIFT+ENTER and do not include the {}s):

  A1:     5       B1:     {=MACRO1.XLS!Multiply_Function(A1:A4)} A2:    3       B2:     {=MACRO1.XLS!Multiply_Function(A1:A4)} A3:    1       B3:     {=MACRO1.XLS!Multiply_Function(A1:A4)} A4:    2       B4:     {=MACRO1.XLS!Multiply_Function(A1:A4)}

The result will be:

  A1:     5       B1:     500 A2:    3       B2:     300 A3:    1       B3:     100 A4:    2       B4:     200

Method 2: Fixed Size Result Array
The second method for returning multiple values from a function macro assumes a fixed number of elements in the resulting array.

The following macro will accept a starting time and an ending time and return the number of hours, minutes, and seconds between them in a 1 by 3 array.

  A1:     Time_Function A2:    =RESULT(64) A3:    =ARGUMENT(&quot;start&quot;,1) A4:    =ARGUMENT(&quot;finish&quot;,1) A5:    =HOUR(finish-start) A6:    =MINUTE(finish-start) A7:    =SECOND(finish-start) A8:    =RETURN(A5:A7)

Once again, the RESULT function instructs the function macro to return an array of values. However, the difference here is that the result will always have three values, so the RETURN function refers to these three cells as the return value. It is not necessary to name the array in this case.

To use this macro, enter a starting time and a finish time in two cells. Then, highlight three cells in a column and enter the following array formula (remember to not include the {}s and enter the formula with CTRL+SHIFT+ENTER):

  A1:     1:00:00 A2:    6:49:34 A3:    {=MACRO1.XLS!Time_Function(A1,A2)} A4:    {=MACRO1.XLS!Time_Function(A1,A2)} A5:    {=MACRO1.XLS!Time_Function(A1,A2)}

The result will appear as follows:

  A1:     1:00:00 A2:    6:49:34 A3:    5 A4:    49 A5:    34