Unable to get gamma_inv property of the worksheetfunction class error

Question

My code so far, first part, where I produce random numbers to the specific range:

Dim i As Long
Randomize
     For i = 1 To 20000
         Range("A" & i) = Rnd()
     Next i
      Range("A1") = ("Rand")
      ActiveCell.Range("A1:A20000").Select
    Sheets("Sheet1").Columns("A").Copy
    Sheets("Sheet1").Columns("B").PasteSpecial xlPasteValues
     Sheets("Sheet1").Columns("A").Delete
    Range("A1") = ("Rand")
      MsgBox ("Nagenerovaných 20 000 hodnôt")

In the second part I am trying to get Gamma.Inv:

  Dim alfa As Integer
  Dim beta As Integer
  Dim a As Long
  Range("I2").Value = InputBox("zadaj parameter alfa")
  Range("J2").Value = InputBox("zadaj parameter beta")
  Range("B2").Select

 Range("I2").Value = alfa
 Range("J2").Value = beta
 For a = 1 To 20000
 Range("B" & a) = WorksheetFunction.Gamma_Inv(Rnd(), alfa, beta)
 Next a

The first part of the code works fine, but it takes a while to make these random numbers. Is there a more efficient way to do this?

The second part does not work. What I am trying to do is using random number I have already generated instead of Rnd() in gamma function. I Have tried the second part of the code with the rand(), because I wanted to know, if its gonna work.

PS: it's a school project and each part of the code is started by pressing a click button, in case you are wondering why I have separated it into 2 parts.

Answer 1

For the first part you can do this:

Dim rand_rng As Range

Set rand_rng = Range("A1:A20000")

    rand_rng = "=Rand()"
    rand_rng = rand_rng.Value2
    Range("A1") = ("Rand")
    MsgBox ("Nagenerovaných 20 000 hodnôt")

Second part has to be like this:

Dim g_range As Range
Set g_range = Range("B1:B20000")

    Range("I2").Value = InputBox("zadaj parameter alfa")
    Range("J2").Value = InputBox("zadaj parameter beta")      

     g_range.formula = "=Gamma.Inv(A1, $I$2, $J$2)"

N.B. parameter that is getting defined needs to be on the left side (i.e. alpha = Range("I2").Value)