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.

 

[Anthony47]

You need to specify the function will return a Variant:

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

Then you will work with an array:

Dim oArr()

Redim oArr(1 to xxx, 1 to yyy)

Your code will populate the array:

oArr(1,1)=This
oArr(1,2)= That
'etc etc

Finally you will assign the array as the returning argument of the function:

BestNorm = oArr
End Function

The function now will return an array of xxx rows * yyy columns

If your Office supports dynamic arrays (365 or office 2021) then the results will expand on your worksheet; otherwise you need to insert the formula in CSE format:
-select xxx rows by yyy columns
-set in the formula bar the formula
-confirm it using the keys Contr-Shift-Enter, not Enter alone

 

[JenniferMurphy]

You need to specify the function will return a Variant:

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

Then you will work with an array:

Dim oArr()

Redim oArr(1 to xxx, 1 to yyy)

Your code will populate the array:

oArr(1,1)=This
oArr(1,2)= That
'etc etc

Finally you will assign the array as the returning argument of the function:

BestNorm = oArr
End Function

The function now will return an array of xxx rows * yyy columns

If your Office supports dynamic arrays (365 or office 2021) then the results will expand on your worksheet; otherwise you need to insert the formula in CSE format:
-select xxx rows by yyy columns
-set in the formula bar the formula
-confirm it using the keys Contr-Shift-Enter, not Enter alone

Excellent. One question: Is oArr Variant? I like to explicitly specify the type.

Oh, one more: what is the "o" in oArr?

Thanks

 

[JenniferMurphy]

Oooppss.. One more question.

The code that works in the worksheet is:

=ROUND(NORM.INV(RANDARRAY(pN),pMean,pStdDev),0)

I tried putting "Application." in front of that:

DataPoints = Application.Round(NORM.INV(RandArray(pN), pMean, pStdDev), 0)

but then it complanied about Norm.Inv. Do I need to put "Application." in front of each worksheet function? Or do I do them in separate statements?

Thanks

 

[JenniferMurphy]

After a little tinkering, I got this to work:

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

Dim DataPoints() As Variant   'The resulting normal data points
Dim i As Long                 'Index variable
Dim Max As Double             'The maximum data point
Dim Min As Double             'The minimum data point

Do
'  DataPoints = Application.Round(NORM.INV(RandArray(pN), pMean, pStdDev), 0)
  DataPoints = Application.RandArray(pN)
  DataPoints = Application.Norm_Inv(DataPoints, pMean, pStdDev)
  DataPoints = Application.Round(DataPoints, 0)
  Exit Do
Loop

BestNorm = DataPoints

End Function

Is there a better way?

 

[JenniferMurphy]

The code above works, but this code does not:

  With Application
    DataPoints = RandArray(pN)
    DataPoints = Norm_Inv(DataPoints, pMean, pStdDev)
    DataPoints = Round(DataPoints, 0)
  End With

What am I doing wrong?

 

[Anthony47]

In statistics I'm at null, so I cannot help with your real problem.
So let me use a Function to solve the following dummy problem:
-tell me which is the perimeter of a certain number of regular polygons with a randon number of sides inscribed in a circle having a certain radius

I solved this problem with the following Function:

Function RandPolygonAttrib(ByVal Trials As Long, ByVal Radius As Single) As Variant
'https://www.mrexcel.com/board/threads/help-returning-array-from-udf.1211844/
Dim oArr()
Dim pBase As Double, pHeight As Double, Alpha As Double
Dim Sides As Long, I As Long
'
ReDim oArr(1 To Trials, 1 To 3)                                 'Resize the output array
'
For I = 1 To Trials
    Sides = Application.WorksheetFunction.RandBetween(3, 20)    'Random number of sides
    Alpha = 2 * Application.WorksheetFunction.Pi / Sides        'The next rows calculates
    pBase = 2 * Sin(Alpha / 2) * Radius                         ' the parametres of the
    pHeight = Cos(Alpha / 2) * Radius                           ' polygon having this number of sides
    oArr(I, 1) = Sides                                          'Save the number of sides for this trial
    oArr(I, 2) = pBase * Sides                                  'Calculate and Save the Perimeter
    oArr(I, 3) = pBase * pHeight / 2 * Sides                    'Calculate and Save the Area
