在VBA中生成具有正态分布的随机数-运行时错误"1004" [英] generating random number with normal distribution in VBA- Runtime error ‘1004’

问题描述

我在用VBA生成具有正态分布的随机数时遇到问题.我正在研究NSGAII.我使用"Application.WorksheetFunction.Norm_Inv(Rnd，均值，偏差)"生成具有正态分布的随机数.但是我这个错误引发了:

I have problem with generating random numbers with normal distribution in VBA. I’m working on NSGAII. I use "Application.WorksheetFunction.Norm_Inv (Rnd, Mean, Deviation)" to generate random numbers with normal distribution. But I this error raises:

运行时错误"1004":无法获取worksheetfunction类的Norm_Inv属性

如何解决此错误?

我已经在另一个简单的宏中使用了此代码，并且可以正常工作.但是在NSGAII代码中使用此代码会出现错误！(在NSGAII代码中定义并使用了大量变量(双精度，长整型，布尔型等)和2D数组，它由一些while，for等循环组成.

I’ve already used this code in another simple macro and it works. But by using this code in NSGAII code there is an error! (Large number of variables (double, long, Boolean, etc.) and 2D arrays are defined and used in NSGAII code and it consist of some do while, for, etc. loops)

我在"Function GenerateNormRand()As Double"行使用了断点.当我中断并继续(逐步运行代码)时，没有任何错误！但是，如果我不这样做，就会出现错误.

Option Explicit
Function Mutation () As Variant
    .
    .
    .
    Dim RandomNumber As Double
    RandomNumber = GenerateNormRand ()
    .
    .
    .
End Function

Function GenerateNormRand () as double
    Dim myrand As Double
    randomize
    myrand = Application.WorksheetFunction.Norm_Inv(Rnd, 0, 5)
    GenerateNormRand = myrand
End Function

即使在每个输入和输出变量上都明确声明为Double，GenerateNormRand = Application.WorksheetFunction.Norm_Inv ...行也会引发运行时错误1004:

Even with explicit declaration of Double on every input and output variable the line GenerateNormRand = Application.WorksheetFunction.Norm_Inv ... raises runtime error 1004:

Sub TestThisFunction()
    MsgBox GenerateNormRand
End Sub

Function GenerateNormRand() As Double
    Randomize
    GenerateNormRand = Application.WorksheetFunction.Norm_Inv( _
        CDbl(Rnd), CDbl(0), CDbl(5))
End Function

推荐答案

您的问题描述似乎很神秘，但是如果要绕过工作表功能，则可以使用

Your problem description seems mysterious, but if you want to bypass the worksheet function, you could use the Box-Muller transform to generate your own normal random variables in pure VBA:

Function RandNorm(Optional mean As Double = 0, Optional sd As Double = 1) As Double
    Dim r1 As Double, r2 As Double, s As Double
    r1 = Rnd()
    If r1 = 0 Then r1 = Rnd() 'no danger of two zeros in a row in rnd()
    r2 = Rnd()
    s = Sqr(-2 * Log(r1)) * Cos(6.283185307 * r2) '6.28 etc. is 2*pi
    RandNorm = mean + sd * s
End Function

这篇关于在VBA中生成具有正态分布的随机数-运行时错误"1004"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！