A single variable can store only one value of any data type at a time. Let us see how to store group of values in a variable. It’s possible with either arrays or collections. In this following tutorial learn about arrays.
- Overview of VBA Arrays in Excel
- What is an Array?
- Advantages of an Array
- Array Declaration
- Assigning Values to an Array
- Types of Arrays
- Array Dimensions
- ReDim Statement
- ReDim with Preserve Statement
- Split String into Array in Excel
- Read Values from Range to an Array
- Read Values from an Array to Range
- Store Variant Values in an Array
- Array Built-In Functions
An array is nothing but a group of values or elements of same data type store in a memory.
We have several advantages by using Arrays. let us see.
Array Declaration in Excel VBA is very good habit. Declaring a variable tells the computer to allocate space in memory for later use. We can declare variable using Dim, Public, Private, or Static statements.
An array values are assigned to an element based on an array index or subscript. Defining values for both static and dynamic array types are same.
There are two types of arrays.
- Static Array: A Static array is an array of fixed size. It stores pre-defined number of elements.
- Dynamic Array: A dynamic array is an array can be resized during run time. We can resize an array using ‘ReDim’ statement. A dynamic array does not have a pre-defined number of elements.
There are three types of dimensions.
- One-Dimensional Array:The One-Dimensional Arra has one dimension uses only one index(Subscript)
- Two-Dimensional Arrays:The Two-Dimensional array has two dimensions and uses two indexes(Subscriptss)
- Three-Dimensional Arrays:The Three-Dimensional array has three dimensions and uses three indexes(Subscriptss)
- Multi-Dimensional Arrays:The Multi-Dimensional Array has more than two dimensions and uses more than two indexes(Subscripts). We can have upto 60 dimensions in an array.
The ‘ReDim’ stament is used to initialize or resize a dynamic array.
Sub VBA_Array_ReDim() 'Declare an Array variable Dim aArrayName() As String ReDim aArrayName(0 To 1) As String 'Set the value of array index 0 aArrayName(0) = "Thanks" 'Set the value of array index 1 aArrayName(1) = "Welcome" End Sub
We can use multiple times ReDim statement in an array. When We use many times the ReDim statement in an array, memory storage reallocates its space.
'VBA Assigning Values to an Array Sub VBA_Array_ReDim_With_Preserve() 'Declare an Array variable Dim aArrayName() As Integer ReDim aArrayName(0 To 2) As Integer 'Set the value of array index 0 aArrayName(0) = 1 'Set the value of array index 1 aArrayName(1) = 2 'Set the value of array index 2 arrMarks(2) = 3 'Reallocates memory space for new elemnt in array ReDim Preserve aArrayName(3) As String 'Set the value of array index 3 arrMarks(3) = 4 End Sub
You can split string into array by using VBA Split function. It helps to create an array and store elements in an array.
You can read values from range, cell, table, row, column, etc.
Let us see the simple example how to read values from an array to range.
'VBA Read Values from an array to range Sub VBA_Read_Values_Array_To_Range() 'Declare an array variable Dim sWeekDays As Variant Dim iCnt As Integer 'Define an Array values sWeekDays = Array(1, 2, 3, 4, 5, 6, 7) 'Loop through each item in an array For iCnt = LBound(sWeekDays) To UBound(sWeekDays) Sheets("Sheet1").Range("A" & iCnt + 1) = sWeekDays(iCnt) Next End Sub
We can quickly create an array using variant type data by creating dynamic array. Let us see how to store variant values to an array quickly.
VBA Array Functions in Excel. These are different VBA Built-In functions. Those are LBound, UBound, IsArray, Erase, Split, Join and Filter. VBA Programmers use these functions in arrays to fasten process.
- LBound: The LBound function represents the lower bound value of an array. It returns the smallest subscript(Index) value of specified array.
- UBound: The UBound function represents the upper bound value of an array. It returns the highest subscript(Index) value of specified array.
- IsArray: The IsArray function checks the specified input variable is an array or not. It returns a boolean value either True or False.
- Erase: The Erase function is used to reset(reinitialise) the size of an array. It depends on type of an array.
- Split: The Split function helps us to know the number of values in an array by using delimiter.
- Join : The Join function helps us to join sub strings in an array and returns a single string. It is opposite of Split function.