How to Use the Standard Normal Distribution Function in Access VBA
Question details
The user needs to call the standard normal cumulative distribution function inside Microsoft Access VBA without creating and closing an Excel instance for every calculation.

- Product
- Microsoft Access, Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user is performing statistical calculations in Microsoft Access VBA and needs an efficient way to evaluate the standard normal cumulative distribution function.
- Observed behavior
- Repeatedly creating and closing Excel.Application instances for each calculation is slow and inefficient.
Check whether your Access VBA environment can access Application.Erf or the Excel object library. If you use a pure VBA approximation, prepare a small set of known normal CDF values, such as NormCDF(0) = 0.5 and NormCDF(1.96) = 0.975, for validation.
Use Application.Erf in a Native Access VBA Function
Use this when Application.Erf is available in your Access VBA environment; it calculates the normal CDF without creating Excel objects.
The simplest direct calculation uses the error function. The standard normal CDF is 0.5 * (1 + ERF(x / SQRT(2))). This avoids Excel automation and runs entirely inside Access.
In Microsoft Access, press Alt+F11 or select Database Tools > Visual Basic to open the Visual Basic Editor.
Right-click the current database in the Project Explorer, choose Insert > Module, and open the new module.
Enter Public Function MyNorm2(ByVal x As Double) As Double: MyNorm2 = 0.5 * (1# + Application.Erf(x / Sqr(2#))): End Function.
Press Ctrl+G, type ?MyNorm2(1.96), and press Enter. Confirm the result is approximately 0.975.

Use a Tested Pure VBA Normal CDF Approximation
Use this when Application.Erf is unavailable and you want no dependency on Excel automation.
Reuse One Excel Application Instance for Worksheet Functions
Use this if you must call the Excel worksheet function and want to avoid starting Excel for every calculation.
A Free, Lightweight Microsoft Office Alternative for Everyday Spreadsheet Work
WPS Office does not provide Microsoft Access or an Access VBA fix, but it is a free, lightweight Office alternative. WPS Spreadsheets opens and saves Excel .xlsx workbooks with familiar menus and strong format compatibility, making it easy to move everyday data and document work without heavy Office automation or subscription costs.

Frequently Asked Questions
Can Access VBA calculate the standard normal CDF without Excel?
Yes. If Application.Erf is available, use MyNorm2 = 0.5 * (1# + Application.Erf(x / Sqr(2#))). If not, you can implement a tested pure VBA approximation or reuse one Excel.Application instance.
Why is creating Excel.Application for each calculation a problem?
Each CreateObject and Quit starts and stops an Excel process, which is slow and can leave hidden Excel processes or cause memory issues. A single reused instance or native VBA function avoids that overhead.
Does the Excel worksheet function Norm_S_Dist work from Access?
Yes, if Excel is installed. You can call xlApp.WorksheetFunction.Norm_S_Dist(x, True) through a reusable Excel.Application object, but this still requires Excel and is slower than a native VBA calculation.
What is Application.Erf in Access VBA?
Application.Erf is the error function exposed in some Office VBA environments. The standard normal CDF can be expressed as 0.5 * (1 + Application.Erf(x / Sqr(2))). Availability depends on the Office version and VBA references.
Can WPS Office replace Microsoft Access VBA?
WPS Office does not include Access or run Access VBA. It can replace many Excel, Word, and PowerPoint tasks through WPS Spreadsheets, Writer, and Presentation, but Microsoft Access-specific code must remain in Access.




