VBA 中的用户定义函数不"array-firendly"

Question

我创建了以下用户定义的函数：

Public Function LNt(LMiu As Double, LSigma As Double, t As Double)
Application.Volatile
LNt = Application.WorksheetFunction.NormDist(Log(t) / Log(2.71828182845905), LMiu, LSigma, False) / t
End Function

和

Public Function IntLNt(LMiu As Double, LSigma As Double, Lower As Double, Upper As Double, Step As Integer)
Application.Volatile
Dim Delta As Double
Dim I As Double
Delta = ((Log(Upper) / Log(2.71828182845905)) - (Log(Lower) / Log(2.71828182845905))) / Step
I = ((Log(Upper) / Log(2.71828182845905)) - (Log(Lower) / Log(2.71828182845905))) * (LNt(LMiu, LSigma, Lower) + LNt(LMiu, LSigma, Upper)) / (2 * Step)
For n = 2 To Step
I = I + LNt(LMiu, LSigma, Lower + Delta * (n - 1)) * Delta
Next
IntLNt = I
End Function

当我试图在电子表格中运行"Lower"one_answers"Upper"(通过1到n求和)的各种值的公式时，使用

=SUMPRODUCT(IntLNt(LMiu,LSigma,ROW(INDIRECT("1:8")),ROW(INDIRECT("1:8"))))

结果(当我计算公式时)不会返回数组中的值1到8。它只返回值1。

错误在哪里？谢谢你的帮助。

Answer 1

让我们举一个可以处理数组的UDF的例子。这个UDF有两个参数。第一个是x作为Double值。另一种是变体。它可以是单个值，也可以是数组或Range。

Public Function arrayFriendlyUDF(x As Double, vParam As Variant) As Variant
'function which can get variant parameter vParam
'either a single value or a range or an array literal
'will return a single value or an array of results
'it will only accept double values as single value, in range or in array literal
Dim adParam As Variant, dParam As Double, aParam() As Double
Dim aResult() As Double
Dim i As Integer
adParam = vParam
'if vParam is a single value, then adParam also is a single value
'if vParam is a range, then adParam is a variant array from this range's values
'if vParam is a array literal, then adParam is a variant array from this array literal
If TypeName(adParam) = "Variant()" Then 'we have a variant array
'it could be one dimensional (row vector) or two dimensional (column vector)
'we will only process one dimensional array, so we must transform if needed
i = 0
For Each vParam In adParam
ReDim Preserve aParam(i)
aParam(i) = vParam
i = i + 1
Next
ElseIf TypeName(adParam) = "Double" Then 'we have a single double value
ReDim aParam(0)
aParam(0) = adParam
End If
'now we have an array (one dimensional) in any case
For i = LBound(aParam) To UBound(aParam)
ReDim Preserve aResult(i)
aResult(i) = x * aParam(i) 'this is the function's operation. simply x * {vParam} in this case
Next
If UBound(aResult) = 0 Then
'if only one result
arrayFriendlyUDF = aResult(0)
Else
'return the array of results
arrayFriendlyUDF = aResult
End If
End Function

此UDF可用作：

=arrayFriendlyUDF(2,3)结果=6

如果A3包含3个，则=arrayFriendlyUDF(2,A3)结果=6

=SUM(arrayFriendlyUDF(2,{1;2;3;4}))结果=20=总和n=1到4(2*n)

如果A1:D1包含｛1,2,3,4｝，则=SUM(arrayFriendlyUDF(2,A1:D1))结果=20

如果A1:A4包含｛1；2；3；4｝，则=SUM(arrayFriendlyUDF(2,A1:A4))结果=20

{=SUM(arrayFriendlyUDF(2,ROW(1:4)))}结果=20

{=SUM(arrayFriendlyUDF(2,COLUMN(A:D)))}结果=20

最后两个公式必须是使用Ctrl+Shift+Enter确认的数组公式。

---

仍然不清楚您的UDF方法。正如评论中所说，您的示例单元格公式缺少参数Step，这不是可选的。即使它是一个数组公式——我们现在可以实现的——结果也总是0的数组，因为Lower和Upper总是相等的，使用ROW(INDIRECT("1:8"))。