We can describe an array as a data structure which contains numbered list of items. Important fact is that we can refer these items by same name. Due to this reason arrays play major role in any programming language. In VBA, items are normally numbered from zero. But if we need, we can change it to start from different number.
So this post will give you good understanding about fixed size arrays. I will use below sample data in the explanations.
In VBA we can declare fixed arrays in several ways. For an example let's create fixed array called FirstTenNames.
|
Dim FirstTenNames(9) As String |
So this array can contain 10 items. Here index start from 0. However if you need to start index from 1, you can declare array as follows
|
Dim FirstTenNames(1 to 10) As String |
So now let's populate this array with first 10 names in column A. You can do it as follows.
|
Dim FirstTenNames(9) As String Dim WS As Worksheet Set WS = Worksheets("sheet1") FirstTenNames(0) = WS.Range("A2").Value FirstTenNames(1) = WS.Range("A3").Value FirstTenNames(2) = WS.Range("A4").Value FirstTenNames(3) = WS.Range("A5").Value FirstTenNames(4) = WS.Range("A6").Value FirstTenNames(5) = WS.Range("A7").Value FirstTenNames(6) = WS.Range("A8").Value FirstTenNames(7) = WS.Range("A9").Value FirstTenNames(8) = WS.Range("A10").Value FirstTenNames(9) = WS.Range("A11").Value |
Now we know how to assign values to an array. What if we need to get values back from an array.
For example think you need to put this 10 names to new sheet (Sheet2) of this workbook. Then you should add following part to end of above code.
|
Dim WS_New As Worksheet Set WS_New = Worksheets("sheet2") WS_New.Range("A2").Value = FirstTenNames(0) WS_New.Range("A3").Value = FirstTenNames(1) WS_New.Range("A4").Value = FirstTenNames(2) WS_New.Range("A5").Value = FirstTenNames(3) WS_New.Range("A6").Value = FirstTenNames(4) WS_New.Range("A7").Value = FirstTenNames(5) WS_New.Range("A8").Value = FirstTenNames(6) WS_New.Range("A9").Value = FirstTenNames(7) WS_New.Range("A10").Value = FirstTenNames(8) WS_New.Range("A11").Value = FirstTenNames(9) |
You will get below result after running the code.
In above example we wrote one line for each item in the array. However it is not practicable in real world as some arrays can contain millions of data. So there should be a better way to populate an array and get data back from it. One solution is to use for next loops. So let's use for next loop to populate FirstTenNames array.
|
Dim WS As Worksheet Set WS = Worksheets("Sheet1") Dim FirstTenNames(1 To 10) As String For i = 1 To 10 FirstTenNames(i) = WS.Range("A1").Offset(i, 0).Value Next i |
So you can see that we were able to shorten our code by great extent. Also we can use for next loop to get back the data from the array as well. Add following part to end of above code.
|
Dim WS_New As Worksheet Set WS_New = Worksheets("Sheet2") For i = 1 To 10 WS_New.Range("A1").Offset(i, 0).Value = FirstTenNames(i) Next i |
After running the code you will get first ten names in column A of sheet 2 of the same workbook.
Also it is a good practice to erase the array after using it. You can erase an array using "Erase" keyword. For an example you can erase above array as follows
|
Erase FirstTenNames |
In our above example we used constant values in for next loops. So if we need to change the number of items in array we need to change it in every for next loop in the code. To avoid this we can use
For i = LBound(FirstTenNames) To UBound(FirstTenNames) instead of For i = 1 To 10
So we can modify our code as follows.
|
Dim WS As Worksheet Set WS = Worksheets("Sheet1") Dim FirstTenNames(1 To 10) As String For i = LBound(FirstTenNames) To UBound(FirstTenNames) FirstTenNames(i) = WS.Range("A1").Offset(i, 0).Value Next i Dim WS_New As Worksheet Set WS_New = Worksheets("Sheet2") For i = LBound(FirstTenNames) To UBound(FirstTenNames) WS_New.Range("A1").Offset(i, 0).Value = FirstTenNames(i) Next i Erase FirstTenNames |
Now we don't need to change the for next loop manually if the number of items in the array changed.
