Hey there, I am back again for you, my lovely VBA enthusiast! This morning I have already consumed 2 mug of coffee because of this article that I am writing. Hahaha. Actually, Arrays are my most favorite topic in VBA. It is used in my almost all client projects and help me a lot in my business. Using Arrays, we can do the most unimaginable calculations,comparisonsvery easily. I will give you the memorable experience here. I am committed to give you the best knowledge.
In this article we are taking a deep dive into Arrays- The fascinating world of Arrays. Well, think of Arrays as the cousins of variables – they share similarities but come with their own set of superpowers and nothing can beat Arrays when it comes to doing the most complex things in excel. Trust me! 😊😊 And the beauty is Arrays are in every programming language like Python too. Let us start the magic show now!
What are we Covering:
1.) What are Arrays.
2.) How to Declare Arrays.
3.) How to Manually Fill an Array.
4.) Option Base 1 & the Error- Subscript Out of Range
5.) Fill an Array Using Loops
6.) What is a Redim (The most Crucial thing in Arrays -Must know)
7.) Array-in built VBA Function
8.) What is a PRESERVE Keyword ?
1.) What are Arrays?
So, what exactly are arrays in VBA? In simple terms, arrays are like variables that allow you to group the data together. Imagine having a bunch of information scattered around your worksheet – arrays help to store all that information or data into them by organizing it neatly, making data manipulation so easy. One second, Did i tell you that Loops are going to make your arrays super duper strong.Yes most of the time, when you are using arrays, you will find the loops are there. You will see in this article. Wait a bit for this! The astonishing combination of Loops and Arrays. 😍😍
Honest thing about Arrays is that till the time you don’t see practical examples,you cannot understand it. So,Let me share images as well while explaining. For now, you got to understand they are like variables that can store small, medium and large information. That’s it.
2.) Declaring Arrays:
As always, use a “dim” key word and then give a name to your array with open and close parenthesis along with the data type like integer, double or string. You already know what are data types concept. Correct! I have written an article on VBA Variables. Pls check if you have not yet.
How to Tell VBA About Your Array.
Dim: This keyword is always used while declaring arrays just like in variables.
ArrayName: ArrayName is like a variable name. For e.g. Dim sport_team(2) as string. Here, sport_team is a my array and 2 is the size of it. It means it can store 3 text values because the array by default starts from 0. So,it means team(0), team(1) and team(2) are the indexes we have created here. Yes, first index which will be given to your array by VBA will start from 0.Kindly note this important point.
Size: As i said,size decides how many indexes we need in an array. Quite obvious,It is always a numeric in nature.So, writing 5 in array size means an array can hold 6 values. (indexes are 0,1,2,3,4,5)
DataType: You say what kind of value will be in the array (like numbers or text).Same concept as in declaring normal variables.
Let’s take a closer look at the code snippet provided:
Code Explanation:
In VBA, the Dimstatement is used to declare variables. In this case, we’re using it to declare an array named myarray. Pls note,You can write (0 to 3) or just (3) . Both syntaxes are ok.
The line Dim myarray(3) As Stringtells VBA that we want to create an array called myarraythat can hold 4 text values since the array starts from 0 by default.
You see 3 in parentheses after myarray. This number represents the number of elements (or slots) in the array. Arrays always use parentheses () and within that a number comes which decides the size so it is always numeric.
Each element in the array hold a value. Since arrays start counting from 0, the four elements in myarraywould be referred to asmyarray(0), myarray(1), myarray(2), myarray(3). See the below image. We can see that in local or watch window just like we see our variables in these windows. It is a same process.
Tip: We can check our array how it looks as we run the code in the Locals window or watch window (We have covered these windows in variable articles. However, just to revise, you can bring up the Local window by simply clicking on View->Locals Window as shown in the image below.
Now Let’s look at our array in Locals Window in below image
So, if you remember the line dim myarray(3) , this is how vba automatically creates the indexes which are empty right now because we have not filled them with values.You can give any name to your arrays (provided it should follow the variable naming rules). It is same thing as you do in declaring a variable. Here, I have just used a name “myarray” so when you look at the images or read the lines – you will be able to connect with the code.
3.) How to Manually Fill an Array with Values
Now, Let’s dive into how we can fill an array with values.
Code Explanation:
We start by declaring an array named myarray(3)with a size of 4 , capable of storing the 4 values which has a string data type.
Next, we assign the values to or array using the syntax myarray(index) = value. Here, indexrepresents the position (0,1,2 etc) of the array, starting from 0, and valueis the data we want to store in that position/index.
In this example, we’re storing the names of four different fruits – “apple”, “orange”, “papaya”, and “banana” – in the myarray.
Each line of code assigns a specific fruit name to a particular element of the myarray. For example, myarray(0) = “apple” assigns the value “apple” to the first index of myarray.
Similarly,myarray(1) = “orange”, myarray(2) = “papaya”, and myarray(3) = “banana” assign the values “orange”, “papaya”, and “banana” to the second, third, and fourth elements of myarray respectively.
4.) What is an Option Base 1 and the Error – Subscript Out of Range
See, We have written the option base 1 manually on the top of the module , before our first sub routine. Remember, It is declared only once in the module and always on the top of your first subroutine -whatever it is. So, then whatever arrays we will create in this module, Option base1 will have its impact on those arrays.
Let’s begin by examining the provided code snippet:
Here’s a detailed breakdown of the code:
Option Base 1:
The line Option Base 1 specifies that array indexing starts from 1 instead of the default value of 0. This means that the first element of an array will have first index as 1. So, point is simple, if you are comfortable with array using first index as 1 , write option base1 and if you think, you are fine with by default index as 0 ,then you do not need to write it.
Any advantage of Option Base1: In my opinion, it has no such benefit other than if you are like me who prefers first index to be a 1 because 0 makes me uncomfortable.😁 For e.g. myarray(3) for me should hold 3 values like 1,2,3 and not 0,1,2,3. So, I leave it to you if you want to use it or not. Frankly, we should not waste time on this simple thing. But remember,in your VBA Interview this question often comes : What is an option base 1 or How can you change be default index of an array from 0 to 1.
Code Explanation:
a.) The lines myarray(1) = “apple”, myarray(2) = “orange”, and myarray(3) = “papaya” fill the first three elements of myarray with the respective fruit names.
b.) However, attempting to assign a value to myarray(4) with myarray(4) = “ink” leads to a “Subscript out of range” error. Why this error?This occurs because the array was declared with a size of 3 and the Option Base1 makes indexing to start from 1, meaning the valid indexes are 1,2 and 3, not 4. This error often disturbs us because of the mismatch in assigning the values and giving a size to an array. So, I have shared this with you. You can thank me later. 😁😁
By the way, Do you remember this error we had discussed before also in VBA introduction article. We had seen this error. Remember? Let me help you. If you have written a code to select a “Asia” sheet in VBA and the name of this sheet is somehow changed or deleted by someone, VBA will throw the same error “Subscript Out of range”. So, now we know, if sheet does not exist in excel which VBA is trying to select OR If an array index does not exist but we’re trying to insert the value into that index which never existed, VBA will throw this error. As i said, this error too sometimes comes in the form of a question in your interview what when does Subscript Out of a range error comes?
5.) Fill an Array Using Loops:
It is not possible to fill array manually in practical world if you have large data. in fact even for a data which has 20 rows, who would fill the data into arrays manually( The one i shared with you in the above image). So, we are not going to say bye bye to manual process and see how we can fill the data or transfer the data into arrays in few seconds. Meet my dear friend The Loops😍 . You have met them before in Loops article. Haven’t you, 😁😁😁
Let’s have a look at the code snippet:
Here’s a detailed explanation of what’s happening in this code:
We start by declaring an array named myarray capable of holding Five text values as we are using option base 1.
Additionally, we declare a variable index of type Long to serve as the loop counter.
We then enter a For loop that goes/iterate from 1 to 5 (For index = 1 To 5). During each iteration, the loop counter index takes on values from 1 to 5.
Within the loop, if you see we have used index variable for array as well as for range command. Because, here it makes sense to me to use index variables in three different forms. One to run the loop, second to use it in array index and third is to capture the value from the cell address using concatenation (“A” & index), allowing us to access consecutive cells in column A. Look, this should not scare you if you have finished the loops article. I have taught you all this over there. So, this is very simple code. Just an intelligent use of “index” variable here.
The line myarray(index) = Range(“A” & index) assigns the value from the current cell (e.g., A1, A2, A3, etc.) to the corresponding element of myarray(index)
When loop begins, we know our variable “index” is 1 so the next line becomes myarray(1) = Range(“A” & 1) and then in the second iteration it is myarray(2)= Range(“A” & 2) and so on. So, now you understand why I had said Loops change the Arrays totally. Because , now imagine you have 20000 rows of data and you want to pass in that all data into your array. This loop can do it so easily. So, in the practical world, 99% times we fill arrays by using loops and not manually. But, sometimes we create small arrays manually too. Bottom line is, we should know all the possibilities of creating an array because it does not cost anything. 🤣🤣
So,we can say that filling arrays using loops provides a powerful mechanism for handling the data in VBA, streamlining data processing tasks and improving code efficiency.
6.) What is a ReDim (The most crucial thing in Arrays)
I will try to make it very simple for you because for beginners it is very confusing. So, I will not look at the length of this topic but the quality. And it means lot of explanation .
First, you know we always use Dim word to define the arrays. Correct! But, if i tell you that Redim can also be used in arrays. Then , you will say why do we want to use it? What is the need of it? One thing please note, Redim is used only in arrays and not in normal variables or anywhere in VBA. Just in arrays.
Why we use it?
Let us say there are 3 cells in excel which i want to use in my array. A1 Cell has a value 100, A2 has 200 and A3 has 300.
Let me write here a line. Dim myarray(3) as integer. Now, you know there are 3 values this array can hold. (Option base1 is written on the top). So, we can run the loop and we can these values into an array.
What if tomorrow our data rows are increased and now there is a value in A4 Cell which is 400 and we want to get this value in the same array.
If you run the code again , you know loop will not run 4 times so you may be thinking right now that let us change the value from 3 to 4. Dim myarray(4) as integer. Are you thinking the same? 😊😊
See, the problem is every time data increases or decreases, we cannot go in the code and keep changing this array value. VBA should pick it automatically. Yes, yes, VBA should pick. I know this is what you are saying too. Are you smiling now or thinking like a economist, Deep level thinking. Well, if you are smiling or excited or really have gone into a deep thinking zone, I say , I am extremely happy that i am able to generate this much curiosity in your mind. Problem is an array takes the values from its index size. You know the error “Subscript out of range”.
So, now the question is How to resize an array ? This is the question now and trust me it is very easy , if it is taught to you in a right manner. In a systematic way.
We will first find out the total number of rows we have in our table and we will assign that value to a variable.
Dim Lrow As Integer Lrow = Range(“a10000”).End(xlDown).Row
You know this will give us the last row and this is dynamic. Now, you know what this means we have actually found the array size. Isn’t it. Because the total no of rows is what we need to know and that value we want to assign it to our array so that our loop should reach to each cell one by one. And our array can always have every value till last row. Fantastic! So. let me show you the code.
Do you see how smartly we have commented the dim MYARRAY(3) and instead of 3 (Constant) we are using a variable.
Dim MYARRAY(Lrow) as integer. This means now when we run the code LROW value will go into an array and then size will be given to an array making it resizable every time the code runs. Now, whether you have 100 rows or 40 rows or 1000 rows, Lrow variable will first get the last row value and array will take that . Wow! that is superb. But then where is a Redim. We have not used it anywhere. Now, here comes the shock. This code will not run. It will generate an error on the l ine Dim MYARRAY(Lrow) as integer. And error is “Constant expression required”. 😁😁😁 Why i am showing you my teeth. Actually, I am having a naughty smile because i love errors. I always say, till the time you will not understand errors descriptions as to what is their root cause and what it means ,you cannot be a master and be it any tool like excel. powerquery, mcode, python etc.
Why this error came and how to solve this?
You need to remember that when you are using a constant inside an array – you are using a dim keyword. But, if you are using a variable because you want to create a resizable array you need to use “Redim”.
Dim arr(2) as string ‘ This is a static array – it has a fixed size due to a constant. It cannot accept 3 values . Note, imagine, option base 1 is used here so array has 2 indexes only.
Redim myarr(Lastrow) as string ‘ This is a dynamic array. Lastrow is a variable and it is already calculated from the above line so now because of redim word, no error will come like “constant expression”. So, correct code is pasted below. And, see the For Loop line. In order to fill all the array values from excel cells(Now there are 2 extra values in A4/A5) loop is also running till last row. That’s a very basic thing but still I have told you. So, now if you see the snapshot, you find all values are into our array. And this is the use of redim.
For resizeable Arrays – Use Redim and for fixed arrays- just use dim word. Because we cannot use a variable in array by using dim. Only redim is allowed as it means re-dimension. I hope, this the most crucial concept is clear to you now. You may re-read this and also practice on your excel . Trust me, it will help.
Now, one more question is, then Why cannot we use redim every time in Arrays? See the below line.
Redim myarray(2) as long ‘ its a fixed array so here Redim is allowed ? if you see the result in the below image, yes it is going to work because VBA does not mind using redim even if it is a constant. I will give you more insight on this in the coming section. So , for now, I hope all is clear.
7.) Another method of creating an array using its Function.
If i need to create a quick array without using excel cells and no loop because it is a very simple array and also it is a constant array then i can use Array function too.
This is a very different approach. You should know it. Sometimes we need to create arrays this way too.
Note:In a first line, we’ve mentioned , dim weekday as variantand there are no parentheses here and the data type is a variant. You will always use variant data type if you want to create an array like this and here no parenthesis. This is a different approach.
In a second line, if you see we are using weekday = Array(“Sun”,”Mon”…………………) . Here, we’re using Array which is an in-built VBA functionand inside that i have written the weekday names. And you can see the local window also – it appears in the same way as it does in other approaches.
a.) So, We can Create Arrays with the help of Loops OR
b.) We can write array values also in our modules by writing their indexes ,assigning the values to it one by one OR
c) We can write arrays using Array Function as well with Data Type Variant.
8.) What is a Preserve keyword ?
i) See the below image. We have given a size of 3 to our array and have manually inserted the 3 values. It does not matter how we are filling our array. If loop need to be used , we can. But, to make the code understandable, I have deliberately written the values manually so you can see and easily understand it. Now, see the local window. Do you see the array with values. Correct! I am sure, till now there is no understanding issue. It is very simple. We have created an array which has been given three values 55, 12 , 2
ii) Now, Going to run the code again till last line. Pls See the below image.
See the line Redim MYARRAY(5) . Here we have increased the array size from 3 to 5 because we want to insert more values in our array so we can easily use redim and give a new size. Sometimes, in our projects, we do this . We do not know the real size of an array initially and as code keep moving forward, we keep knowing the size of an array and then we use this strategy. So, we have assigned the myarray(4) and myarray(5) some values. First three indexes were already given the values from the above lines. Correct!
Pay attention!See the local window in the image. Are you surprised to see that what had happened to first three values . They are zero. MYARRAY(0) = 0 , MYARRAY(1) = 0 & MYARRAY(2) = 0.
What happened here? Why previous indexes are zero and index 4 and 5 are not ?
When you resize an array using the ReDim statement without the Preserve keyword, the existing values in the array are lost, and the array is reallocated in memory with the new size. This means that if you resize an array without Preserve, you are going to erase its previous content. wow! That was such a great knowledge. Isn’t it?
So, if i need to keep my old values too in an array , I have to use Preserve key word. That is the use of Preserve and trust me, I have used it many times in my projects. It is a great boon. See the new below image. Now, we are using the preserve keyword and nothing else is changed in the code.
When you use ReDim Preserve, you’re instructing VBA to resize the array while preserving the already existing values stored in it. This is extremely useful when you need to add or remove elements from an array without losing the data already stored in it. I strongly urge you that please read this delicate and complex topic again if you think you still need clarity because I did many revisions in my learning days on Preserve and then I was able to understand it. But, the truth is that time in 2005 i did not find any in-depth articles on internet but you are lucky. hahaha. Sometimes I love to praise myself🤣🤣🤣🤣
So, the thing is Redim gives the size to an array. Correct? If that ARRAY is not undergoing a size change, then obviously we will not use Redim statement second time. But, if i have a situation where i have to resize my array size at different time /at different sections of the vba code, while using Redim second time or third time and so on, it is going to loose the previous values , if we do not use the PRESERVE. So, Preserve keeps saving the values while array size is increased.
Redim is what? It is re dimension. Correct! Means Array has a size and we use redim to define the size. Now, whenever Redim comes, array on resizing , creates new indexes and while doing so, it destroys the previous values from those indexes. That’s the concept.
The most important question or doubt. Can we use PRESERVE for the very first time in Redim line. Something like this?
Well. why do you want to use preserve at the very first time while declaring an array. PRESERVE means to save the values . Correct? But, first time when you assign the values, you are actually inserting the values into an array so there is no question of saving it. Because, first time array is given a size and based on that , array is taking the values which are already saved .When the values are coming into an array, they are not going to be destroyed Itself. Correct? It is only when this array in the code , somewhere down the lines, will have a new Redim , means a new size is being given, then you have to save those values, That is where PRESERVE comes. So, bottom line is for the first time if i am declaring my array, it does not need any PRESERVE because that time values are being allocated to array and they are always there. They are saved. Values are destroyed only when we change the size of a an array.
I hope I was able to justify this article on Arrays and this has enlighten you about ARRAYS. In VBA, If you do not know Arrays, You can not be called a VBA Developer because it is used in acheiving big results. In my 80 percent Client projects, I use ARRAYS. I feel the need of them due to difficult calucations and complex things that i need to deal with. Arrays make my work very easy. It is going to take some time to get mastery on ARRAYS but keep practicing and use real data for practice. Hope, you loved the efforts. Let us meet in the next article on File and Folders handling. Pls do drop your review on this article and if i have missed something, Please let me know.
