How To Rank A Multi Dimensional Array In VBA

1
votes

I'm embarrassed I couldn't find this answer on my own, but if I have a multi-dimensional VBA array, and wish to use the Rank/Large/Small function on a single dimension, how is that executed?

Example:

Dim Arr(1, 2) As Integer

    Arr(0, 0) = 1
    Arr(0, 1) = 2
    Arr(0, 2) = -1
    Arr(1, 0) = 100
    Arr(1, 1) = 40
    Arr(1, 2) = 60

Here's a visual illustration:

enter image description here

If I want the largest number in the Arr(0, x) field (which would be 2), how is that executed? For the largest in the Arr(1,x) field I would expect 100.

I've tried:

Application.WorksheetFunction.Large(Arr(, 2), 1) this errors Application.WorksheetFunction.Large(Arr(1, 2), 1) this gives just that array on that postion.

I've googled:

  • ranking multi-dimensional arrays
  • how to rank a single dimension an array
  • rank one dimension in array

No luck. I could probably find it, but I'm also hoping I might get a -3 ranking on this weak question so I get can delete it and earn the SO "humiliation" badge (or whatever it's called).

Thanks!

3
arraysexcelvba
stackoverflow.com/questions/7031416/… – Tim Williams

3 Answers

2
votes

You need to peel out one of the ranks with the worksheet's Index function.

Dim Arr(1, 2) As Integer

Arr(0, 0) = 1
Arr(0, 1) = 2
Arr(0, 2) = -1
Arr(1, 0) = 100
Arr(1, 1) = 40
Arr(1, 2) = 60

Debug.Print Application.Large(Application.Index(Arr, 0, 2), 1)

Index is used as all the 'rows' (0) in the second 'column' (2). Between 2 and 40 in the second rank, 40 is the largest.

0
votes

Just to follow up on Jeeped answer (as it took me a couple minutes to figure out):

With

Dim Arr(1, 2) As Integer

Arr(0, 0) = 1
Arr(0, 1) = 2
Arr(0, 2) = -1
Arr(1, 0) = 100
Arr(1, 1) = 40
Arr(1, 2) = 60

Here is how you would return the top ranked member for the Arr(0,x): Application.Large(Application.Index(Arr, 1, 0), 1) Returns 2

For the second set Arr(1,x): Application.Large(Application.Index(Arr, 2, 0), 1) Returns 100

0
votes

You could use a for loop like

Dim large As Double

large = arr(row, 0)

For i = 1 To numberOfColumns
  If (arr(row, i) > large) Then
    large = arr(row, i)
  End If
Next i