How VBA arrays are used

1, declare an array.

Arrays are declared the same way as other variables, and can be declared using Dim, Static, Private, or public statements. The difference between a scalar quantity (not an array) and an array variable is that you usually have to specify the size of the array. If the size of the array is specified, it is a fixed-size array. If the size of the array can be changed while the program is running, it is a dynamic array.

Whether an array is indexed from 0 or 1 is based on the setting of the option Base statement. If option Base is not specified as 1, the array index starts at zero.

(1) Declaring an array of fixed sizes

The following line of code declares a fixed-size array, which is an Integer array of 11 rows multiplied by 11 columns:

Dim MyArray as Integer

(2) declaring a dynamic array

If declared as a dynamic array, you can change the size of the array while the code is executing. You can declare an array with Static, Dim, Private, or public statements, and leave the parentheses blank, as shown in the following example.

Dim Sngarray () as single

2. Get the maximum and minimum subscript of the array. By using the LBound function and the UBound function function, the minimum and maximum subscript of the array can be obtained respectively, and its syntax is:

LBound (arrayname[, Dimension])

UBound (arrayname[, Dimension])

The syntax contains the following sections:

Partial description

Arrayname is required. The name of the array variable, followed by a standard variable naming convention.

Dimension optional; Variant (Long). Specifies which dimension's lower bound is returned. 1 means the first dimension, 2 represents the second dimension, and so on. If dimension is omitted, it is considered to be 1.

3, ReDim statement. The ReDim statement is used to define or redefine the size of a dynamic array that has previously been declared with a Private, public, or Dim statement with empty parentheses (no dimension subscript), and its syntax is:

ReDim [Preserve] VarName (subscripts) [as type] [, VarName (subscripts) [as type]] ...

The syntax of the REDIM statement includes the following sections:

Partial description

Preserve is optional. keyword, you can use this keyword to keep the original data in an array when you change the size of the last dimension of the original array.

VarName is required. The name of the variable, followed by the standard variable naming convention.

Subscripts is required. The dimension of the array variable; You can define up to 60 dimensions of multidimensional arrays. The subscripts parameter uses the following syntax:

[Lower to] upper [, [Lower to] upper] ...

If lower is not explicitly specified, the lower bound of the array is controlled by the option Base statement. If there is no option Base statement, the lower bound is 0.

Type is optional. The data type of the variable, which can be Byte, Boolean, Integer, Long, Currency, single, Double, Decimal (not currently supported), Date, string (pair of variable length strings), String * Length (a fixed-length string), object, Variant, user-defined type, or object type. Each variable declared must have a separate as type clause. For a Variant that contains an array, type describes the types of each element of the array, and you cannot change the variant to another type.

You can use the ReDim statement to repeatedly change the elements of the array and the number of dimensions, but you cannot define an array as a data type, and then use ReDim to change the array to another data type, unless it is an array contained in a Variant. If the array is indeed contained in a variant, and the Preserve keyword is not used, you can use the AS type clause to change the type of its elements, but it is not allowed to change any data type when this keyword is used.

If you use the Preserve keyword, you can only redefine the size of the last dimension of the array, and you cannot change the number of dimensions at all. For example, if the array is one-dimensional, you can redefine the size of the dimension because it is the last dimension and the only one-dimensional one. However, if the array is a two-dimensional or more dimension, only the last dimension can be changed to retain the contents of the array at the same time.