"The code is exactly
how I would like to write code and the algorithms used are very
efficient and welldocumented."
Van T. Dinh, Microsoft MVP


Module: MathStatistics in Category General VBA/VB6 : Math from Total Visual SourceBook
Mathematical functions covering the areas of statistics and probability in VBA and VB6.
This category contains various math routines covering the areas of statistics and probability.
VBA has builtin functions for various mathematical operations. Search online help for "Math Functions" and "Derived Math Functions" for more information
Procedure List
Procedure Name

Type

Description

(Declarations)

Declarations

Declarations and private variables for the modMathStatistics module

Factorial

Procedure

Get the factorial of a given number.
This function only works for values between 0 and 170. 170 is the maximum because the factorial of any number greater than 170 is beyond the numeric range of VBA/VB6.

FactorialRecursive

Procedure

Get the recursive factorial of a given number.
This function only works for values between 0 and 170. 170 is the maximum because the factorial of any number greater than 170 is beyond the numeric range of VBA/VB6.

GetArrayMean

Procedure

Get the mean of the elements in the supplied array

GetArrayMedian

Procedure

Get the median of the elements in the supplied array.
fSort should be set to True if you are not concerned with the order of the numbers in your array. If you do not want the array you pass to be sorted, set fSort to False.

GetArrayMode

Procedure

Get the mode (most common value) of the supplied array. If there is a tie, the smallest number is the mode.
This function also includes information on calculating standard error and coefficient of variance.

GetArrayStatistics

Procedure

Calculate descriptive statistics of an array using N1 method.
Also includes information on calculating standard error and coefficient of variance.

GetArrayStdDev

Procedure

Get the standard deviation of the supplied array using N1 method.

GetNumberCombinations

Procedure

Get the number of combinations of items that can be derived from a population (order does not matter).
Combinations are different from permutations, because permutations count the number of ways (orders) items can be selected from the pool. For instance, with playing cards, there is only one combination of 4 Aces (all four cards), but there are many permutations depending on the order the Aces appear (diamond first, spade next, etc.). All the permutations result in the same combination.

GetNumberPermutations

Procedure

Get the number of combinations of intItems that can occur from intPool (order matters).
Unlike combinations, permutations count the number of ways (orders) items can be selected from the pool.
For instance, with playing cards, there is only one combination of 4 Aces (all four cards), but there are many permutations depending on the order the Aces appear (diamond first, spade next, etc.).
All the permutations result in the same combination.

GetTableMedianDAO

Procedure

Get the median of a table's field using DAO.
The median is the 50th percentile. Half the data is greater than this value and half is less than this value.

GetTableMedianADO

Procedure

Get the median of a table's field using ADO.
The median is the 50th percentile. Half the data is greater than this value and half is less than this value.

GetTableModeDAO

Procedure

Get the mode (most common value) of the named field in the specified Jet database. This function uses a SQL string to calculate the mode by counting by group.

GetTableModeADO

Procedure

Get the mode (most common value) of the named field in the specified Jet database. This function uses a SQL string to calculate the mode by counting by group.

QuickSortStatArray

Procedure

Recursively sort the passed array of doubles (quicksort algorithm).

