Excel VBA Arrays - Master arrays in VBA | Use arrays instead of ranges in Excel VBA
We know very well that a variable is a container to store a value. Sometimes, developers are in a position to hold more than one value in a single variable at a time. When a series of values are stored in a single variable, then it is known as an array variable.
Arrays are declared the same way a variable has been declared except that the declaration of an array variable uses parenthesis.
Parameter Description
Preserve − An optional parameter used to preserve the data in an existing array when you change the size of the last dimension.
Varname − A required parameter, which denotes the name of the variable, which should follow the standard variable naming conventions.
Subscripts − A required parameter, which indicates the size of the array.
Types of Arrays in VBA
VBA supports two types of arrays namely;
Static – These types of arrays have a fixed pre-determined number of elements that can be stored. One cannot change the size of the data type of a Static Array. These are useful when you want to work with known entities such as the number of days in a week, gender, etc.
For Example: Dim ArrayMonth(12) As String
Dynamic – These types of arrays do not have a fixed pre-determined number of elements that can be stored. These are useful when working with entities that you cannot predetermine the number.
For Example: Dim ArrayMonth() As Variant
Array Dimensions
An array can be one dimension, two dimensions or multidimensional.
One dimension: In this dimension, the array uses only one index. For example, a number of people of each age.
Two dimensions: In this dimension, the array uses two indexes. For example, a number of students in each class. It requires number of classes and student number in each class
Multi-dimension: In this dimension, the array uses more than two indexes. For example, temperatures during the daytime. ( 30, 40, 20).
#practicalit #vbaarrays #vbatutorials