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. A Do-Loop is used to iterate through the cells in the worksheet's column A until an empty cell is found. 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Ĭells(I, "B").Value = myArray(I - 1) Next I End SubĪfter declaring the dynamic array, you must determine the required size of the array. Convert text to numbers.įor I = 1 To arraySize myArray(I - 1) = Val(Cells(I, "A").Value) Next I Do anotherIteration = False For I = 0 To arraySize - 2 I = I + 1 Loop Until Cells(I, "A").Value ReDim myArray(arraySize - 1) Public Sub DynamicBubble() Dim tempVar As Integer Dim anotherlteration As Boolean Dim I As Integer Dim arraySize As Integer Dim myArray() As Integer The BubbleSort2() and Transpose() sub procedures are now rewritten using dynamic arrays. You will see an example of using ReDim Preserve in the Math Game project at the end of the chapter. 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.
Normally, an array is re-dimensioned with the Preserve keyword only when the new size is larger than the previous size of the array. 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. If you need to preserve the existing values then use the Preserve keyword. The ReDim statement will re-initialize (erase) all elements of the array. Therefore, avoid using ReDim as a declarative statement, but use it to re-size previously declared arrays. 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.
To declare a dynamic array, use empty parentheses instead of a value for the bound(s).Īfter the required length of the array has been determined then the array is re-dimensioned using the ReDim keyword. The size of a dynamic array can be changed (increased or decreased) as necessary while the program runs. 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. 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. The number of values in fixed length arrays cannot be changed while the program is running. The BubbleSort2() and Transpose() sub procedures use arrays with fixed lengths.
0 kommentar(er)
