Specifying All Members of an Array

[FracinDean]

Good day Mr. Excel users,

Searching in the forum and online help have not helped me figure out how to use worksheet function Rank but using members of a VBA array as arguments. I made a simple example to work with. Here is what I have so far:

[FONT=Verdana]Sub ranker()
    Dim c As Variant, allVals() As Long
    ReDim allVals(10)
    For i = 1 To 10
        allVals(i) = Application.WorksheetFunction.RandBetween(0, 100)
    Next i
    For Each c In allVals
        Rank = Application.WorksheetFunction.Rank(c, allVals(), 0)  ' Error on this row
    Next c
End Sub
[/FONT]

The values I'm trying to rank are virtual within VBA, they do not appear in cells in my spreadsheet.

The code above is generating a compile error, "Array Type Mismatch" on the argument "allVals()".

Assistance will be appreciated.

 

[Kenneth Hobson]

Arg2 of the function requires a range.

Maybe something like this. View the Immediate Window in VBE after a run to see the results.

Sub ranker()
    Dim c, allVals, Rank, i As Integer
    ReDim allVals(1 To 10)
    For i = 1 To 10
        allVals(i) = WorksheetFunction.RandBetween(0, 100)
        Debug.Print i, allVals(i)
    Next i
    
    Debug.Print
    For i = 1 To UBound(allVals)
        Rank = fRank(allVals(i), allVals)  ' Error on this row
        Debug.Print i, allVals(i), Rank
    Next i
    
    Debug.Print
    Debug.Print Join(ArrayListSort(allVals), vbCrLf)
End Sub

Function fRank(v, a) As Integer
  Dim s
  s = ArrayListSort(a)
  fRank = PosInArray(v, s)
End Function

'If array is 0 based, 1 is returned if aValue matches anArray(0).
Function PosInArray(aValue, anArray)
  Dim pos As Long
  On Error Resume Next
  pos = -1
  pos = WorksheetFunction.Match(aValue, anArray, 0)
  PosInArray = pos
End Function

Function ArrayListSort(a As Variant, Optional bAscending As Boolean = True)
  With CreateObject("System.Collections.ArrayList")
    Dim cl
    For Each cl In a
      .Add cl
    Next
     
    .Sort 'Sort ascendending
    If bAscending = False Then .Reverse 'Sort and then Reverse to sort descending
    ArrayListSort = .Toarray()
  End With
End Function

 

[FracinDean]

Thank you for the solution. I had no idea it would be that complicated but your solution certainly works and I can modify it to suit if I need to.

One thing caught my eye in the re-dim statement -- what is the impact of changing the redim from "allVals(10)" to "allVals(1 to 10)"?

 

[Kenneth Hobson]

You can see from the code that you can add a parameter to set sort order. This is "sort" of what the 3rd parameter of =Rank() sort of thing. Even so, it is not perfect. Compare with some tests in worksheet formulas.

You could just set the size in the Dim. e.g. Dim a(1 to 10).

The default option is base 0. So, Dim a(10) has 11 elements. If you put Option Base 1 as first line of a Module, then element 1 starts as 1. Howsoever, arrays by Split() are always 0 based as first element index number.

Sub rd()
  Dim a(10), b(1 To 10)
  MsgBox LBound(a) & " " & LBound(b), , UBound(a) & " " & UBound(b)
End Sub