Microsoft Access Statistical Functions using VBA with Total Access Statistics

No Complicated VBA Functions

Total Access Statistics makes it very easy to add and automate complex statistical analysis into your application. Rather than creating a complex language for each statistical function and all its options, Total Access Statistics lets you specify those options from the Interactive Add-in Wizard. The VBA interface simply runs the settings you've saved. This makes it very easy for you to specify, run, and test the analysis before adding to your project.

Each saved scenario has a unique number and you call the statistics function by passing the ID of the scenario you want to run. It couldn't be easier!

VBA Programmatic Interface

A VBA programmatic interface is available so you can generate your statistical calculations from code. You can easily run any saved scenario by calling a single procedure in the Total Access Statistics VBA library. The library includes a royalty-free runtime license so you can include it with your Access database and distribute it to non-Total Access Statistics owners.

Call the function from a button's OnClick event or other process, and the results are generated for you. You can even hide the built-in user interface so your users don't even know Total Access Statistics is running. They'll be amazed with your statistical analysis capabilities!

Add Total Access Statistics to Your References

To use a function in a library database, you need to create an explicit reference from your database to the library. Handle this by putting a module in design mode, and invoking the VBA Editor’s Tools, References menu:

Different runtime libraries are available to support your Access version and database format. If you are using a MDB database, a MDE runtime library is used. If you are using an ACCDB database in Access 2007 or later, an ACCDE database is available. A 64 bit version of the ACCDE is available for the 64 bit versions of Access 2010, 2013 and 2016.

Total Access Statistics includes a special VBA function (TAS_Statistics) for your use in the runtime library. To add this to your code, follow these easy steps:

• Create the scenario in the database with the data to analyze.
• Determine the scenario number of the scenario desired.
• Make sure Total Access Statistics is properly installed and referenced by your database as a library database.
• Invoke the Total Access Statistics function.

The VBA statistics function definition is:

Function TAS_Statistics(
	ByVal lngScenarioNo As Long, 
	ByVal strScenarioTbl As String, 
	ByVal strFieldTbl As String,
	ByVal strOptionsTbl As String, 
	ByVal fMessage As Boolean,
	Optional strDataSource As String = "",
	Optional strOutput1Tbl As String = "", 
	Optional strOutput2Tbl As String = "", 
	Optional strMessageTitle As String = "",
	Optional strParameterTbl As String = "")
As Boolean

That may seem complicated, but most of the parameters are optional and support situations when you store the scenario settings in your own tables or override the saved settings. Here's an example of run a scenario number 5) and letting it show progress to the user:

Dim fOK As Boolean

fOK = TAS_Statistics(5, "", "", "", True)

The function returns a True or False depending on whether it was successful.

To make it even easier to create the VBA code for your application, Total Access Statistics includes a Code Generator feature that creates a procedure for your currently selected scenario:

Simply paste it in your application and you're ready to go.

In addition to running Scenarios, Total Access Statistics also lets you incorporate the Probability Calculator in your VBA code. Easily generate probability values for Student's Z-Value, t-Value, Chi-Square, and F-Value with their corresponding degrees of freedom.

Probability Calculator in Total Access Statistics

The Probability Calculator also has a Code Generator to generate the code with a button click:

Probability function definition:

Function TAS_Probability(
	ByVal strProbType As String,
	ByVal dblTestValue As Double, 
	Optional ByVal dblDF1 As Double,
	Optional ByVal dblDF2 As Double)
As Double

Sample Code

This example calculates the probability of an F-value of 1.5 for 30 and 50 degrees of freedom:

Dim dblProb as Double

dblProb = TAS_Probability("F", 1.5, 30, 50)

Similarly, if you know the probability value and degrees of freedom, the Inverse Probability function generates the test value for that probability.

Inverse Probability Calculator in Total Access Statistics

Function TAS_ProbInverse(
	ByVal strProbType As String, 
	ByVal dblProb As Double, 
	Optional ByVal dblDF1 As Double,
	Optional ByVal dblDF2 As Double)
As Double

Sample Code

This example finds the t-value with a probability of 0.05 (5%) for a distribution with 30 degrees of freedom:

Dim dblValue as Double

dblValue = TAS_ProbInverse("T", 0.05, 30)