VBA 定义能返回数组公式的自定义函数

返回一个变量大小结果数组的方法

此方法返回基于一个参数范围的值的数组。结果数组的大小具体取决于参数数组中的元素数量波动。例如对于假定您要创建一个范围中的每个值乘以 100 的函数。下面的自定义函数接受作为其参数的数组 （某一范围的值）：

      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
      ' the array and multiply each element by 100.
      ' if not more than one element then temp is multiplied by 100.
      If IsArray(temp) Then
         For i = 1 To UBound(temp, 1)
            For j = 1 To UBound(temp, 2)
               temp(i, j) = temp(i, j) * 100
            Next j
         Next i
      Else
         temp = temp * 100
      End If
      Multiply_Range = temp
   End Function
				

若要用于自定义函数中的单元格 A1:A4 中输入以下数据：

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

选择范围 B1:B4 并以数组形式输入以下公式：

=Multiply_Range(A1:A4)

注意： 上述公式必须以数组公式的形式输入。若要将公式作为数组公式，在 Excel 中输入 windows，请按 CTRL + SHIFT + ENTER。在 Excel 中为 Macintosh，请按命令 + ENTER。

结果将是：

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

 

要返回的固定大小数组结果的方法

此方法从自定义函数返回多个值假定您在所得数组中已经输入固定的个数的元素。

下面的自定义函数接受起始时间和结束时间，并返回它们在 3 行 1 列数组之间的小时、 分钟和秒数。 >

   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.Transpose(Array(hours, minutes, seconds))
   End Function
				

若要用于此自定义的函数 （例如对于 A1 和 A2 下面） 的两个单元格中输入开始时间和完成时间。

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

然后，突出显示的列 （例如对于到 A5 A3） 中的三个单元格，并以数组公式的形式输入以下内容：

=Elapsed_Time(A1,A2)

注意： 上述公式必须以数组公式的形式输入。若要将公式作为数组公式，在 Excel 中输入 windows，请按 CTRL + SHIFT + ENTER。在 Excel 中为 Macintosh，请按命令 + ENTER。

该结果将显示，如下所示：

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

如果您希望该函数输入水平代替垂直的单元格区域，更改 Elapsed_Time 该行在以读取行：

elapsed_Time = 阵列 （小时、 分钟、 秒）