VBA Arrays in Excel: 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.
- 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
- Instructions to Run VBA Macro Code
- Other Useful Resources
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 up-to 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.
You can refer the following link for the step by step instructions.
Click on the following links of the useful resources. These helps to learn and gain more knowledge.