1

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:

Runtime error ‘1004’: Unable to get the Norm_Inv property of the worksheetfunction class

How can I fix this error?

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)

I used break point at line "Function GenerateNormRand() As Double". when I break and continue (run code step by step), there is No Any Error! But when I don't, error raises. 

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

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
AmirAbbas
  • 11
  • 1
  • 5
  • Error 1004 can be caused by wrong variable declaration or senseless variables (see e. g. here: https://stackoverflow.com/a/12009904/10908769), but as the error is still there after complete declaration of input and output, I don't know why it still raises. – Asger Jan 27 '19 at 15:48
  • Can you paste an additional code line, where `GenerateNormRand` is called? If in that line some additional variables are included, then please also paste, how they are declared. Also the declaration of the returning result's value is of interest. – Asger Jan 27 '19 at 18:50
  • I am almost sure that it's not related to wrong variable declaration or senseless variables. because NO Error raises by using break point at line "Function GenerateNormRand() As Double" and continue after each pause (Break). – AmirAbbas Jan 28 '19 at 11:37
  • Please give `Mutation()` an `As Double` or whatever data type is the result. Please use `Option Explicit` as the first line above each module to force an explicit declaration of everything to prevent typos like `myrand` vs. `rand`. – Asger Jan 28 '19 at 11:50
  • I've already used `option explicit` and `Mutation () as Variant` – AmirAbbas Jan 28 '19 at 11:59
  • Then please change your example to your actual code (and delete the old part or at least correct the typo). – Asger Jan 28 '19 at 12:03
  • Done :)........ – AmirAbbas Jan 28 '19 at 12:14
  • 1
    `Rnd()` [returns](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/rnd-function) `[0, 1)`. `Norm_Inv` [requires](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.norm_inv) `(0, 1)`. Also, for the sake of it, [try](https://stackoverflow.com/a/27302888/11683) `Application.Norm_Inv` instead. – GSerg Jan 28 '19 at 12:20
  • 1
    I've used `GenerateNormRand()` in a loop millions of time without error. Something else is going on with your program. Please give a [mcve] – John Coleman Jan 28 '19 at 12:26
  • Calling randomize within a tight loop is bad practice. See [this](https://stackoverflow.com/a/41059728/4996248) for an explanation. Just call `Randomize` once, in the main code. In that case, you will need to guard against `rnd()` being zero (which will happen on average about once every 17 million calls) – John Coleman Jan 28 '19 at 14:25
  • @JohnColeman I'm confused! I tried to write minimal code for reproducing the problem. But little code has no error and problem! Why?! I also had used `Randomize` several times. I edited my code and used `Randomize` just once in main code. Thanks. – AmirAbbas Jan 28 '19 at 19:20

1 Answers1

2

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
John Coleman
  • 51,337
  • 7
  • 54
  • 119
  • It's useful. Thanks a lot in advance. – AmirAbbas Jan 28 '19 at 11:40
  • I tweaked the code to avoid evaluating `Log(0)` since as @GSerg points out, `rnd() = 0` is a possibility. – John Coleman Jan 28 '19 at 15:17
  • `no danger of two zeros in a row in rnd()` - why? – GSerg Jan 28 '19 at 16:42
  • 1
    @GSerg Good question. If [this description](https://bytes.com/topic/access/insights/964786-vba-rnd-function-bad-what-use-instead) of `rnd()` is accurate, the value after `0` can't be `0` but is instead `12820163/2^24 = 0.7614` I just ran a simple test which printed the value of `rnd` just after `0` (by using `If Rnd() = 0 Then Debug.Print Rnd()` in a big loop), and it was `0.7641413` as expected. – John Coleman Jan 28 '19 at 17:00