Returning Multiple Values from an Excel Function Macro

ID: Q71946

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("range",64)
   A4:     {=SET.NAME("result",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 "result" (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, "result," 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("start",1)
   A4:     =ARGUMENT("finish",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

REFERENCES

"Microsoft Excel User's Guide, Book 2." Version 4.0, pages 207-208.

"Microsoft Excel User's Guide." Version 3.0, pages 571-572.

"Microsoft Excel Functions and Macros." Version 2.1x, pages 179-180.

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 4.0

Keywords :
Version :
Platform :
Issue type :