Forum Discussion
Norm.S.Dist in VBA Access
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
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
