ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming»
  • Programming Languages

VBA: How to Define, Dimension, & Loop through an Array

Updated on September 11, 2012

An array is a variable that is capable of holding multiple data elements.

An array can be single dimensional or multidimensional.

This module will cover single dimensional arrays.

The storage capability of a single dimensional array can best be compared to that of a single row or column of data in Excel.

The Struture of an Array

Each item in an array has an index number associated with it. In a single dimensional array this the index number is a single integer specifying a data elements co ordinance in the array.

When thinking of a single dimensional array as single column or data, you can think of the index number as the row number. The only difference is by default the Index numbers begins at zero a proceeds upward to the end of the array.

Specifying the size of an array

Before you can use a variable as an array you must specify its size. There are two methods for doing this.

You can specify a variables size when defining it or you can do it later subsequently using redim.

When Defining Variable

Dim MyStr(5)as string

After Defining Variable

Redim MyStr(5)

Redim allows you to specify or change the size of your array after is has been declared. The example above would resize the a string variable "MyStr" to have 6 (0 to 5) slots for holding data.

Loading an Array

MyStr(0)="One"
MyStr(1)="Two"
MyStr(2)="Three"
MyStr(3)="Four"
MyStr(4)="Five"
MyStr(5)="Six"

Looping through an Array

Ubound is used to return the highest in number in you array. In the example case 5.

Lbound is used to return the lowest index number in your array. In the example case 0.

Looping through an array is easy using Ubound and Lbound

Below is an example for a loop that would go from x=0 (LBound) to x=5 (UBound). Returning the values of the array in cells A1:A6.

For x=LBound(MyStr) to Ubound(MyStr)
Cells(x+1,1)=MyStr(x)
Next

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.