Module: MathStatistics in Category General VBA/VB6 : Math from Total Visual SourceBook

' 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 line-by-line (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*(n-1)*(n-2)...*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*(n-1)*(n-2)...*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

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 royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

• List of All Modules in Total Visual SourceBook
• List of All Code Categories in Total Visual SourceBook
• Sample Module Code from Total Visual SourceBook (Example of Date, Time and Age Calculations)