excel - UDF 随机数生成器类型不匹配

Question

我已经尝试了近一个小时来解决这个问题，但似乎毫无进展。

基本上，我想尝试一个更好的随机数生成器，并找到了我想尝试在程序中实现的代码

 Function RandNorm(Optional mean As Double = 0, _
              Optional Dev As Double = 1, _
              Optional Corr As Double = 0, _
              Optional bVolatile As Boolean = False) As Double()


Randomize
Dim z(0 To 1)   As Double
Dim U           As Double
Dim V           As Double
Dim S           As Double

If bVolatile Then Application.Volatile

Do
    U = 2 * [rand()] - 1
    V = 2 * [rand()] - 1
    S = U * U + V * V
Loop Until S < 1

S = Sqr(-2 * Log(S) / S)
z(0) = Dev * U * S + mean
z(1) = Dev * V * S + mean

If Corr <> 0 Then z(1) = Corr * z(0) + Sqr(1 - Corr ^ 2) * z(1)
RandNorm = z
End Function

如您所见，该函数返回一个数字。当在 Excel 中运行时，例如 =RandNorm()，一切都很好。但是当运行在这样一个简单的代码中时:

 Sub test()

    Dim x() As Double, i As Long

    ReDim x(1 To 10, 1 To 1)
    For i = 1 To 10
          x(i, 1) = RandNorm
    Next i

    Range("A1:A10") = x
End Sub

它给了我“类型不匹配”错误，尽管它已被定义为As Double。如果我在 Sub 中定义 RandNorm As Double，它会给我 0。但是，如果我将 x(i, 1) 替换为 Cells(i, 1)，它就可以工作。

该函数也位于其自己的模块和子模块中。

我错过了什么？任何帮助将非常感激。提前致谢!

Answer 1

Double 与 Double() 不同:RandNorm 返回一个数组。当在单个单元格中用作 UDF 时，RandNorm 返回该数组的第一个 元素。例如，考虑以下函数。

Function foo() As Double()
    Dim z(0 To 1) As Double
    z(0) = 1
    z(1) = 2
    foo = z
End Function

当在单个单元格中用作 UDF 时，=foo() 将始终返回 1。您需要使用 Ctrl+Shift+Enter 在 2 个单元格中将其作为数组公式输入，以显示第二个元素 2 .

简单的修复方法可能是将 Double() 更改为 Double 并将 RandNorm = z 更改为 RandNorm = z(0)。

Function RandNorm(Optional mean As Double = 0, _
              Optional Dev As Double = 1, _
              Optional Corr As Double = 0, _
              Optional bVolatile As Boolean = False) As Double
    ....
    If Corr <> 0 Then z(1) = Corr * z(0) + Sqr(1 - Corr ^ 2) * z(1)
    RandNorm = z(0)
End Function

尽管很难说这就是您正在寻找的东西 - 因为您实际上忽略了 z(1)。