Vertex42 The Excel Nexus

Normally, arrays are not discussed until the end of introductory programming books; however, as you are already familiar with spreadsheet applications, the concept of an array should come easily. An array is a variable that can hold multiple values. You should use arrays when a related set of values is to be stored in a variable. Doing so relieves you from having to declare a new variable with a unique name for each value in the set. Arrays are convenient as they simplify programming code tremendously.

A spreadsheet column that contains data is basically the same thing as an array—it's a group of related values. Each cell within a spreadsheet column containing the related set of values is referenced by a row and column index. Values in an array are also referenced using indices.

I assume that you organize your spreadsheets in the normal way—by placing data inside columns rather than rows—but the argument is the same whether you equate a spreadsheet column or row to an array.

Before starting with the simplest example of an array (the one-dimensional array), consider a sub procedure that uses a worksheet column much as a programmer would use an array in an application that does not work with a spreadsheet.

In previous chapters, and throughout this chapter I use the Cells property of the Excel Application object in code examples. The Cells property is straightforward, with a row and column index that corresponds to a single spreadsheet cell. Although discussed in detail in Chapter 5, be aware as you look at the examples in this chapter that the Cells property acts like a function that returns a Range object consisting of a single spreadsheet cell. I have used the Value property of the Range object extensively thus far, but the Range object has many other properties for the VBA programmer to use besides the Value property, and you will see many examples in this chapter and subsequent chapters.

The BubbleSort() procedure sorts a column of integer values from lowest to highest value. Two integer variables and a Boolean variable are all you need.

Public Sub BubbleSort()

'Sorts data in A2:A11 and writes sorted data to B2:B11 Dim tempVar As Integer Dim anotherlteration As Boolean Dim I As Integer

Range("A2:A11").Copy Range("B2:B11") 'Copy all data to column B Range("B1").Value = "Sorted Data" Do anotherlteration = False For I = 2 To 10

'Compare and swap adjacent values

If Cells(I, "B").Value > Cells(I + 1, "B").Value Then tempVar = Cells(I, "B").Value Cells(I, "B").Value = Cells(I + 1, "B").Value Cells(I + 1, "B").Value = tempVar anotherIteration = True End If Next I

A For/Next loop nested inside a Do-Loop will iterate through a column of 10 values until the data is sorted from lowest to highest value. The nested For/Next loop effectively pushes the largest value from wherever it is located to the last position, much like a bubble rising from the depths to the surface. The For/Next loop starts at the beginning of the data list and compares two successive values. If the first value is larger than the second value, then the position of the two values are swapped with help from the variable tempVar. The next two values are then compared, where the first of these values was the second value in the previous comparison (or first if it had been swapped). Please note: the row index in the Cells property uses I + 1, so the looping variable in the For/Next loop works from 2 to 11 so that the procedure sorts ten values. If a swap of two values has to be made, then the Boolean variable anotherIteration is set to true to ensure the outer Do-Loop continues with at least one more iteration.

Each iteration through the Do-Loop moves the next largest value in the set down the column to its correct position. Thus, it will take up to n iterations to sort the data, where n is the number of values in the set. This does not make the BubbleSort() procedure terribly efficient, but it works well for small data sets. The worksheet shown in Figure 4.3 illustrates what happens to a set of numbers after each iteration through the Do-Loop loop. Note that Figure 4.3 was created for display only; the BubbleSort() procedure sorts values from column A and copies them to column B only.

Worksheet illustration of the

BubbleSort() sub procedure.

Worksheet illustration of the

BubbleSort() sub procedure.

Was this article helpful?

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

## Post a comment