How do I convert the Excel formula =STDEV.S(IF(FREQUENCY(range,range),range)) into VBA code?

Question

I have an Excel formula that operates on a pre-existing range of data.

The Excel formula is: =STDEV.S(IF(FREQUENCY(range,range),range)) , where "range" is the aforementioned range of data.

My goal is to convert this formula into VBA code.

The following code is my attempt at trying to convert the formula into VBA, as well as my visualization of the process to try and understand why it is not putting out the same result:

Private Sub CommandButton1_Click()

    Dim diffArray() As Variant
    Dim i As Integer
    Dim x As Integer
    Dim array1() As Variant, size As Integer, j As Integer
    Dim freqArray1() As Variant
    Dim freqArray2() As Variant, size2 As Integer, j2 As Integer

    'assigns the data values to array1
    size = 0
    j = 0
    ReDim array1(size)
    For i = 3 To 15
        size = size + 1
        ReDim Preserve array1(size)
        array1(j) = Cells(i, 2)
        j = j + 1
    Next i
    Cells(20, 2).Value = UBound(array1)
    Cells(21, 2).Value = LBound(array1)
    If UBound(array1) > 1 Then Cells(19, 2).Value = WorksheetFunction.StDev_S(array1)

    'setting freqArray1 to frequency(array1, array1)
    freqArray1 = WorksheetFunction.Frequency(array1, array1)
    Cells(20, 3).Value = UBound(freqArray1)
    Cells(21, 3).Value = LBound(freqArray1)
    For i = LBound(freqArray1) To (UBound(freqArray1))
        Cells(2 + LBound(freqArray1) + i, 3).Value = freqArray1(i, 1)
    Next i
    If UBound(freqArray1) > 1 Then Cells(19, 3).Value = WorksheetFunction.StDev_S(freqArray1)

    'setting freqArray2 to if(frequency(array1, array1), array1)
    size2 = 0
    j2 = 0
    ReDim freqArray2(size2)
    For i = LBound(freqArray1) To (UBound(freqArray1))
        If freqArray1(i, 1) Then
            size2 = size2 + 1
            ReDim Preserve freqArray2(size2)
            freqArray2(j2) = freqArray1(i, 1)
            j2 = j2 + 1
        End If
    Next i
    Cells(20, 4).Value = UBound(freqArray2)
    Cells(21, 4).Value = LBound(freqArray2)
    For i = (LBound(freqArray2)) To UBound(freqArray2)
        Cells(2 + LBound(freqArray2) + i, 4).Value = freqArray2(i)
    Next i

    'takes the standard deviation of if(frequency(array1, array1), array1)
    If UBound(freqArray2) > 1 Then Cells(19, 4).Value = WorksheetFunction.StDev_S(freqArray2)

End Sub

The data values being operated on are in the orange cells column B(array1). The array 'frequency(array1, array1)' is in the yellow cells column C. The array 'if(frequency(array1, array1), array1)' is in the green cells column D. The goal is for the values in the two blue cells(B18 and D19) to be the same.

I don't understand two things:

Why are the values in the blue cells(B18 and D19) not the same?
Why do the indices of the arrays change? One starts at '0', the next starts at '1', and the last starts at '-1'?

It is going to be easier to get the list of unique values using a dictionary, then iterate the dictionary into an array and do the stddev of that. — Scott Craner

Scott Craner Scott Craner · Accepted Answer · 2020-03-02T16:14:54

use a dictionary to create a unique list and use that in the StDev_S

Private Sub CommandButton1_Click()

    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    Dim rngArray As Variant
    rngArray = ActiveSheet.Range("B3:B15")

    Dim i As Long
    For i = LBound(rngArray, 1) To UBound(rngArray, 1)
        On Error Resume Next
            dict.Add rngArray(i, 1), rngArray(i, 1)
        On Error Resume Next
    Next i

    If dict.Count > 0 Then
        Dim unqArr As Variant
        ReDim unqArr(1 To dict.Count) As Variant

        i = 1

        Dim key As Variant
        For Each key In dict.Keys
            unqArr(i) = key
            i = i + 1
        Next key

        ActiveSheet.Cells(19, 4).Value = Application.WorksheetFunction.StDev_S(unqArr)
    End If


End Sub

How do I convert the Excel formula =STDEV.S(IF(FREQUENCY(range,range),range)) into VBA code?

1 Answers