Norm.S.Dist in VBA Access

[dnemescu]

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

[#2]

@dnemescu 

class 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

[#3]

@peiyezhu 

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 Function

[#4]

@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 Function

[#5]

@dnemescu 

Alternatively,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