Next I
'
RandPolygonAttrib = oArr                                        'Export the calculated values
'
End Function

Then you will call it using the syntax:
= RandPolygonAttrib(NumberOfTrials, RadiusOfTheInscribingCircle)

For example

=RandPolygonAttrib(3,2)

This will return the perimeter and area of 3 polygons having a random number of sides inscribed in a circle having radius=2. As shown in the image


A few random comments to some of your doubts:

Is oArr Variant?

When you use Dim oArr() you declare an unspecified array of type variant (Variant is the default type)

what is the "o" in oArr?

You may use any name for your variables (but there area certain constrains); in my own terminology oArr stands for “output array”

The code that works in the worksheet is:

=ROUND(NORM.INV(RANDARRAY(pN),pMean,pStdDev),0)

Round, RandArray, Norm.Inv are all WorksheetFunctions, so you should use for example

With Application.WorksheetFunction
    DataPoints = .Round(.Norm_Inv(.RandArray(pN), pMean, pStdDev), 0)
End With

Is there a better way?

When a code does what you need then its perfect

 

[JenniferMurphy]

Round, RandArray, Norm.Inv are all WorksheetFunctions, so you should use for example

With Application.WorksheetFunction
    DataPoints = .Round(.Norm_Inv(.RandArray(pN), pMean, pStdDev), 0)
End With

I get an error on that statement. Here's my code:

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

Dim DataNext() As Variant         'The next set of normal data points
Dim DataKeep() As Variant         'The best set of normal data points
Dim i As Long                     'Index variable
Dim iTemp As Double               'Next max value
Dim iMax As Double: iMax = 0      'The maximum data point
Const Iter As Long = 5000         'Number of iterations (5,000 = ? min)
'Const Iter As Long = 50000000     'Number of iterations

For i = 1 To Iter
'  DataNext = Application.RandArray(pN)
'  DataNext = Application.Norm_Inv(DataNext, pMean, pStdDev)
'  DataNext = Application.Round(DataNext, 0)
  With Application.WorksheetFunction
    DataNext = .Round(.Norm_Inv(.RandArray(pN), pMean, pStdDev), 0)
    If .Min(DataNext) < 52 Then GoTo Continue
    iTemp = .Max(DataNext)
  End With
 
Continue:
Next i

BestNorm = DataKeep

End Function

When I try to run it, I get this error:

There are a few lines of code here that are not in the snippet above, but I don't think they matter. I deleted code that I thought didn't matter to keep it simpler.

When a code does what you need then its perfect

If only that were true (sigh)

 

[Anthony47]

I only know (because I read the description of the function Norm.Inv) that .Round(.Norm_Inv(.RandArray(pN), pMean, pStdDev), 0) returns a list with pN lines, each related to a random index of probability.
I understand you would like to run 5000 trials and keep the best one; if you explain how understanding which is "the best result" out of the 5000 trials maybe I could suggest something

 

[JenniferMurphy]

I only know (because I read the description of the function Norm.Inv) that .Round(.Norm_Inv(.RandArray(pN), pMean, pStdDev), 0) returns a list with pN lines, each related to a random index of probability.
I understand you would like to run 5000 trials and keep the best one; if you explain how understanding which is "the best result" out of the 5000 trials maybe I could suggest something

Here's the problem. This code works:

  DataNext = Application.RandArray(pN)
  DataNext = Application.Norm_Inv(DataNext, pMean, pStdDev)
  DataNext = Application.Round(DataNext, 0)

But this code gets an error on the second statement:

  With Application.WorksheetFunction
    DataNext = .RandArray(pN)                       'Works
    DataNext = .Norm_Inv(DataNext, pMean, pStdDev)  'Fails
    DataNext = .Round(DataNext, 0)
  End With

This is the error:

The 2 sets of code shoud be identical, no?