VBA: Enhance and speed up random numbers and sorting, using spill functions

Sorting Arrays
VBA does not have a built in sort function, which requires having your own sorting code. But now, the Excel Sort function can be used in VBA, and based on my testing, it is faster than my quicksort function.
Note that the array must be 2D, eg (1000,1) or (500,3)

For example if you have an array A, you can sort it into an array B like this

VBA Code:

B = Application.WorksheetFunction.Sort(A)

you can include a sort column and sort direction if needed, just as in...

Click to expand...

Dan_W said:

Thank you for this. It hadn't yet occurred to me to use the SORT function in VBA, so this is extremely useful. Your point about how the array must be 2D jumped out at me. Is this because the SORT function expects a range as an argument?

I encountered the same issue when I tried using the UNIQUE function in VBA, and while it just wouldn't work with a simple 1D array (e.g., MyArray(1 to 10), passing it a range as a argument (a list in Range("A1:A10")) would work fine. Obviously, Range("A1:A10") converts into a 2D array, so I'm guessing that's also what's happening with SORT?

Click to expand...