Excel VBA is a Programming language, and though it is a language, it should support Array Programming. Yes, it does. In Excel we can use Array Formula. We can also use the power of Array in Excel VBA.
What is an Array?
In a simple definition, the variable, which contains two or more values under the same variable, is called an Array. In programming language sometime you need to use lookup function in a specific list. To do this use an Array. An Array is a way to store more than one value under the same variable name. An array looks like this when it is declared:
In the above code, Dim is short for the word Dimension and it allows you to declare variable names and their type. Then we used a custom name yourName to store values. But how many values can store in yourName? We declared (5) that means 0 to 5 = 6 values. If we declared (1 to 5) then it will not count the 0 (zero), so that you can store 5 values under the same yourName variable. So you can also set an Array variable with 6 stored cell like below:
How to set values in Array?
Now to set your Array Values under the same Array Variable, follow the below code:
In the above code, you entered 4, 2, 7, 3, 8, 7 digits as values for each variable cell (1 to 6) under the same variable name yourName.
Run the Array values:
To test your Array values working properly or not, use the below codes in your code window:
If you run the above program, then it will show a message box. The message box shows the value which is stored in yourName(4). That means 3:
This is the basic of Array in Excel VBA. In next I will try to explain more details with the help of real life project.
|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw
What is an Array?
In a simple definition, the variable, which contains two or more values under the same variable, is called an Array. In programming language sometime you need to use lookup function in a specific list. To do this use an Array. An Array is a way to store more than one value under the same variable name. An array looks like this when it is declared:
Dim yourName(5) as Integer
In the above code, Dim is short for the word Dimension and it allows you to declare variable names and their type. Then we used a custom name yourName to store values. But how many values can store in yourName? We declared (5) that means 0 to 5 = 6 values. If we declared (1 to 5) then it will not count the 0 (zero), so that you can store 5 values under the same yourName variable. So you can also set an Array variable with 6 stored cell like below:
Dim yourName(1 to 6) as Integer
How to set values in Array?
Now to set your Array Values under the same Array Variable, follow the below code:
Dim yourName(1 to 6) as Integer yourName(1) = 4 yourName(2) = 2 YourName(3) = 7 YourName(4) = 3 YourName(5) = 8 YourName(6) = 7
In the above code, you entered 4, 2, 7, 3, 8, 7 digits as values for each variable cell (1 to 6) under the same variable name yourName.
Run the Array values:
To test your Array values working properly or not, use the below codes in your code window:
Sub ArrTest() Dim yourName(1 to 6) as Integer yourName(1) = 4 yourName(2) = 2 YourName(3) = 7 YourName(4) = 3 YourName(5) = 8 YourName(6) = 7 MsgBox yourName(4) End Sub
If you run the above program, then it will show a message box. The message box shows the value which is stored in yourName(4). That means 3:
Image 1: Result
This is the basic of Array in Excel VBA. In next I will try to explain more details with the help of real life project.
|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw