VBA Array – Everything you need to know.
A variable is the place holder in the memory (RAM). When program execute, all these variables take space in memory and be used to store and read values. VBA Array is the collection of these variables.
Dim strName as string
Dim dblSalary as string
We have two variables here, strName can be used to store name and dblSalary can be used to store salary.
Now, let’s say we have 1000 employees to store their information, we would not want to type 2000 lines of variables. In that case we will use Array.
How to declare VBA Array –
In above declaration, varEmpName has 6 elements, though we are giving 5 in the parenthesis. But by default, base of array is 0. So, from 0 to 5, there are 6 elements Though, we can change the base of array by using VBA options. See below.
Option Base 1
Option base 1 will start array from 1, if we have this line at the top of the module. Above declaration of array will create only 5 elements in the array. It will be 1 to 5. Not 0 to 5.
By default, it is Option base 0, so we don’t need to write it. We cannot define base other than 0 or 1. For example we can not say Option Base 5.
So, with Option Base 1 setting, our array will look like this.
We see the Lbound(strEmpName) is 1 and Ubound(strEmpName) is 5. Lbound() and Ubound() are very important to understand when we work with arrays. We will see better use of these function further in this article.
Array Dimension –
Array must have dimension(s). dimension define how many elements an array will have. For example, above array is one dimensional (1D) array. Image this array as a collection of five cells in excel sheet. Let’s say A1:E1, this range is going horizontally in one direction. So that is one D.
Now, lets expand this range from A1:E1 to A1:E2. This expanded range have 10 elements because now it has 2 rows and five columns. See how to replicate these many elements in array.
Dim varData( 1 to 2, 1 to 5)
First, 1 to 2 is telling that varData array should two rows, second, 1 to 5 is telling that it should have five columns. Now, our array looks like this
Array’s Dimensions can be expanded further to 3D, 4D, 5D and so on, and these will get more complicated. imaging like one sheet in excel is a two-dimensional array, because it has rows and columns. One workbook is a 3D array. As it has multiple 2D arrays. A folder where we have saved many excel files can be considered as a 4D array, because it contains multiple 3D arrays (workbook) and so on…
Most of the time we use 1D and 2D array, rarely we use 3D array. But I personally never get chance to use 4D array.
Redefining VBA array –
Let’s see how we can re-define arrays. DIM keyword means Declare-in-Memory. We have another keyword which ReDIM, we can guess what that mean. That mean declare again.
When we use REDIM, it will wipe out existing configuration of array along with the data stored in it. See below.
Redim varData(1 to 10,1 to 2)
Above line will re-structured array with 10 rows and 2 columns from five rows and 2 columns. Remember, it will erase all data from the array and give we a brand-new blank array with new dimensions.
Preserving data while re-defining
These is way we can preserve data in array when we use redim an array. But that is limited. We use Preserve keyword with Redim to preserve data. It works with only the last dimension of array.
That means, we can only preserve data if we are re-defining the last dimension of array. For example, if we have 1D array, we can keep increasing elements in array and preserve older data. See the program below.
Dim lngR as long
For lngR= 1 to 10
redim preserve varValue(Ubound(varValue)+1)
In above program, we declare array with 1 element (considering we don’t have Option Base 1), In for Loop we are increasing array element by one and assigning value to the last elements. Post your comment in the comment box if anything doesn’t make sense.
In 1D array, there is only 1 dimension and that is the last dimension too. But in 2D or more dimension. Last dimension can be varied. For example, in 2D array, Last dimension is column.
We can only preserve value if we increase columns in our array. If we try to increase rows, it won’t work.
Dynamic Array –
This Is the interesting part. This is the whole reason we use array in VBA (most of the time).
We know the Array is much faster in terms of storing and reading data from memory, the only reason of making them faster is that they store only values, not any properties and formatting (except couple). So, less data equal to faster program.
Suppose you have a range of 10000 rows and 200 columns. And if you preform a for each loop. There are possibilities that it will hang your PC. Or alteast it will run for few seconds.
If we run a for loop on array with these many elements, it will take one second.
Let’s see how we can create an array from excel range. It is quite simple. Declare a variant array and assign range to this array.
That’s it. You have created an array from that range. But that is not what I mean by dynamic array. Dynamic means which can auto resize as per the data on range. In Dynamic array program don’t know how many elements array will have.
Like in above example, we know that it will have 2600 elements. Let’s say tomorrow we got more data in 101 line; in the case this program will not pick that new data. We need to use CurrentRegion or UsedRange property of range to include all data into array.
When we use above code, we don’t know how many rows and columns array will have. It means we don’t know where to end the loops. That is where Lbound() and Ubound() play their roles.
We start loop from Lbound() to Ubound() of array.
For lngR= Lbound(varData) to Ubound(varData)
‘—Operative code goes here
For lngC= Lbound(varData) to Ubound(varData,2)
‘—Operative code goes here
Notice that in next loop for column. We use Ubound(varData,2). This 2 in Ubound() tells which dimension upper bound we want to get. By default, it is 1. So, we can omit, like in the first loop.
Filter array in VBA –
Filter() is a function in VBA which can see the contents in each element and will return the array with only matching contents. It works only on 1D array
varData = Range(“A1:E10”)
varData = Filter(varData, “Apple”, True, vbTextCompare)