How to determine the three smallest values in a variable using excel VBA?

Question

I have an array for instance M = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] and I want to get the top 3 smallest which are: [1, 2, 3]. How do I do this in excel VBA?

I want to get 3 smallest values for each row, i, of the array Min_NDate(i, j) in my actual code as shown below:

For i = 1 To Total_Rows_Help
    For j = 1 To Total_Rows_Help
        Min_NDate(i, j) = Worksheets("Help Worksheet").Cells(i, 2) - Worksheets("Help Worksheet").Cells(j, 2)
    Next j
Next i

Answer 1

Just use the application's SMALL function.

dim m as variant, k as long
m = array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
for k=1 to 3
    debug.print application.small(m, k)
    debug.print application.large(m, k)
next k

I have included a worksheet's LARGE functionality as I want to empirically demonstrate that I am not simply writing off the first three elements of the array (which is sorted in an ascending manner).