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.
- Objective
- 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
What is an Array?
An array is nothing but a group of values or elements of same data type store in a memory.
Advantages of an Array
We have several advantages by using Arrays. let us see.
Array Declaration
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.
Assigning Values to an Array
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.
Types of Arrays
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.
Array Dimensions
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.
ReDim Statement
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
ReDim with Preserve Statement
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
VBA Split String into Array in Excel
You can split string into array by using VBA Split function. It helps to create an array and store elements in an array.
Read Values from Range to an Array
You can read values from range, cell, table, row, column, etc.
Read Values from an Array to Range
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
Store Variant Values in an Array
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.
Array Built-In Functions
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.
Instructions to Run VBA Macro Code or Procedure:
You can refer the following link for the step by step instructions.
Instructions to run VBA Macro Code
Other Useful Resources:
Click on the following links of the useful resources. These helps to learn and gain more knowledge.
VBA Tutorial VBA Functions List VBA Arrays in Excel Blog
VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers