CODEWITHSUNDEEP
arraysexcelvba
Atl LED
Atl LED

Reputation: 666

Retrieving values of Mode Array in Excel

We have some spreadsheets with thousands of numbers where we would like to return the mode arrays. MODE.MULT can only handle 254 numbers.

Trying to overcome this, we split the mode arrays to check 252 cells of a single column. We were then going to check the Mode from among these numbers (understanding that this adds a degree of error, but considering the set size, we believe it to be small).

This led us to assigning a jagged array to the mode function, but we're having problems retrieving the values therein:

Dim ModeArray() As Variant 'Also tried ModeArray as not an array of variants but that left the same errors
Dim RowSet As Integer
RowSet = 2

Dim rr As Range

    For n = 2 To 252
    Set rr = Sheet5.Range(Sheet5.Cells(7, n), Sheet5.Cells(260, n))
        If Application.WorksheetFunction.CountBlank(rr) < 253 Then
            'Needed because a 1004 error will pop if there are no numbers in a particular column
            ModeArray = Application.WorksheetFunction.Mode_Mult(rr)

            For i = 1 To UBound(ModeArray)
                Sheet5.Cells(RowSet, n).Value = ModeArray(i) 
                'We get a few different errors. E.g. sub script out of range errors or if (1)(1) is tested "Object doesn't support this property or method (Error 438)" even though the TypeName(ModeArray(i)) is Double
                RowSet = 1 + RowSet
            Next
            RowSet = 2
        End If
    Next

We are only expecting 2-3 modes per column, but gave space for 5. That's not our problem, however. Trying to retrieve the information out of ModeArray doesn't work, and it's type is Variant().

How can we get the actual values of the mode out, so we can report them in another table? I'm aware I could put in array functions directly in the worksheet, but I'd like to avoid dealing with "N/A" values down stream, and I don't have a way to determine the length of the mode array in a function.

Alternatively, is there a way to skip this all together and retrieve the modes of a very large data set?

Edit:

I should note that the above script will work if there is only one Mode, it's a problem if there are more than one modes.

Upvotes: 2

Views: 174

Answers (1)

Charles Williams
Charles Williams

Reputation: 23540

MODE.MULT can handle more than 254 numbers in each parameter - its limitation is that it cannot handle more than 254 different parameters.

Sub modes()
    Dim modearray As Variant
    Dim j As Long
    modearray = Application.WorksheetFunction.Mode_Mult(Range("Sheet1!A:B"))
    If UBound(modearray) = 1 Then
        Debug.Print modearray(1)
    Else
        For j = LBound(modearray) To UBound(modearray)
            Debug.Print modearray(j, 1)
        Next j
    End If
End Sub

This works (a little slowly) for several thousand numbers in cols A & B

Upvotes: 1

Related Questions