Results 1 to 7 of 7

Thread: Unable to get the Norm_Inv property of the WorksheetFunction Class

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    52

    Unable to get the Norm_Inv property of the WorksheetFunction Class

    Hey all,

    I'm currently suffering at the hands of an error which I've posted about before. On this one specific line, I receive the error "Unable to get the Norm_Inv property of the WorksheetFunction Class":

    Code:
    Rand = Application.WorksheetFunction.Norm_Inv(Rnd(), 15, 1)
    I'm attempting to retrieve a random number from a distribution with a mean of 15 and standard deviation of 1. I've used the same line numerous time with different values and this is the only one that receives an error. After the error pops up, I simply press Run again and the code continues until a few minutes later when I receive the same error. It's preventing me from running my code (which takes hours) without monitoring it.

    For comparison's sake, the following line is essentially the same code, but has never returned an error:

    Code:
    Array(i, 9) = Application.WorksheetFunction.Norm_Inv(Rnd(), 216, 15)
    In both cases, Rand and Array(i, 9) are of type Double. Thus far the only idea I've had is changing the name, which had no effect.

    Anyone have a clue what's going on?

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Unable to get the Norm_Inv property of the WorksheetFunction Class

    i do not know why it would not work

    try that code in a new workbook
    if it still errors attach the workbook to post, zip first
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    52

    Re: Unable to get the Norm_Inv property of the WorksheetFunction Class

    Cheers for the response!

    I opened a blank excel workbook and ran the line for a good 50,000 iterations, no error.

    After doing some googling regarding that, two solutions I've come across are to use "Application.Norm_Inv(Rnd(), 216, 15)", or to use the following line:

    Code:
    On Error Resume Next
    I'll test both and return with my results

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Unable to get the Norm_Inv property of the WorksheetFunction Class

    if you use on error resume next it will defeat the purpose of the code, as although the code will run without stopping, the variable will either contain no value or the previous value returned without error

    can you use the line that errors in some other part of the code in the original workbook?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    52

    Re: Unable to get the Norm_Inv property of the WorksheetFunction Class

    Quote Originally Posted by westconn1 View Post
    if you use on error resume next it will defeat the purpose of the code, as although the code will run without stopping, the variable will either contain no value or the previous value returned without error
    I realised this when trying it out, you'd think common sense would've come to my aid

    I moved the troublesome line of code to it's own subroutine, and it still leads to the same errors. I also tried removing the worksheetfunction part, and that led to a type mismatch error. Any thoughts?

    Again, thanks for your help.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Unable to get the Norm_Inv property of the WorksheetFunction Class

    just as a test, try calling Randomize when the code may error
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Member
    Join Date
    Oct 2016
    Posts
    52

    Re: Unable to get the Norm_Inv property of the WorksheetFunction Class

    I tried calling Randomize, but it still errors unfortunately.

    I suspected it might be something to do with Rnd(), so I tried the following:

    Code:
     Dim Ran as Double
                Ran = Int((1 - 0 + 1) * Rnd + 0)
                Rand = 0
                Rand = Application.WorksheetFunction.Norm_Inv(Ran, 15, 1)
    Unsurprisingly it had no impact.

    I really don't understand how such an error can occur on just one specific line, I use the Norm_inv function about 10 times within my code, thousands of times each iteration, and yet it's only this line that errors.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width