Consulting

Results 1 to 5 of 5

Thread: DataPoint = NormInv(rand(), MeanNow, 1#) fails in VBA

  1. #1

    DataPoint = NormInv(rand(), MeanNow, 1#) fails in VBA

    Using VBA in Excel 2016. The rand() function is not defined.

    My addins are :
    Analysis Toollpack and
    Analysis Toollpack VBA

    What is the problem, what is an alternative?

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,873
    Location
    Rand() is the worksheet function

    Rnd() is the VBA function

    https://docs.microsoft.com/en-us/off...6)%26rd%3Dtrue
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    I replaced the failed statement with this and it worked:
    DataPoint = Application.WorksheetFunction.NormInv(Rnd(), MeanNow, SD)
    Well almost. It ran for 600,000 data points. Then there was a weird failure with the error message:
    Run-time error "1004"
    Unable to get Norminv property of the WorksheetFunction class
    I clicked [debug], and F5, and it continued running to a total of 2,500,000 numbers without any more errors.
    The error is repeatable.

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,873
    Location
    Show the code
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Solved: this works:
    Function MyRandomNormal2() As Double
        Dim Uniform As Double
        Uniform = Evaluate("=rand()")
        MyRandomNormal2 = Application.WorksheetFunction.NormInv(Uniform, MeanNow, SD)
    End Function

Posting Permissions

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