Microsoft Office Tutorials and References
In Depth Information
Sorting an array
Although Excel has a built-in command to sort worksheet ranges, VBA doesn’t offer a method to
sort arrays. One viable (but cumbersome) workaround is to transfer your array to a worksheet
range, sort it by using Excel’s commands, and then return the result to your array. But if speed is
essential, it’s better to write a sorting routine in VBA.
In this section, I cover four different sorting techniques:
h Worksheet sort transfers an array to a worksheet range, sorts it, and transfers it back to
the array. This procedure accepts an array as its only argument.
h Bubble sort is a simple sorting technique (also used in the Chapter 9 sheet-sorting
example). Although easy to program, the bubble-sorting algorithm tends to be rather slow,
especially when the number of elements is large.
h Quick sort is a much faster sorting routine than bubble sort, but it is also more difficult to
understand. This technique works only with Integer and Long data types.
h Counting sort is lightning fast, but also difficult to understand. Like the quick sort, this
technique works only with Integer and Long data types.
The companion CD-ROM includes a workbook application (named sorting demo.
xlsm ) that demonstrates these sorting methods. This workbook is useful for comparing
the techniques with arrays of varying sizes. However, you can also copy the procedures
and use them in your code.
Figure 11-16 shows the dialog box for this project. I tested the sorting procedures with seven
different array sizes, ranging from 100 to 100,000 elements. The arrays contained random numbers
(of type Long ).
Figure 11-16: Comparing the time required to perform sorts of various array sizes.
Table 11-1 shows the results of my tests. A 0.00 entry means that the sort was virtually
instantaneous (less than .01 second).