使用VBA从分布向存储器生成随机数 [英] Random Number Generation to Memory from a Distribution using VBA

问题描述

我想从VBA(Excel 2007)中的选定分布中生成随机数. 我目前正在使用带有以下代码的Analysis Toolpak:

     Application.Run "ATPVBAEN.XLAM!Random", "", A, B, C, D, E, F

哪里

A = how many variables that are to be randomly generated 
B = number of random numbers generated per variable 
C = number corresponding to a distribution
         1= Uniform
         2= Normal
         3= Bernoulli
         4= Binomial
         5= Poisson
         6= Patterned
         7= Discrete 
D = random number seed
E = parameter of distribution (mu, lambda, etc.) depends on choice for C
(F) = additional parameter of distribution (sigma, etc.) depends on choice for C

但是我想将随机数生成到一个数组中，而不是生成到一张纸上. 我知道""的位置指定应将随机数打印到的位置，但是我不知道将随机数分配给数组或某种其他形式的内存而不是表的语法./p>

我已经尝试按照 Analysis Toolpak网站中讨论的语法进行操作，但是没有成功.

我意识到VBA不是生成随机数的理想场所，但是我需要在VBA中这样做.任何帮助深表感谢！谢谢！

解决方案

使用内置函数是关键.除泊松外，每个功能都有一个对应的版本.在我提出的解决方案中，我使用Knuth提出的算法从泊松分布中生成一个随机数.

对于离散或模式化，显然您必须编写自定义算法.

关于种子，可以在填充数组之前放置 Randomize [seed] .

Function RandomNumber(distribution As Integer, Optional param1 = 0, Optional param2 = 0)

    Select Case distribution
    Case 1 'Uniform
        RandomNumber = Rnd()
    Case 2 'Normal
        RandomNumber = Application.WorksheetFunction.NormInv(Rnd(), param1, param2)
    Case 3 'Bernoulli
        RandomNumber = IIf(Rnd() > param1, 1, 0)
    Case 4 'Binomial
        RandomNumber = Application.WorksheetFunction.Binom_Inv(param1, param2, Rnd())
    Case 5 'Poisson
        RandomNumber = RandomPoisson(param1)
    Case 6 'Patterned
        RandomNumber = 0
    Case 7 'Discrete
        RandomNumber = 0
    End Select

End Function

Function RandomPoisson(ByVal lambda As Integer)   'Algorithm by Knuth

    l = Exp(-lambda)
    k = 0
    p = 1

    Do
         k = k + 1
         p = p * Rnd()
    Loop While p > l

    RandomPoisson = k - 1

End Function

I want to generate random numbers from a selected distribution in VBA (Excel 2007). I'm currently using the Analysis Toolpak with the following code:

     Application.Run "ATPVBAEN.XLAM!Random", "", A, B, C, D, E, F

Where

A = how many variables that are to be randomly generated 
B = number of random numbers generated per variable 
C = number corresponding to a distribution
         1= Uniform
         2= Normal
         3= Bernoulli
         4= Binomial
         5= Poisson
         6= Patterned
         7= Discrete 
D = random number seed
E = parameter of distribution (mu, lambda, etc.) depends on choice for C
(F) = additional parameter of distribution (sigma, etc.) depends on choice for C

But I want to have the random numbers be generated into an array, and NOT onto a sheet. I understand that where the "" is designates where the random numbers should be printed to, but I don't know the syntax for assigning the random numbers to an array, or some other form of memory storage instead of to a sheet.

I've tried following the syntax discussed at this Analysis Toolpak site, but have had no success.

I realize that VBA is not the ideal place to generate random numbers, but I need to do this in VBA. Any help is much appreciated! Thanks!

解决方案

Using the inbuilt functions is the key. There is a corresponding version for each of these functions but Poisson. In my presented solution I am using an algorithm presented by Knuth to generate a random number from the Poisson Distribution.

For Discrete or Patterned you obviously have to write your custom algorithm.

Regarding the seed you can place a Randomize [seed] before filling your array.

Function RandomNumber(distribution As Integer, Optional param1 = 0, Optional param2 = 0)

    Select Case distribution
    Case 1 'Uniform
        RandomNumber = Rnd()
    Case 2 'Normal
        RandomNumber = Application.WorksheetFunction.NormInv(Rnd(), param1, param2)
    Case 3 'Bernoulli
        RandomNumber = IIf(Rnd() > param1, 1, 0)
    Case 4 'Binomial
        RandomNumber = Application.WorksheetFunction.Binom_Inv(param1, param2, Rnd())
    Case 5 'Poisson
        RandomNumber = RandomPoisson(param1)
    Case 6 'Patterned
        RandomNumber = 0
    Case 7 'Discrete
        RandomNumber = 0
    End Select

End Function

Function RandomPoisson(ByVal lambda As Integer)   'Algorithm by Knuth

    l = Exp(-lambda)
    k = 0
    p = 1

    Do
         k = k + 1
         p = p * Rnd()
    Loop While p > l

    RandomPoisson = k - 1

End Function

这篇关于使用VBA从分布向存储器生成随机数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！