#VALUE！尝试将UDF函数调用到另一个UDF函数时出错

Question

简单地说，我试图做的是一个简单的函数，它可以计算excel中选定单元格范围的样本方差。其效果是得到与excel函数=VAR.S((完全相同的结果

我已经在用户定义的函数中为mean进行了编码(见下文(，但在尝试将Mean1b函数调用到Var2a函数时出现了错误。如果我在方差公式中简单地将均值定义为常数，一切都会顺利进行。

我的问题是，为什么我不能简单地通过我的Var2a函数传递输入数组，这样它就会被Mean1b函数接受？如果我继续在ParamArray中工作，解决方法是什么？

Public Function Mean1b(ParamArray arr() As Variant)
Dim rtot As Double
Dim elem As Variant
Dim i As Integer
Dim count As Integer
rtot = 0 'set initial state
count = 0 'initiate count of elements in the array
For i = LBound(arr) To UBound(arr) 'loop from lower bound to upper bound of array
For Each elem In arr(i)
rtot = rtot + elem.Value
count = count + 1
Next elem
Next i
Mean1b = rtot / count
End Function
Public Function Var2a(ParamArray arr() As Variant) As Double
Dim rtot As Double
Dim elem As Variant
Dim i As Integer
Dim count As Integer
mean = Mean1b(arr)
rtot = 0
count = 0
For i = LBound(arr) To UBound(arr)
For Each elem In arr(i)
rtot = rtot + (elem.Value - mean) ^ 2
count = count + 1
Next elem
Next i
Var2a = rtot / (count - 1)
End Function

Answer 1

解决了它！原来你所要做的就是修改这个：

mean = Mean1b(arr)

到此：

mean = Mean1b(arr(0))

显然，这是在将ParamArray传递到另一个函数时所必需的