Others

How to Use the Standard Normal Distribution Function in Access VBA

Rana GarciaRana Garcia Nov 13, 2026 869 views

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.

How to Use the Standard Normal Distribution Function in Access VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the Access VBA editor

In Microsoft Access, press Alt+F11 or select Database Tools > Visual Basic to open the Visual Basic Editor.

2
Insert a module

Right-click the current database in the Project Explorer, choose Insert > Module, and open the new module.

3
Add the MyNorm2 function

Enter Public Function MyNorm2(ByVal x As Double) As Double: MyNorm2 = 0.5 * (1# + Application.Erf(x / Sqr(2#))): End Function.

4
Test in the Immediate Window

Press Ctrl+G, type ?MyNorm2(1.96), and press Enter. Confirm the result is approximately 0.975.

Use Application.Erf in a Native Access VBA Function
Application.Erf availability: If Application.Erf is not recognized, use the pure VBA approximation solution or the reusable Excel instance solution.
Free Microsoft Office alternative

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.

Uses a familiar ribbon interface for spreadsheets, documents, and presentations to reduce migration effort.Opens, edits, and saves Microsoft Excel .xlsx, Word .docx, and PowerPoint .pptx files with high compatibility.Free to download and use with no subscription for core Office features.Lightweight installation avoids the overhead of repeated Excel automation and keeps older PCs responsive.Includes built-in PDF and cloud tools to streamline document sharing and reduce add-ins.
microsoft office alternative - wps office

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.