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]

1) You say:
-the first code works
So (I say) "use it and you are ok"

But based on which information you say that "it works"? Does it return the right information? Then again "use it and you are ok".
Or just "it don't throw an error" but the result is wrong"? In this case I should say "it doesn't works"

2) When I read the specification for WorksheetFunction.Norm_Inv, it says about Arg1:
Required; data type: Double; description: Probability - A probability corresponding to the normal distribution
But in the code we pass it .RandArray(pN), i.e. an array with pN items, and I guess this is the error: vba doesn't mimick the dynamic array feature that Office 365 (the version you use, right?) offers.
So we have to mimick it in the code

3) Now I understood that your Function BestNorm should run several simulation and return "the best result"; is this correct?
If Yes maybe I can still help even if I am at zero in statistics and associated excel functions.

4) I see that the formula =ROUND(NORM.INV(RANDARRAY(pN),pMean,pStdDev),0) returns the table shown in the image
(I used =ROUND(NORM.INV(RANDARRAY(10),8,2),0) so pN=10, pMean=8, pStdDev=2)

5) If we repeat 5000 times the simulation (I saw a For i = 1 To Iter /Next I in your code, with Iter=5000) then we shall get 5000 tables similar the one in the image, or 5000*pN rows

6) Well, assuming that what I understood (point 3 above) is correct, my question is:
-how do we determine which is the best result? Not "how do we do that in vba" but "which are the criteria to say "this is the best result" and which data should the function return?

If you can follow me maybe we can make some progress and get out of the current deadlock

 

[Alex Blakenburg]

Hello Jennifer, @Anthony47 is clearly more technical than I am so this is likely to be my only foray into this thread, but see if the below overcomes the error you are getting.

    pN = 10
    
    With Application
        Dim DataRand As Variant
        DataRand = .RandArray(pN)
        
        ReDim DataNext(1 To UBound(DataRand))
        
        DataNext = .Round(.Norm_Inv(DataRand, pMean, pStdDev), 0)
    End With

 

[Anthony47]

Alex, thank you for showing me that Norm_Inv behave differently if called as Application.WorksheetFunction or Application

