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,

Please post VBA questions in Forum707.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[cookch10msu]

Sorry about that. Would you mind posting the link for Forum 707?

I am new to this board and am having some difficulty finding this particular forum.

Much appreciated,

Chris

 

[SkipVought]

http://www.tek-tips.com/threadminder.cfm?pid=707

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}