Forum Discussion
Norm.S.Dist in VBA Access
hello,
I am trying to use Norm.S.Dist in a VBA module, ms access, in a querry.
i found only very old hints, that are not working anymore.
could you help me with a fast, working example?
this code is very, very slow
Public Function MyNorm2(x As Double) As Double
Dim xlApp As Object
Dim xlFunc As Object
Set xlApp = CreateObject("Excel.Application")
Set xlFunc = xlApp.WorksheetFunction
MyNorm2 = xlFunc.Norm_S_Dist(x, True)
xlApp.Quit
Set xlFunc = Nothing
Set xlApp = Nothing
End Function
thanks
dnemescu wrote: ``MyNorm2 = xlFunc.Norm_S_Dist(x, True)``
I don't know about MSAccess, but the Excel Worksheetfunction Norm_S_Dist has only one parameter, just like the Excel NORMSDIST function.
So perhaps the following is sufficient:
Public Function MyNorm2(x As Double) As Double
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
MyNorm2 = xlApp.WorksheetFunction.Norm_S_Dist(x)
xlApp.Quit
Set xlApp = Nothing ' not necessary for a local variable?
End Functionclass Main { public static void main(String[] args) { // SaveAllFilesName.save_files_name_to_file(); // String str = "filename=\"Welcome.zip\""; // str=SplitWords.parse_file_name(str); // System.out.println(str); System.out.println(cdfGaussian(1.33334)); } public static double cdfGaussian(double x) { double t = 1.0/(1+0.2316419 * Math.abs(x)); double b1 = 0.31938153; double b2 = - 0.356563782; double b3 = 1.781477937; double b4 = - 1.821255978; double b5 = 1.330274429; double temp = 1 - Math.exp(- x*x/2)/Math.sqrt(2*Math.PI) * (b1*t + b2 * Math.pow(t, 2) + b3 * Math.pow(t, 3) + b4 * Math.pow(t, 42) + b5 * Math.pow(t, 5)); if (x < 0) { temp = 1 - temp; } return temp; } }I found an algorithm of JAVA。
I have run it and the result very similar to Excel function.
vba may like below
function cdfGaussian( x)
t = 1.0/(1+0.2316419 * abs(x));
double b1 = 0.31938153;
double b2 = - 0.356563782;
double b3 = 1.781477937;
double b4 = - 1.821255978;
double b5 = 1.330274429;
double temp = 1 - exp(- x*x/2)/sqr(2*3.1415926) *
(b1*t + b2 * power(t, 2) + b3 * power(t, 3)
+ b4 * Math.power(t, 42) + b5 * power(t, 5));
temp = 1 - temp;
cdfGaussian=temp
end function
thanks, it is working, with very slight differences from the original function.
Public Function StandardNormalCumulative(x As Double) As Double
Dim t As Double
Dim pi As Double
Dim y As Double
Dim b1 As Double
Dim b2 As Double
Dim b3 As Double
Dim b4 As Double
Dim b5 As Double
pi = 3.14159265358979
t = 1 / (1 + 0.2316419 * Abs(x))
y = 1 / Sqr(2 * pi) * Exp(-0.5 * x ^ 2)
b1 = 0.31938153
b2 = -0.356563782
b3 = 1.781477937
b4 = -1.821255978
b5 = 1.330274429
If x >= 0 Then
StandardNormalCumulative = 1 - y * (b1 * t + b2 * t ^ 2 + b3 * t ^ 3 + b4 * t ^ 4 + b5 * t ^ 5)
Else
StandardNormalCumulative = y * (b1 * t + b2 * t ^ 2 + b3 * t ^ 3 + b4 * t ^ 4 + b5 * t ^ 5)
End If
End FunctionAlternatively,as Joe metioned you can try below codes.
public xlApp As Object
public xlFunc As Object
public xlWb As Object
Public Sub setNewXlApp()
rem use getobject to open an excel workbook firstly or specify a excel workbook path (google getobject)
Set xlApp = GetObject(,"Excel.Application").Application
Set xlFunc = xlApp.WorksheetFunction
End Sub
Public Function MyNorm2(x As Double) As Double
MyNorm2 = xlFunc.Norm_S_Dist(x, True)
End Function
sub main
sql="select MyNorm2(field) from tableA"
set runSql=currentdb.openrecordset(sql)
Set xlFunc = Nothing
end sub
