I use the following line to generate Poisson random numbers:
Application.Run("Random", "", 1, 100, 5, , 34)
this produces 100 random numbers with Lambda (34) in an excel sheet. I would like to save the output into a variable instead of sheet. I tried
X=Application.Run("Random", "", 1, NSim, 5, , 34)
I don't get any error but nothing is saved in "X". Could you please help me how I can save the result in a variable. Thanks
Seems like the function returns a boolean instead of an array of numbers and only accepts a range as output as one of its parameters.
It looks like Random is a sub routine, unless you have access to the underlying code you can't do this directly.
What you could do (albeit rather backwards) is let the sub run, then assign the result back to a variable and remove it from the sheet:
Sub MM()
Dim generateNumbers As Integer, results As Variant
generateNumbers = 100 '// numbers to generate
Application.Run("Random", "", 1, generateNumbers, 5, , 34)
results = WorksheetFunction.Transpose(Range("A1:A" & generateNumbers))
Range("A1:A" & generateNumbers).ClearContents '// If required
End Sub
The variable results will now be an array of the results.
How about:
Sub MAIN()
Dim x(1 To 100) As Variant
Dim Lambda As Double
Lambda = 34
For I = 1 To 100
x(I) = Poisson(Lambda)
Next I
End Sub
Public Function Poisson(L As Double) As Long
Dim I As Long, x As Double, expL As Double
expL = Exp(-L)
I = 0
x = 1
While x > expL
I = I + 1
x = x * Rnd()
Wend
Poisson = I - 1
End Function
Although it is poissonous, it's not toxic.
