Variable can store more than one value at a time. When you store series of values in a variable then it is called as an array variable. To declare an array, we use the same process as we declare a variable with only change is of parenthesis. While declaring an array variable, we use parenthesis. Here we will be going to discuss VBA Array in Excel.
VBA Array: Define
The VBA Array is a type of variable that stores the list of data and values of similar types. Normally a variable can store many values at a time but in terms of VBA, a normal variable can store a single value at a time.
For example, if you want to store the marks of the students via VBA then you need many variables.
How to use the VBA Array?
- Let’s take an example of three students. Read these marks and write it on the immediate window.
- To open immediate window, click on the developer tab.
Also check Pivot Chart
- In the developer tab, you will find the Visual Basic option.
- Click on this option, find an immediate window where you can write the code.
- Write code
Public Sub StudentMarks( )
Declare variable for every student
Dim Student1 as Integer
Dim Student2 as Integer
Dim Student3 as Integer
Student1 = .Range(“C2”).Offset(1)
Student2 = .Range(“C2”).Offset(2)
Student3 = .Range(“C2”).Offset(3)
Debug.Print “Student Marks”
This code will show you the result in the Immediate Window.
In this, we have added code for every student. Think if you have 1000 of students then you need to write 3000 approx lines of code. Hence to make it easier we use arrays. With arrays, you can store lots of data items in a single structure. Check the VBA code:
Public Sub StudentMarksArr( )
Declare an array
Dim Students(1 to 3) as integer
Read student marks
Dim i as integer
For I = 1 to 3
Students(i) = .Range(“C2”).Offset(i)
Print Student Marks
Debug.Print “Students Marks”
For I = Lbound(Students) to Ubound(Students)
Hence this loop will work until the completion of the array. It will continue till the table gets completed.
Different Types of VBA Arrays
There are two types of arrays available:
Static: The static array is an array that has fixed size.
Dynamic: The array whose size was set at the runtime.
How to assign values to an Array
For both static and dynamic types of arrays we assign values in a similar way:
Public Sub AssignValue( )
Dim arrMarks(0 To 3) As long
arrMarks(0) = 5
arrMarks(3) = 46
arrMarks(4) = 99
You will find various inbuilt functions are there in VBScript that helps the developers in handling arrays. These inbuilt functions are
Also Check Excel functions list
LBound: Returns an integer that has the smallest subscript.
UBound: Returns an integer that has the largest subscript.
Join: Returns a string that contains the number of substrings.
Split: Returns an array with the specified number of values.
IsArray: Returns Boolean value.
Filter: Returns a zero-based array consisting subset of string arrays depending on the specific filter criteria
Erase: Recovers the allocated memory of array variables.