Search code examples
vbaworksheet-functioninverse

Problem with WorksheetFunction.Norm_S_Inv()

I am trying to make a modelisation of Monte-Carlo for 5000 trials. I seem to have a weird problem with

g = WorksheetFunction.Norm_S_Inv(Rnd())

During the functioning of the loop (of the 5000 trials), at first it is working but then an error message occurs:

Run-time error "1004": Method 'Norm_S_Inv' of object 'WorksheetFunction' failed

I have checked on many VBA sites and I don't seem to find a solution. I also tested with

  • Application.WorksheetFunction.Norm_S_Inv(Rnd())
  • with WorksheetFunction.Norm_S_Inv(Rnd())
  • with WorksheetFunction.Norm.S.Inv
  • with Application.WorksheetFunction.Norm.S.Inv(Rnd())
  • with WorksheetFunction.NormSInv

Thank you in advance,

Solution

  • It happens when your Rnd() returns a value of 0 or 1, which is of course an acceptable value for the Rnd() function but it is not as an input of the Norm_S_Inv().

    I can reproduce that easily on my computer (my Excel is in Italian, actually I get the 1004 code but the text on my side rather says "Cannot find property Norm_S_Inv for the class WorksheetFunction): 

    g = WorksheetFunction.Norm_S_Inv(0.14)
Debug.Print g
>>> -1.08031934081496

g = WorksheetFunction.Norm_S_Inv(0) 'or g = WorksheetFunction.Norm_S_Inv(1)
>>>

    enter image description here

    In order to avoid that, make sure you don't get 0 or 1 as inputs of your function: 

    myRand = Rnd()
Do Until (myRand <> 0 And myRand <> 1)
    myRand = Rnd()
Loop
g = WorksheetFunction.Norm_S_Inv(myRand)

    Personal note: this seems a pretty bad error handling done for this function, the error message is not at all clear and I understand your frustration in finding the root cause.