Example Code for Using Module: MathStatistics
' Example of modMathStatistics
'
' To use this example, create a new module and paste this code into it.
' Then run the procedure by putting the cursor in the procedure and pressing:
' F5 to run it, or
' F8 to step through it linebyline (see the Debug menu for more options)
Private Sub Example_modMathStatistics()
' Comments: Example of the modMathStatistics module to demonstrate mathematical functions covering the areas of statistics and probability in VBA and VB6.
' See the results in the Immediate Window.
Const cstrSamplePath As String = "C:\Total Visual SourceBook 2013\Samples\"
Const cstrSampleDatabase As String = cstrSamplePath & "SAMPLE.MDB"
Const cstrSampleTable As String = "Orders"
Const cintElements As Integer = 10
Const cstrJetProvider351 As String = "Microsoft.Jet.OLEDB.3.51"
Const cstrJetProvider4 As String = "Microsoft.Jet.OLEDB.4.0"
Const cstrJetProvider12 As String = "Microsoft.ACE.OLEDB.12.0"
Const cstrJetProvider14 As String = "Microsoft.ACE.OLEDB.14.0"
Dim cnn As New ADODB.Connection
Dim intCounter As Integer
Dim dblMedian As Double
Dim dblMean As Double
Dim dblMode As Double
Dim lngN As Long
Dim dblSumX As Double
Dim dblSumX2 As Double
Dim dblVar As Double
Dim dblStdDev As Double
Dim dblList() As Double
Dim strProvider As String
ReDim dblList(1 To cintElements)
' Example of Factorial()
' Calculate the factorial of a value (n!) which is the product of n*(n1)*(n2)...*2
Debug.Print "Factorial of 3: " & Factorial(3) ' displays 6 (3 * 2)
Debug.Print "Factorial of 4: " & Factorial(4) ' displays 24 (4 * 3 * 2)
Debug.Print "Factorial of 5: " & Factorial(5) ' displays 120 (5 * 4 * 3 * 2)
' Example of FactorialRecursive()
' Calculate the factorial of a value (n!) which is the product of n*(n1)*(n2)...*2
Debug.Print "FactorialRecursive of 3: " & FactorialRecursive(3) ' Displays 6 (3 * 2)
Debug.Print "FactorialRecursive of 4: " & FactorialRecursive(4) ' Displays 24 (4 * 3 * 2)
Debug.Print "FactorialRecursive of 5: " & FactorialRecursive(5) ' Displays 120 (5 * 4 * 3 * 2)
' Example of GetArrayMean()
' Calculate the mean of an array of numbers.
' Note the specification of the 1 based array.
' If you use:
' ReDim dblList(10) As Double
' The analysis starts from element 0, and 11 values are used in the calculation (0 to 10).
' Create an array of values
For intCounter = 1 To 10
dblList(intCounter) = intCounter
Next intCounter
dblMean = GetArrayMean(dblList()) ' Calculates 5.5
Debug.Print "Mean of Array: " & dblMean
' Example of GetArrayMedian()
' Calculate the median of an array of numbers.
' An example using a simple array is shown, followed by a random array with option to sort or not sort the array.
'
' Note, in this example, of the specification of the 1 based array. If you use:
' ReDim dblList(10) As Double
' The analysis starts from element 0, and 11 values are used in the calculation (0 to 10).
' A simple list of integers
For intCounter = 1 To cintElements
dblList(intCounter) = intCounter
Next intCounter
dblMedian = GetArrayMedian(dblList(), True)
Debug.Print "Median of Array: " & dblMedian
' A random list of numbers
For intCounter = 1 To cintElements
dblList(intCounter) = 100 * Rnd
Next intCounter
' Display original array
Debug.Print "Original array:"
For intCounter = 1 To cintElements
Debug.Print dblList(intCounter)
Next intCounter
' Change the sort parameter from True to False to see the impact on the array order:
dblMedian = GetArrayMedian(dblList(), True)
Debug.Print vbCrLf & "Median: " & dblMedian
Debug.Print vbCrLf & "Final array:"
For intCounter = 1 To cintElements
Debug.Print dblList(intCounter)
Next intCounter
' Example of GetArrayMode()
' Calculate the mode of an array of numbers.
' An example using a simple array is shown, followed by a random array with option to sort or not sort the array.
'
' Note, in this example, of the specification
' of the 1 based array. If you use:
' ReDim dblList(10) As Double
' The analysis starts from element 0, and 11 values are used in the calculation (0 to 10).
' A simple list of integers
For intCounter = 1 To cintElements
dblList(intCounter) = intCounter
Next intCounter
dblMedian = GetArrayMode(dblList(), True)
Debug.Print "GetArrayMode: " & dblMedian
' A random list of numbers
For intCounter = 1 To cintElements
dblList(intCounter) = Int(5 * Rnd)
Next intCounter
' Display original array
Debug.Print "Original array:"
For intCounter = 1 To cintElements
Debug.Print dblList(intCounter)
Next intCounter
' Change the sort parameter from True to False to see the impact on the array order:
dblMedian = GetArrayMode(dblList(), True)
Debug.Print ""
Debug.Print "Median: " & dblMedian
Debug.Print ""
Debug.Print "Final array:"
For intCounter = 1 To cintElements
Debug.Print dblList(intCounter)
Next intCounter
' Example of GetArrayStatistics()
' Several basic descriptive calculations are generated for an array of numbers. The values
' are calculated by the procedure and passed back to the calling procedure through the passed parameters.
'
' In this example, the array is 1 based.
' If you use:
' ReDim dblList(20) As Double
' The analysis starts from element 0, and 21 values are used in the calculation (0 to 20).
'
' Fill an array with random numbers with values between 0 and 10.
' This will create a new set of numbers each time this procedure is run.
ReDim dblList(20)
For intCounter = 1 To 20
dblList(intCounter) = 10 * Rnd
Next intCounter
' Calculate results and set the last 6 parameters:
GetArrayStatistics dblList(), lngN, dblMean, dblSumX, dblSumX2, dblVar, dblStdDev
' Display the results in the immediate window:
Debug.Print "Count : " & lngN
Debug.Print "Mean : " & dblMean
Debug.Print "Sum : " & dblSumX
Debug.Print "Sum Squared: " & dblSumX2
Debug.Print "Variance : " & dblVar
Debug.Print "Std.Dev. : " & dblStdDev
' Example of GetArrayStdDev()
' Calculate the standard deviation of an array of numbers. Note, in this example, of the specification of the 1 based array.
' If you use:
' ReDim dblList(10) As Double
' The analysis starts from element 0, and 11 values are used in the calculation (0 to 10).
' Fill an array with random numbers with values between 0 and 10
' Array is different for each run.
For intCounter = 1 To 20
dblList(intCounter) = 10 * Rnd
Next intCounter
dblStdDev = GetArrayStdDev(dblList())
Debug.Print "GetArrayStdDev: " & dblStdDev
' Example of GetNumberCombinations()
' Calculate the number of combinations of items given a certain population (pool) size.
Debug.Print GetNumberCombinations(10, 1) ' displays 10
Debug.Print GetNumberCombinations(10, 2) ' displays 45
Debug.Print GetNumberCombinations(10, 3) ' displays 120
Debug.Print GetNumberCombinations(10, 5) ' displays 252
Debug.Print GetNumberCombinations(10, 10) ' displays 1
' Example of GetNumberPermutations()
' Calculate the number of ways a set of items can be selected from a given population.
' The number of permutations of picking 4 cards out of 4 cards:
Debug.Print GetNumberPermutations(4, 4) ' displays 24
' Permutations with a population of 10:
Debug.Print GetNumberPermutations(10, 1) ' displays 10
Debug.Print GetNumberPermutations(10, 2) ' displays 90
Debug.Print GetNumberPermutations(10, 3) ' displays 720
Debug.Print GetNumberPermutations(10, 5) ' displays 30240
Debug.Print GetNumberPermutations(10, 10) ' displays 3628800
' Use ACE provider to support the ACCDB version of Access.
#If VBA7 Then
strProvider = cstrJetProvider12
#Else
strProvider = cstrJetProvider4
#End If
cnn.Open "Provider=" & strProvider & ";Data Source=" & cstrSampleDatabase
' Example of GetTableMedianDAO and GetTableMedianADO
' Calculate the median of a field.
' The median is the 50th percentile. Half the data is greater than this value and less than this value.
dblMedian = GetTableMedianDAO(cstrSampleDatabase, cstrSampleTable, "Freight")
Debug.Print "GetTableMedianDAO: " & dblMedian
dblMedian = GetTableMedianADO(cnn, cstrSampleTable, "Freight")
Debug.Print "GetTableMedianADO: " & dblMedian
' Example of GetTableModeDAO and GetTableModeADO
' Calculate the mode of a field in a table.
dblMode = GetTableModeDAO(cstrSampleDatabase, cstrSampleTable, "Freight")
Debug.Print "GetTableModeDAO: " & dblMode
dblMode = GetTableModeADO(cnn, cstrSampleTable, "Freight")
Debug.Print "GetTableModeADO: " & dblMode
cnn.Close
Set cnn = Nothing
End Sub
Overview of Total Visual SourceBook
The source code in Total Visual
SourceBook includes modules and classes for Microsoft Access, Visual
Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily
add this professionally written, tested, and documented royaltyfree code
into your applications to simplify your application development efforts.
Additional Resources

