arrays - How To Rank A Multi Dimensional Array In VBA

Question

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:

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!

标签： arraysexcelvba

Answer 1

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.