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.

M-- · Accepted Answer · 2017-04-27T18:30:46.533

0

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)

edited Apr 27 '17 at 18:30

answered Apr 27 '17 at 17:56

M--

25,431
8
61
93

Yes, works perfect, thanks. But, what about "randomize" before I start generating random numbers? Is it not neccesary? And also, I forgot to ask, how do I sort these numbers? From the smallest values to highest..I have no clue how to do that in VBA, yes, I can use the macro for example and then step into, but I Have to do it in VBA. – mandu J. Apr 27 '17 at 18:44
@manduJ. This thread helps you to learn ["how to sort?"](http://stackoverflow.com/questions/21451458/vba-excel-sort-range-by-specific-column). What do you mean randomize before generating random number? Seed number changes by time in excel. If you want to make it not change then you need to set the seed. – M-- Apr 27 '17 at 18:54
Thank you very much! By randomize I ment, that I have found many examples of how to made random numbers in VBA, some of them had randomize before the generating itself. Just word randomize, I dont know how to call it specificly. But it works also without randomize, as I can see. What I have done with those rand. numbers was that, I have copy the entire column, paste it special as values into column B, then delete column A, so I had rand. numbers in column A, without changing all over again. Pretty dummy, but it just worked fine. – mandu J. Apr 27 '17 at 19:05

score 0 · Answer 2 · answered Apr 28 '17 at 13:23

0

Might be a bit faster to set them all at once:

[I2] = InputBox("zadaj parameter alfa")
[J2] = InputBox("zadaj parameter beta")

[A1:B20000] = [{"=RAND()", "=GAMMA.INV(A1, I$2, J$2)"}]

answered Apr 28 '17 at 13:23

Slai

22,144
5
45
53

Unable to get gamma_inv property of the worksheetfunction class error

2 Answers2