Help returning array from UDF

[JenniferMurphy]

I need to write a UDF that will generate an array of data from a Normal Distribution, test the data, and return it to the calling sheet. I will pass it the mean, standard deviation, and the number of data points.

Here's the skeleton of the code. I need help with the details.

Function BestNorm(pMean As Double, pStdDev As Double, pN As Long)

'Define the variables. I need an array of pN rows by 1 column

Do
  'Generate N data points using this worksheet function:
    '=ROUND(NORM.INV(RANDARRAY(pN),pMean,pStdDev),0)
  'Do some tests on the data
  'If the tests are succfessful, Exit Do
  'Loop
Loop

'Return the array of data to the calling sheet

End Function

Do I generate the data in the sheet or in a variable in the UDF?

Thanks for any help.

 

[JenniferMurphy]

I think we can find the answer in the Rubber Duck article below. (fairly recent, dated: 15 Feb 2021)

Using "WorksheetFunction" is using Early Binding and as such VBA is converting "function results" to expected data types along the way.
Just using "Application" is Late Binding and it is not converting data types until the end and since we are using a Variant data type it is fine with the result.
WorksheetFunction and Errors

Another way of doing this is to use Evaluate and you will find that this works as well.

        Dim Str As String
        Str = "=Round(Norm.Inv(RandArray(" & pN & ")," & pMean & "," & pStdDev & "),0)"
        DataNext = Evaluate(Str)

I did know about evaluate, but I heard that it has performance issues.

Thanks for all you help, too.

 

[Alex Blakenburg]

You're welcome. It was good timing, that Rubber Duck was fairly recent and filled a gap for me too.