VBA for Excel 2007 tutorial--VBA Array


VBA Array

Array is a collection of values of the same type. A variable can store only one value but the array can store multiple values. The array can have multiple dimensions. Most programmers use one-dimensional array and two-dimensional arrays.

-One-dimensional array:

One-dimensional array store items in one direction (linear). To use an array, you must declare it.
You can declare a one-dimensional array as shown below:
Dim array_name(size) As Data type

Example: To declare an array called x to store 5 values of integers (elements), you can write:
Dim x(5) As Integer

To assess the array elements you, you can use a loop to do that.
Example:
Dim i,sum As Integer
sum=0
For i=0 to 5 'assign values to the array
x(i)=i*i
Next

For each v in x 'Get values from the array and sum them
sum=sum+x
Next
Msgbox sum
Note: the start index of the array is 0 and the last index is equal to its size
subtracted by 1

-Two-dimensional array:

Two-dimensional array stores values in a table of rows and columns.

Index 0 1 2
0 1 2 3
1 4 5 6

Example: To declare a two-dimension array (2 rows and 3 columns) called arr, you can write:

Dim arr(2,3) As Integer
Dim i,j As Integer
Dim val As String
'Assign values to the array without using a loop
arr(0,0)=1
arr(0,1)=2
arr(0,2)=3
arr(1,0)=4
arr(1,1)=5
arr(1,2)=6
'Accessing the value of the array using for next loops
for i=0 to 1
  for j=0 to 2
     val=val & arr(i,j) & vbcrlf
  Next
Next



Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.