Excel VBA Array and collection are both used to store a group of related values. However, arrays are fixed in size, while collections can grow or shrink dynamically. Arrays are more efficient when storing large amounts of data, while collections are more flexible and easier to use when dealing with smaller amounts of data.
Arrays are a special kind of variable that can store multiple values of the same data type. This allows you to store a lot of data in one variable so that you can later go through the variable and use what you need.
An array can be one dimension, two dimensions or multidimensional.
Sub Array_creation_method1()
Dim name() As Variant
Name = Array ("sachin", "dhawan", "rohit", "rahul")
Range ("A1").Value = name (1)
MsgBox name (2) & vbNewLine & name (3)
End Sub
Sub array_creation_method2()
Dim emp_name(3) As Variant
Dim city_name(3) As Variant
emp_name(0) = "Sachin"
emp_name(1) = "Dhawan "
emp_name(2) = "Rohit"
city_name(0) = "Mumbai"
city_name(1) = "Pune"
city_name(2) = "Bangalore"
Range("B1").Value = emp_name(1) & "live in " & city_name(0)
MsgBox emp_name(1) & "live in " & city_name(0)
End Sub
Sub array_creation_method3()
Dim Student(1 To 3) As Variant ‘ here we can choose own no like (5 to 10),(10 to 20)
Student(1) = “Yuvraj”
Student(2) = “Dhoni”
Student(3) = “Virat”
Range(“D1”).Value = Student(1)
MsgBox Student (1) & vbNewLine & Student (3)
End Sub