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

[stanhilliard]

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?

[Paul_Hossler]

Rand() is the worksheet function

Rnd() is the VBA function

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/rnd-function?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev11.query%3Fapp Id%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vblr6.chm1009008)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue

[stanhilliard]

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.

[Paul_Hossler]

Show the code

[stanhilliard]

Solved: this works:

Function MyRandomNormal2() As Double
Dim Uniform As Double
Uniform = Evaluate("=rand()")
MyRandomNormal2 = Application.WorksheetFunction.NormInv(Uniform, MeanNow, SD)
End Function