☰

在 VBA 中使用数组公式

tag-icon • tag-icon vba worksheet-function
在 VBA 中使用数组公式

工作表中的 Excel 数组函数很有用。例如，为了计算数组 A1:A5 的 sin 值的平均值，我输入了以下内容，效果很好：

{=AVERAGE(SIN(A1:A5))}

现在我想在 VBA 中创建一个完成该工作的函数，例如：

Function MyFunction(r As Variant) As Variant     ' r is the range, e.g. A1:A5 as above  
    MyFunction = WorksheetFunction.Average(Sin(r))  
End Function

此函数应产生单个值，但它不起作用（无论我在 Excel 单元格中将其作为普通公式或数组公式输入）。我认为问题可能是 VBA sin 函数与工作表 sin 函数不同，但用 WorksheetFunction.Sin 替换 sin 也不起作用。

当然，可以找出值的数量，编写一个循环将sin(r[i=1..n])值存储在临时数组变量中，然后计算平均值，但我正在寻找更优雅、更高效的解决方案。

答案1

这里的问题是该Sin函数需要一个值作为参数，而不是一个数组

请注意，VBA 函数Sin与工作表函数不同Sin（并且Sin不是对象的成员WorkSheetFunction，因此WorksheetFunction.Average(WorksheetFunction.Sin(r))也不起作用）

如果你将 udf 更改为

Function MyFunction(r As Variant) As Variant     ' r is the range, e.g. A1:A5 as above  
    MyFunction = WorksheetFunction.Average(r)  
End Function

您将获得指定范围的平均值，这表明 udf 可以处理数组数据。

如果你正在寻找一种有效的方法，我的建议是尽可能坚持使用本机函数（数组或其他方式），作为对 udf 的调用，任何 udf，比本机函数慢得多。

相关内容