VBA defines a custom function that can return an array formula

A method that returns an array of variable size results

This method returns an array of values based on a parameter range. The size of the resulting array depends on the number of elements in the parameter array fluctuations. For example, assume that you want to create a function that multiplies each value in a range by 100. The following custom function accepts an array (a range of values) as its arguments:

      Function Multiply_range (myrange as Object) As Variant      Dim temp As Variant      Dim i As Integer, j As Integer      Temp = myrange. Value ' Creates a copy of the values in myrange      ' if more than one element then loop through both dimensions of      ' t He array and multiply each element by.      ' If not more than one element then the temp is multiplied by.      If IsArray (temp) then-         i = 1 to UBound (temp, 1) for            j = 1 to UBound (temp, 2)               temp (i, j) = Temp (i, j) * 10 0            Next J         Next i      Else         temp = temp *      end If      multiply_range = temp   End Function

To use the Custom function, enter the following data in cell A1:A4:

A1:5
A2: 3
A3: 1
A4: 2

Select the range b1:b4 and enter the following formula as an array:

=multiply_range (A1:A4)

Note: The above formula must be entered as an array formula. To use a formula as an array formula and enter windows in Excel, press CTRL + SHIFT + ENTER. For Macintosh in Excel, press COMMAND + ENTER.

The result will be:

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

method to return the result of a fixed-size array

This method returns multiple values from a custom function assuming that you have entered a fixed number of elements in the resulting array.

The following custom function accepts the start and end times and returns the number of hours, minutes, and seconds between their 3 rows and 1 column arrays. >

   Function Elapsed_time (Start, finish as Date) as Variant      Dim hours, minutes, seconds as Integer      hours = Hour (finish -start)      minutes = Minute (finish-start)      seconds = Second (finish-start)      elapsed_time = Application.transpo SE (Array (hours, minutes, seconds))   End Function

To use this custom function (for example, A1 and A2 below), enter a start time and a finish time in two cells.

A1:1:00:00
A2: 6:49:34

Then, highlight the columns (for example, to A5 A3) in three cells, and enter the following as an array formula:

=elapsed_time (A1,A2)

Note: The above formula must be entered as an array formula. To use a formula as an array formula and enter windows in Excel, press CTRL + SHIFT + ENTER. For Macintosh in Excel, press COMMAND + ENTER.

The results are displayed as follows:

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

If you want the function to enter horizontally instead of a vertical cell range, change the Elapsed_time line to read the row:

elapsed_time = Array (hours, minutes, seconds)

VBA defines a custom function that can return an array formula