Help returning array from UDF

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,192
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

Well-known Member
Joined
Mar 29, 2006
Messages
3,285
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Attachments

  • JM_Immagine 2022-07-29 095742.jpg
    JM_Immagine 2022-07-29 095742.jpg
    22.6 KB · Views: 1
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
6,125
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
    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
 
Upvote 0

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,285
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:
Code:
  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
VBA Code:
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
Excel Formula:
=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?
 
Upvote 0

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
6,125
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,285
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Alex said:
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
 
Upvote 0

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,192
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
' 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
 
Upvote 0

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,285
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

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:

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

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
6,125
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
        Dim Str As String
        Str = "=Round(Norm.Inv(RandArray(" & pN & ")," & pMean & "," & pStdDev & "),0)"
        DataNext = Evaluate(Str)
 
Upvote 0

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,285
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 
Upvote 0

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,192
Office Version
  1. 365
Platform
  1. Windows
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

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:
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.
 
Upvote 0

Forum statistics

Threads
1,186,312
Messages
5,957,137
Members
438,289
Latest member
donnabee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top