Calculate the standard deviation of elements in an array

Category:

Dates and Math

Type:

Snippets

Difficulty:

Intermediate

Author:

Intelligent Solutions Inc.

Version Compatibility:

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

'Function takes an array with numeric elements as a parameter
'and calcuates the standard deviation

Public Function StandardDeviation(NumericArray As Variant) _ 
As Double

Dim dblSum As Double, dblSumSqdDevs As Double, dblMean As Double
Dim lngCount As Long, dblAnswer As Double
Dim vElement As Variant
Dim lngStartPoint As Long, lngEndPoint As Long, lngCtr As Long

On Error GoTo errorhandler
'if NumericArray is not an array, this statement will
'raise an error in the errorhandler

lngCount = UBound(NumericArray)

On Error Resume Next
lngCount = 0

'the check below will allow
'for 0 or 1 based arrays.

vElement = NumericArray(0)

lngStartPoint = IIf(Err.Number = 0, 0, 1)
lngEndPoint = UBound(NumericArray)

'get sum and sample size
For lngCtr = lngStartPoint To lngEndPoint
  vElement = NumericArray(lngCtr)
    If IsNumeric(vElement) Then
      lngCount = lngCount + 1
      dblSum = dblSum + CDbl(vElement)
    End If
Next

'get mean
If lngCount > 1 Then
    dblMean = dblSum / lngCount

'get sum of squared deviations
    For lngCtr = lngStartPoint To lngEndPoint
        vElement = NumericArray(lngCtr)

If IsNumeric(vElement) Then
            dblSumSqdDevs = dblSumSqdDevs + _
            ((vElement - dblMean) ^ 2)
        End If
    Next

'divide result by sample size - 1 and get square root. 
'this function calculates standard deviation of a sample.  
'If your  set of values represents the population, use sample
'size not sample size - 1

If lngCount > 1 Then
        lngCount = lngCount - 1 'eliminate for population values
        dblAnswer = Sqr(dblSumSqdDevs / lngCount)
    End If
    
End If

StandardDeviation = dblAnswer

Exit Function

errorhandler:
Err.Raise Err.Number
Exit Function
End Function