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

[Dermot]

Dermot submitted a new Excel article:

VBA: Enhance and speed up random numbers and sorting, using spill functions - 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

B = Application.WorksheetFunction.Sort(A)

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

Read more about this Excel article...

 

[Dan_W]

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?

 

[Dermot]

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?

I would guess that's the reason. The developers are having to juggle the introduction of spill functions with the risk of changing existing functions to be better behaved, so I think we'll be living with oddities like this for some time.