What is an Array :
Mostly we work with a single variable or element. But sometimes we face a situation where we need a variable to hold more than one value of same data type e.g. all the names from a class. Here, we can declare an array of variables instead of using a variable for each item.
An Array is the collection of similar type of data.
Working with Arrays:
Let’s start working with Arrays in VBA. Before using an Array, You need to declare it in the code like:
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:
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 Base 1
Dim lngCounter As Long
Dim arrStrStudentNames() As String
Dim arrLngStudentMarks() As Long
For lngCounter = 1 To 10
arrStrStudentNames(lngCounter) = Application.InputBox("Please Give the name", "Name", "Useful Gyaan")
arrLngStudentMarks(lngCounter) = Application.InputBox("Please Give the marks", "Marks", 100)
Range("A1").Resize(10, 1).Value = arrStrStudentNames
Range("A1").Offset(, 1).Resize(10, 1).Value = arrLngStudentMarks
And have fun.See you soon…………..