Need help with tweaking a UDF that utilizes Excel's MODE function

[cookch10msu]

I had question regarding some code that I came across to obtain the 1st, 2nd, 3rd etc. MODE within a VBA UDF function.

Specifically, I was wondering if anyone knows whether it is possible to pass a string of values to this function within a UDF; let me elaborate.

I currently have a function that does the following (since I do not have a method of posting an image at the moment), lets assume this is the worksheet, and my udf is called GetValues().

Column/Row
A | B
-----------
1|1,2 | 2,3
2|3 | 9,1
3|22,3| 2,4
4|1 | 2

In my sheet, I have cells with numbers, some with 1, or some with more than 1 number within the cell (as shown above).

If I run my UDF, GetValues(A1:A4) it would basically pull out all the numbers into an array, which would be joined to output the value (assuming I use the join function with "," as the delimeter):
1,2,3,22,3,1

Here is the code for my function:
------------------------------------------------
Function GetValues(mKa As Range)
Dim StackIt()
Dim i As Integer

i = 0
For Each mKa In mKa

ReDim Preserve StackIt(i)

StackIt(i) = Trim(mKa.Cells.Value)

i = i + 1
Next mKa

GetValues = Join(StackIt, ",")
End Function
------------------------------------------------

Here is a function that I came across to obtain first, second, third mode etc, which if I ran it on the previous set of values, moder(1,2,3,22,3,1) --> would return 1,3 as the 2 modes of that set of numbers.

moder() Function:
------------------------------------------------
Function moder(x As Range)
Dim modes As New Collection
For Each ce In x
If WorksheetFunction.CountIf(x, ce) = WorksheetFunction.CountIf(x, WorksheetFunction.Mode(x)) Then
On Error Resume Next
modes.Add Item:=ce, Key:=Str(ce)
End If
Next ce
For i = 1 To modes.Count

moder = moder & "," & modes(i)
Next i
moder = WorksheetFunction.Substitute(moder, ",", "", 1)
End Function
------------------------------------------------

What I would like to be able to do, is take my joined array value(s), and pass it within my GetValues function to this MODER function; yielding a final output of 1,3 (the 2 modes obtained for the values A1:A4 that I put together with my GetValues function). I cannot get this to work, and I have tried everything that I can think of.

I have done an exhaustive search for trying to figure this out, however I have hit a dead end, so if anyone could help I would greatly appreciate it. Thanks!

Chris

 

[SkipVought]

Hi,

Function GetValues(mKa As Range)
    Dim StackIt, r As Range
    Dim i As Integer
    
    i = 0
    For Each r In mKa
        StackIt = Split(Trim(r.Value), ",")
        For i = 0 To UBound(StackIt)
            GetValues = GetValues & StackIt(i) & ","
        Next
    Next
    
    GetValues = Left(GetValues, Len(GetValues) - 1)
End Function

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[cookch10msu]

Thanks for the reply Skip. I tried your code, however it isn't pulling out the common values, it just returns all the values.

ex.) When I try it on these values:

40
34, 39
34, 42
GetValues() returns --> 40,34, 39,34, 42

In this instance, tt should only return 34. Any thoughts?

 

[SkipVought]

I missed that aspect. I was confused by your use of MODES, which means something entirely different to me.

You could load all the values in an array using ReDim Preserve ONLY when the next value to load is not already in the array

Function GetValues(mKa As Range)
    Dim StackIt, r As Range, vOut(), jOut As Integer
    Dim i As Integer, j As Integer, bNotFound As Boolean
    
    i = 0
    jOut = 0
    For Each r In mKa
        StackIt = Split(Trim(r.Value), ",")
        For i = 0 To UBound(StackIt)
            bNotFound = True
            If jOut = 0 Then
                ReDim Preserve vOut(j)
                vOut(jOut) = StackIt(i)
                jOut = jOut + 1
            End If
            For j = 0 To UBound(vOut)
                If vOut(j) = StackIt(i) Then
                    bNotFound = False
                    Exit For
                End If
            Next
            If bNotFound And jOut > 0 Then
                GetValues = GetValues & StackIt(i) & ","
                ReDim Preserve vOut(jOut)
                vOut(jOut) = StackIt(i)
                jOut = jOut + 1
            End If
        Next
    Next
    
    GetValues = Left(GetValues, Len(GetValues) - 1)
End Function

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[cookch10msu]

Still not quite working, this is what I am getting:

40
34, 39
34, 42

getvalues --> 34, 39, 42
should be 34


14
10, 11
11, 14
getvalues --> 10, 11,11, 14
should be 11,14

Thoughts?

Is there no way to utilize excel's built in MODE function to get these values?

Chris

 

[SkipVought]

How about trying to tune it yourself.

I've given you a working function. Observe and figure out what its doing and how.

Modify to suite.

Post back with specific questions.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[cookch10msu]

Sounds good. Thanks for your help. I will let you know what I come up with.

Chris