Note See Chapter for more information on using the Msg Box function

0 Switch to Excel and run the macro.

■ The message box shows the array element specified in step 4.

REDIMENSION AN ARRAY

Yi ou can change the size of an array by redimensioning it using the ReDim statement. You can change the size of a dynamic array at any time within a procedure.

VBA lets you declare two different types of arrays, fixed-size and dynamic arrays. When you declare a fixed-size array, you specify the number of elements in the array. For example, the following code statement creates a fixed array with 15 elements: Dim NewArray(15) As Integer.

If you do not know how large to make the array when you declare it, you can use a dynamic array. A dynamic array does not have a size until you use the ReDim statement within your procedure to change the array size. You can use the Dim statement, without a size to create the array, as in the example: Dim NewArray() As Integer.

When you are ready, you can use the ReDim statement to size the array so you can add values. For example, in the code ReDim NewArray(1 To 15), the array is initially declared as a dynamic array with an unknown number of elements. The array is redimensioned to contain 15 elements using the ReDim statement.

VBA does not enable you to redimension a fixed-size array. If you attempt to change the size of a fixed-size array, you receive an "Array already dimensioned" error message. If the array was initially declared as a dynamic array, however, you can use the ReDim statement multiple times within a procedure to change the size of an array.

Each time you redimension an array, you destroy the existing elements in that array. If you want to preserve the existing values in the array, use the Preserve statement. For example, the statement ReDim Preserve NewArray(10) instructs VBA to resize the array to 10 elements and maintain any existing values. If the array has five values, those values remain the first five values in the resized array. If the array has 15 values, the first ten values in the array are maintained.

REDIMENSION AN ARRAY

REDIMENSION AN ARRAY

'-H Create a new subroutine.

B Type Dim ArrayName() As DataType, replacing ArrayName with the name of the array variable and DataType with the type of values the array will store.

< Type ReDim ArrayName(N), replacing N with the upper bounds of the array.

Specify the values for each element of the array.

0 Use the MsgBox function to view an element of the array.

0 0

Post a comment