What I want to talk about today is the basic knowledge of VBA arrays. After you learn it, you will know that the combination of arrays and dictionaries is really multiplier! Therefore, it is also essential to learn the basic knowledge points of arrays. Only with the basic knowledge can there be subsequent applications.
Basic Concepts of Arrays
An array is actually a group of elements with the same elements . We can use the array name and its position in the array to specify some specific value. Can have many values, they are distinguished by index numbers inside the array. In fact, the array is the existence of a variable, please understand this concept well.
declare array
Now that we have a preliminary understanding of arrays, do we know how to declare arrays? It's actually very simple, as shown below:
The above is the basic method of defining arrays, of course, this is the method of defining one-dimensional arrays. But how do we declare multidimensional arrays? As shown below:
The image above is how we declare a 3-dimensional array.
If we refer to a value in a three-dimensional array, how should we refer to it? We can refer to the value of arr(1,1,1) like this.
dynamic array
As the name implies, a dynamic array is that the size of the array can be changed. What we declared above are all fixed-size arrays, but we have a lot of times when we don't know how big the arrays we need to use are. At this time, we will use dynamic arrays. Here's how we declare a dynamic array.
One thing to note about dynamic arrays is that when we need to use this array in code, we need to use the Redim statement to re-specify the size of the array during the program. In fact, it is to redefine the size of the array and give it a value.
Of course, to define a dynamic array, there is no limit to the number of times we can use it, that is, we can define a dynamic array multiple times, but at this time, if we redefine it, the value originally stored in it will be lost. If we don't want the original number to be lost, we need to use Preserve for array preservation. As shown below:
array creation
Said the declaration of the array, and then said how the array is created. Let's take a look at the following methods of array creation.
Code writing: We can see that the numbers have been stored in the array.
Use Array to create arrays, use constant arrays directly, and perform assignment operations.
Direct assignment method, as shown below: The value of the direct cell area is assigned to the array
Sometimes I personally prefer the array approach obtained by direct assignment. As for which one you prefer personally, it depends on you, their efficiency is actually similar.
Array superscript and subscript
We all know the size of the array, so surely everyone wants to know the size of the array, which is the size of the starting point and the ending point. Please don't be yellow, we have a way to get the size of this, it is our UCount and LBound functions. Let's see how it is done!
To know the upper bound of the array arr, UBound(arr)
To know the lower bound of the array arr, LBound(arr)
Did you find that it is actually very easy to get the size of the array?
Then if we want to know the number of elements in a dynamic array? How should I get it?
Just subtract the subscript from our superscript and add 1, which is the number of elements in our array. That is, UBound(arr) - LBound(arr)+1.
The last two points are my personal experience in the learning process:
(1) When writing a one-dimensional array into a cell area, the cell must be horizontal, that is, a cell area with one row and multiple columns. If you want to write a vertical area, you must use the Transpose function of the worksheet to convert it;
(2) Like the multi-cell array formula of the worksheet, when the array is written into a cell, the area of the cell must be the same as the size of the array.
The above is the array in the code we talked to you today. In fact, VBA code is very simple to learn. It is a scripting language. It has its own rules and regulations. You can write the code according to the rules of the game. If you don't understand or don't understand, you can leave a message below and we will answer them one by one.
I am an Excel lesson plan, follow me and continue to share more Excel skills!