This means when the OP said (post #10):

This code works:

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

he meant that the function returned some value, without the need of using 2 variable.

As in

Function DemoJM(pMean As Double, pStdDev As Double, pN As Long)
Dim DataNext As Variant
'This is  JenniferMurphy's code:
  DataNext = Application.WorksheetFunction.RandArray(pN)
  DataNext = Application.Norm_Inv(DataNext, pMean, pStdDev)
  DataNext = Application.Round(DataNext, 0)
'
DemoJM = DataNext
End Function

Calling this function for example using

=demojm(8;0.3;10)

it return an array with 10 values, like the image attached to my post #11


It is still not clear to me what the OP need to retrieve (somewhere she talh about "the best result", somewhere else she run a loop with 5000 cycles), and whether after your suggestion she needs further help; in this latter case then I confirm my previous question:
-how do we determine which is the best result? Not "how do we do that in vba" but "which are the criteria to say "this is the best result" and which data should the function return?

 

[Alex Blakenburg]

I have seen a few references to WorksheetFunction not being able to return arrays but I don't know why it works if you just use the Application syntax.

I know they behave differently when it comes to error handling in that the WorksheetFunction needs VBA error handling while the Application version doesn't crash the code and allows using IsError and IfError, so they definitely do something differently under the hood.

 

[Anthony47]

I know they behave differently when it comes to error handling in that the WorksheetFunction needs VBA error handling while the Application version doesn't crash the code and allows using IsError and IfError, so they definitely do something differently under the hood.

I too use Application for the different error handling; what got me to a wrong path was that it reported a syntax error

Bye

 

[JenniferMurphy]

My only remaining question is why some of these versions of what appear to me to be identical code work and others do not. And when I say "work" I mean exactly that. They fully worK: no errors and the correct result.

' Version 1a Works
  DataNext = Application.Round(Application.Norm_Inv(Application.RandArray(pCount), pMean, pStdDev), 0)

' Version 1b Compile error: Can't assign to array
  DataNext = Application.WorksheetFunction.Round(Application.WorksheetFunction.Norm_Inv(Application.WorksheetFunction.RandArray(pCount), pMean, pStdDev), 0)

' Version1b Works
  DataNext = Application.RandArray(pCount)
  DataNext = Application.Norm_Inv(DataNext, pMean, pStdDev)
  DataNext = Application.Round(DataNext, 0)

' Version 2b Compile error: Type mismatch on line 2
  DataNext = Application.WorksheetFunction.RandArray(pCount)
  DataNext = Application.WorksheetFunction.Norm_Inv(DataNext, pMean, pStdDev)
  DataNext = Application.WorksheetFunction.Round(DataNext, 0)

' Version 3a Works
  With Application
    DataNext = .Round(.Norm_Inv(.RandArray(pCount), pMean, pStdDev), 0)
  End With

' Version 3b Compile error: Can't assign to array
  With Application.WorksheetFunction
    DataNext = .Round(.Norm_Inv(.RandArray(pCount), pMean, pStdDev), 0)
  End With

' Version 4a Works
  With Application
    DataNext = Application.RandArray(pCount)
    DataNext = Application.Norm_Inv(DataNext, pMean, pStdDev)
    DataNext = Application.Round(DataNext, 0)
  End With

' Version 4b Compile error: Type mismatch on line 2
  With Application.WorksheetFunction
    DataNext = Application.WorksheetFunction.RandArray(pCount)
    DataNext = Application.WorksheetFunction.Norm_Inv(DataNext, pMean, pStdDev)
    DataNext = Application.WorksheetFunction.Round(DataNext, 0)
  End With

 

[Anthony47]

The canonical method for using the Excel Function from within vba is Application.WorksheetFunction.xlFunc (see Using Excel worksheet functions in Visual Basic)

But it is true that most (all?) of the accepted functions can be called using Application.xlFunc
This has been long known, and the immediate difference is how they deal with error conditions: WorksheetFunction.xlFunc throws a run-time error, whereas Application.xlFunc “returns” an error, and basically this second approach is easier to handle than run-time errors

Calling Worksheet Functions In VBA

www.cpearson.com

But it has also been reported that sometime some Functions returns different value or at least different datatype (this could be the case with your "Version 2b" test).
I have never seen an official Microsoft document that explain why this situation, but Tushar Mehta gave a reasonable interpretation in this aged discussion:

Redirecting

But basically I should not ask “why”; we should remember the two approaches and use the one that matches our needs.

 

[Alex Blakenburg]

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)

 

[Anthony47]

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

Thank you for the link, very interesting from a technical point of view

 

[JenniferMurphy]

The canonical method for using the Excel Function from within vba is Application.WorksheetFunction.xlFunc (see Using Excel worksheet functions in Visual Basic)

But it is true that most (all?) of the accepted functions can be called using Application.xlFunc
This has been long known, and the immediate difference is how they deal with error conditions: WorksheetFunction.xlFunc throws a run-time error, whereas Application.xlFunc “returns” an error, and basically this second approach is easier to handle than run-time errors

Calling Worksheet Functions In VBA

www.cpearson.com

But it has also been reported that sometime some Functions returns different value or at least different datatype (this could be the case with your "Version 2b" test).
I have never seen an official Microsoft document that explain why this situation, but Tushar Mehta gave a reasonable interpretation in this aged discussion:

Redirecting

Thanks for those links. I took a quick look. I'll dig deeper when I have some time.

Your comments led me to a solution (probably my 3a or 4a, if I need to see any intermediate values), so I'm marking this as the soluiton.

Thanls for your he[p.

But basically I should not ask “why”; we should remember the two approaches and use the one that matches our needs.

You and see the world differently or, at least, deal with it differently. I always ask why. ALWAYS.