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 describe four different sorting techniques:

♦ 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 and is limited to arrays with no more than 65,536 elements —the number of rows in a worksheet.

♦ 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.

♦ Quick sort is a much faster sorting routine than Bubble Sort, but it is also more difficult to understand. This technique only works with Integer or Long data types.

♦ Counting sort is lightning fast but also difficult to understand.

The companion CD-ROM includes a workbook application that demonstrates these sorting methods.This workbook is useful for comparing the techniques with arrays of varying sizes.

Figure 11-11 shows the dialog box for this project. I tested the sorting procedures with seven different array sizes, ranging from 100 to 100,000 elements (random numbers). The arrays contained random numbers (of type Long).

Figure 11-11: 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). Note that the worksheet sort is limited to 65,536 items.

Table 11-1 SORTING TIMES IN SECONDS FOR FOUR SORT ALGORITHMS USING RANDOMLY FILLED ARRAYS

Array

Excel

VBA

VBA

VBA

Elements

Worksheet

Bubble

Quick

Counting

Sort

Sort

Sort

Sort

100

0.03

0.01

0.00

0.02

500

0.04

0.03

0.01

0.02

1,000

0.04

0.15

0.01

0.03

5,000

0.10

0.50

0.02

0.03

10,000

0.19

14.55

0.05

0.09

50,000

0.95

329.24

0.27

0.09

100,000

N/A

1,199.70

0.54

0.17

The worksheet sort algorithm is amazingly fast, especially when you consider that the array is transferred to the sheet, sorted, and then transferred back to the array. If the array is almost sorted, the worksheet sort technique is even faster.

The bubble sort algorithm is reasonably fast with small arrays, but for larger arrays (more than 5,000 elements), forget it. The quick sort algorithm is a winner, but the counting sort wins by a long shot.

Was this article helpful?

0 0

Post a comment