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,comparisons very 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 Dim statement 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 String tells VBA that we want to create an array called myarray that 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 myarray would be referred to as myarray(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, index represents the position (0,1,2 etc) of the array, starting from 0, and value is 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. 🤣🤣
-
-