VBA中的用户定义功能不是“阵地”的。 [英] User defined function in VBA not "array-firendly"

问题描述

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

I have the following user-defined functions created:

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

何时我尝试使用电子表格

When I tried to run the formula in spreadsheet for various values of "Lower" and "Upper" (for summation through 1 to n), using

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

结果（当我评估公式时）不会在数组中返回值1到8。它只是返回值1。

The result (when I evaluate the formula), does not return the values 1 through 8 within the array. It just returns the value 1.

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

Where is the mistake? Thanks for the help.

推荐答案

让我们举一个可以处理数组的UDF示例。此UDF具有两个参数。第一个是x作为Double值。另一个是变体。可以是单个值，也可以是数组或范围。

Lets have an example of a UDF which can deal with arrays. This UDF takes two parameters. The first is x as a Double value. The other is a Variant. It can be either a single value or an array or a 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

= arrayFriendlyUDF（2，A3）如果 A3 包含3

= SUM（arrayFriendlyUDF（2，{1 ; 2; 3; 4}））结果= 20 =总和n = 1到4（2 * n）

=SUM(arrayFriendlyUDF(2,{1;2;3;4})) result = 20 = sum n=1 to 4 (2 * n)

= SUM（arrayFriendlyUDF（2，A1：D1））如果 A1：D1 包含{1,2,3,4 }

=SUM(arrayFriendlyUDF(2,A1:D1)) result = 20 if A1:D1 contains {1,2,3,4}

= SUM（arrayFriendlyUDF（2，A1：A4）） result = 20 if A1：A4 包含{1; 2; 3; 4}

=SUM(arrayFriendlyUDF(2,A1:A4)) result = 20 if A1:A4 contains {1;2;3;4}

{= SUM（arrayFriendlyUDF（2， ROW（1：4）））} 结果= 20

{= SUM（arrayFriendlyUDF（2，COLUMN（ A：D）））} 结果= 20

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

The last both formulas must be array formulas confirmed using Ctrl+Shift+Enter.

使用UDF方法仍不清楚。如评论中所述，示例单元格公式缺少参数 Step ，该参数不是可选的。即使它可以作为数组公式工作（我们现在可以实现），但由于 Lower 和 Upper 始终使用 ROW（INDIRECT（ 1：8））相等。

Still not clear with your UDF approach. As said in the comment your sample cell formula lacks the parameter Step which is not optional. And even if it would work as an array formula - what we could achieve now - the result would be an array of 0s always since Lower and Upper are always equal using ROW(INDIRECT("1:8")) for both.

这篇关于VBA中的用户定义功能不是“阵地”的。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！