How to use Excel VBA arrays

1. Declare an array.

Arrays are declared in the same way as other variables, and can be declared using the dim, Static, Private, or public statements. The difference between a scalar (non-array) and an array variable is that you typically 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 size
The following line of code declares a fixed-size array, which is an Integer array of 11 rows multiplied by 11 columns:
Dim MyArray (Ten) as Integer
(2) Declaring dynamic arrays
If declared as a dynamic array, you can change the size of the array as you execute the code. You can use the Static, Dim, Private, or public statements to declare an array and leave the parentheses empty, as shown in the following example.
Dim Sngarray () as single

2. Get the maximum and minimum subscript of the array. The LBound function and the UBound function function can be used to obtain the minimum and maximum subscript of an array, with the following syntax:
LBound (arrayname[, Dimension])
UBound (arrayname[, Dimension])
The syntax contains the following sections:
Section description
Arrayname is required. The name of the array variable, followed by the standard variable naming convention.
Dimension optional; Variant (Long). Specifies which dimension to return to the lower bound. 1 represents 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 been declared with a Private, public, or Dim statement with empty parentheses (no dimension subscript), with the following syntax:
ReDim [Preserve] VarName (subscripts) [as type] [, VarName (subscripts) [as type]] ...
The syntax for the REDIM statement includes the following sections:
Section description
Preserve is optional. keyword, use this keyword to keep the original data in the 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 number of dimensions of the array variable; You can define a multidimensional array of 60 dimensions. 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 then the lower bound is 0.
The type is optional. The data type of the variable, which can be a Byte, Boolean, Integer, Long, Currency, single, Double, Decimal (not currently supported), date, string (string with variable length), String * Length (for fixed-length strings), 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 type of each element of the array and cannot change the variant to another type.
You can use the ReDim statement to repeatedly change the elements of an array and the number of dimensions, but you cannot use ReDim to change an array to a different data type after you have defined it as a data type, unless it is an array contained in a Variant. If the array is indeed contained in a variant and does not use the Preserve keyword, you can use the AS type clause to change the type of its elements, but it is not allowed to change any data type in the case of using this keyword.
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. However, if the array is a two-dimensional or more dimension, only the last dimension will be changed to retain the contents of the array at the same time.

How to use Excel VBA arrays