The Excel "NORMSINV" Function in Visual Basic

The MS-Excel worksheet function NORMSINV computes the "inverse standard normal cumulative distribution". This function is not available in MS-Access (VBA) or VB6. When Office 2000 is installed, the undocumented function MSOWCFLib.OCFunc.NormSInv (in MSOWCF.DLL) can be used. But with Office 2003 this library is no longer installed.

The VB function below uses Peter J. Acklam's algorithm to compute the same values as the NORMSINV function of Excel.

' This function is a replacement for the Microsoft Excel Worksheet function NORMSINV.
' It uses the algorithm of Peter J. Acklam to compute the inverse normal cumulative
' distribution. Refer to http://home.online.no/~pjacklam/notes/invnorm/index.html for
' a description of the algorithm.
' Adapted to VB by Christian d'Heureuse, http://www.source-code.biz.
Public Function NormSInv(ByVal p As Double) As Double
Const a1 = -39.6968302866538, a2 = 220.946098424521, a3 = -275.928510446969
Const a4 = 138.357751867269, a5 = -30.6647980661472, a6 = 2.50662827745924
Const b1 = -54.4760987982241, b2 = 161.585836858041, b3 = -155.698979859887
Const b4 = 66.8013118877197, b5 = -13.2806815528857, c1 = -7.78489400243029E-03
Const c2 = -0.322396458041136, c3 = -2.40075827716184, c4 = -2.54973253934373
Const c5 = 4.37466414146497, c6 = 2.93816398269878, d1 = 7.78469570904146E-03
Const d2 = 0.32246712907004, d3 = 2.445134137143, d4 = 3.75440866190742
Const p_low = 0.02425, p_high = 1 - p_low
Dim q As Double, r As Double
If p < 0 Or p > 1 Then
Err.Raise vbObjectError, , "NormSInv: Argument out of range."
ElseIf p < p_low Then
q = Sqr(-2 * Log(p))
NormSInv = (((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / _
((((d1 * q + d2) * q + d3) * q + d4) * q + 1)
ElseIf p <= p_high Then
q = p - 0.5: r = q * q
NormSInv = (((((a1 * r + a2) * r + a3) * r + a4) * r + a5) * r + a6) * q / _
(((((b1 * r + b2) * r + b3) * r + b4) * r + b5) * r + 1)
Else
q = Sqr(-2 * Log(1 - p))
NormSInv = -(((((c1 * q + c2) * q + c3) * q + c4) * q + c5) * q + c6) / _
((((d1 * q + d2) * q + d3) * q + d4) * q + 1)
End If
End Function

Author: Christian d'Heureuse (www.source-code.biz, www.inventec.ch/chdh)