VBA for MS Excel 2010 tutorial- Array

VBA Array



An Array can be said as  a collection of values of the same type. If you want to store many values of the same type temporarily in memory, it is better to store them in an array then you can access each element by its index that is from 0 to the array size subtracted by 1.The array can have multiple dimensions. Most programmers use one-dimensional array and two-dimensional arrays.

-One-dimensional array

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  (elements) integral numbers, you can write:

Dim x(4) As Integer

To assess the array elements, you can use a loop to do that.

Example:

Dim i,sum, r As Integer

sum=0

For i=0 to 4 ‘assign values to the array

     x(i)=i*i

Next

r=1

For each v in x ‘read the elements of the array in to Excel cells

     cells(r,1)=v

      r=r+1

Next

 

Note: the start index of the array is 0 and its size is equal to last index added by 1.

 

-Two-dimensional array

-dimensional array stores values in rows and columns.

Index 

0

1

2

0

10

20

3

1

41

15

25

 

Example: To declare a two-dimensional 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)=10

arr(0,1)=20

arr(0,2)=3

arr(1,0)=41

arr(1,1)=15

arr(1,2)=25

‘Accessing the elements of the two-dimensional array using for next loops

for i=0 to 1

     for j=0 to 2

              Sheets("Sheet1").Cells(i+1,J+1)=arr(i,j)

     Next

Next

-Dynamic array

The arrays discussed above are fixed-size array. You can also create an array that dynamically expands its size as needed. This type of array is called dynamic array. To create a dynamic array, you don't need to specify the size of the array when it is declared. And later, the keywords ReDim Preserve are used to change the size of the array.


    Example:
       Dim arr() As Integer
       arr(0)=12
       arr(1)=10
       ReDim Preserve arr(UBound(darr)+10) 'Increase the array size by 10



Comments

Name:
Comment:
Enter the text:
CAPTCHA image

...................................................................................................................................Home | Forum | About | Contact
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.
Copyright @ 2011-2013 worldbestlearningcenter. All Rights Reserved.
Home Programming Web DB & App Questions Exercises Tips Programs FAQs Download About
Computer-Wbest
Popular Pages
You might like