The BubbleSort2() and Transpose() sub procedures use arrays with fixed lengths. The number of values in fixed length arrays cannot be changed while the program is running. This is fine as long as the required length of the array is known before running the program; however, the use of dynamic arrays allows programmers to create a more robust program. Wouldn't the BubbleSort2() procedure be more useful if it sorted data with any number of values rather than just ten values? A similar question can be asked of the Transpose() procedure —wouldn't it be more useful if it worked with any size data set rather than just a set with 10 rows and 3 columns? If you do not want to limit the BubbleSort2() and Transpose() sub procedures to constant-sized data sets, then you must use dynamic arrays.
The size of a dynamic array can be changed (increased or decreased) as necessary while the program runs. To declare a dynamic array, use empty parentheses instead of a value for the bound(s).
Dim myArray() As Integer
After the required length of the array has been determined then the array is re-dimensioned using the ReDim keyword.
ReDim can also be used as a declarative statement with arrays, but potential conflicts may arise if there are variables of the same name within your project-even if they are of different scope. Therefore, avoid using ReDim as a declarative statement, but use it to re-size previously declared arrays.
The ReDim statement will re-initialize (erase) all elements of the array. If you need to preserve the existing values then use the Preserve keyword.
ReDim Preserve myArray(size)
If the new size of the array is smaller than the original size, then the values of the elements at the end of the array are lost. Normally, an array is re-dimensioned with the Preserve keyword only when the new size is larger than the previous size of the array. When re-sizing an array with the Preserve keyword, you can only change the size of the last dimension; you cannot change the number of dimensions, and you can only change the value of the upper bound. You will see an example of using ReDim Preserve in the Math Game project at the end of the chapter.
The BubbleSort2() and Transpose() sub procedures are now rewritten using dynamic arrays.
Public Sub DynamicBubble() Dim tempVar As Integer Dim anotherlteration As Boolean Dim I As Integer Dim arraySize As Integer Dim myArray() As Integer
'Get the array size.
Do arraySize = I
I = I + 1 Loop Until Cells(I, "A").Value ReDim myArray(arraySize - 1)
'Get the values. Convert text to numbers.
For I = 1 To arraySize myArray(I - 1) = Val(Cells(I, "A").Value) Next I Do anotherIteration = False For I = 0 To arraySize - 2
If myArray(I) > myArray(I + 1) Then tempVar = myArray(I) myArray(I) = myArray(I + 1) myArray(I + 1) = tempVar anotherIteration = True End If Next I
Loop While anotherIteration = True
'Write data to column B.
For I = 1 To arraySize
Cells(I, "B").Value = myArray(I - 1) Next I End Sub
After declaring the dynamic array, you must determine the required size of the array. A Do-Loop is used to iterate through the cells in the worksheet's column A until an empty cell is found. By keeping track of the number of iterations with the variable I , the number of values in the column—and hence the required size of the array—is discovered. Then the array is re-dimensioned with the appropriate variable and ReDim statement.
This is not the best method for learning how many values the user has entered into column A of the worksheet, as the potential for error is high. For example, any text entered into a cell will be converted to a numerical value with the Val() function—ususally zero. The procedure also limits the sort to data entered into column A of the worksheet. In the next chapter, I'll discuss additional methods for allowing the user more flexibility in terms of where the data can be input, and gathering user input such that ambiguities in the data are minimized.
The rest of the DynamicBubble() procedure is the same as the BubbleSort2() procedure except the upper limit of all looping variables are set to the same value as the size of the array.
The DynamicTranspose() sub procedure is re-written using a dynamic array that is re-dimensioned with two dimensions. One dimension is for the number of rows in the grid of values to be transposed and the other dimension is for the number of columns.
Once again, Do-Loops are used to determine the number of rows and columns holding values in the worksheet. The array transArray is then re-dimensioned to the same number of rows and columns. Don't forget the lower bound on each dimension is 0. The rest of the procedure is the same, with the exception of the upper limit on the looping variables used in the For/Next loops.
Public Sub DynamicTranspose() Dim I As Integer Dim J As Integer Dim transArray() As Integer Dim numRows As Integer Dim numColumns As Integer
'Get rows for dynamic array.
Do numRows = I I = I + 1 Loop Until Cells(I, "A").Value = ""
'Get columns for dynamic array.
Loop Until Cells(1, Chr(I + 64)).Value = "" ReDim transArray(numRows - 1, numColumns - 1)
'Copy data from worksheet to array.
For I = 1 To numColumns For J = 1 To numRows transArray(J - 1, I - 1) = Val(Cells(J, Chr(I + 64)).Value) Next J Next I
'Copy data from array to worksheet transposed.
For I = 1 To numColumns For J = 1 To numRows
Cells(I, Chr(J + 64)).Value = transArray(J - 1, I - 1) Next J Next I End Sub
Was this article helpful?