Arrays in VBA

Posted: April 28, 2013 by Theodoulus in VBA
Tags: ,

What is an Array :

Usually we work with variables that store a single value or element.  But sometimes we need a variable to hold more than one value (of same data type) e.g. all student names from a class. Here, we can declare an array of variables instead of using a separate variable for each student name.

Definition :
An Array is the collection of similar type of data.

Working with Arrays:

Let’s start working with Arrays in VBA. An array needs to be declared before it can be used, like in the statement below:
        Dim arrStrNames() as String

Here, we have declared the Array but we can’t assign values to it since we have not mentioned the size (upper and lower indexes) of this Array. To fix this, we will write another line:

      ReDim arrStrNames(5)

Now this Array is ready to contain 6 elements. Shocked? Yes, this Array will contain 6 elements despite the upper bound being 5 as the number you have mentioned in parenthesis is upper index (not the number of elements). And the default lower index is 0 for Arrays in all VBA programs. So, as we have given the value 5 for upper index but not for lower index, this Array will start from 0 to 5 means 6 elements.

You can also set the lower index in your code by using the following line of code as the first line: Option Base 1

Now, if you write
Redim arrStrNames(5) Now “arrStrNames” will contain 5 elements e.g. 1 t o5.

The second way of declaring an Array is:
    Dim arrStrNames(5) as String

Dim arrStrNames(1 to 5) as String

By the above line of code, you wouldn’t need to Redim the Array as you already have given the lower and upper indexes.

Apart from the indexes, there is one more difference while declaring an Array. If you declare an Array like “Dim arrStr() as String”. This would be a dynamic Array. You can give the indexes anywhere in the code and change too.
But if you declare an Array as “Dim arrStrNames(1 to 5) as String”, then it would be a static Array. You can’t change the lower and upper indexes in your whole program.

Now, you are ready to play with the array. Let’s write the following lines of code into the VBA code window:

Option Explicit 
Sub Test()

    Dim lngCounter           As Long
    Dim strArrNames()        As String
    Dim lngArrMarks()        As Long

    ReDim lngArrMarks(10)
    ReDim strArrNames(10)
    For lngCounter = 0 To 9
        strArrNames(lngCounter) = Application.InputBox("Please Give the name", "Name", "Useful Gyaan")
        lngArrMarks(lngCounter) = Application.InputBox("Please Give the marks", "Marks", 100)
    Next lngCounter
    Range("A1").Resize(10, 1).Value = Application.Transpose(strArrNames)
    Range("A1").Offset(, 1).Resize(10, 1).Value = Application.Transpose(lngArrMarks)

End Sub

Have fun. And see you soon…………..


Share your thoughts/feedback

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s