If an array has more than one dimension it is called multidimensional array. If you have a list of names which you want to put to an array, you can put them in an array which has single dimension. But think you need to store these names with address and age of each person separately, then the best way is to use an array with two dimensions. Two dimensional arrays are like tables, where rows of tables correspond to first dimension and columns corresponds to second dimension. But if an array has more than two dimensions, following is the best way to illustrate it's structure.
Consider this four dimensional array
|
Dim SampleArray(0 To 1, 0 To 3, 0 To 2, 0 To 5) As Variant |
It's structure can be best explained as follows.
So now let's look at how we can declare and populate multidimensional array. I will use below sample data for the explanations.
So let's put all these data to an array called All_Data()
|
Dim WS As Worksheet Dim All_Data(0 To 17, 0 To 4) As Variant Dim i, j As Long Set WS = Worksheets("Sheet1") For i = 0 To 17 For j = 0 To 4 All_Data(i, j) = WS.Range("A2").Offset(i, j).Value Next j Next i |
It is a best practice to use LBound and UBound instead of constants. Because then we don't need to change whole code if we need to change the size of our array. So here is the modification of above code where constants are replaced with LBound and UBound functions.
|
Dim WS As Worksheet Dim All_Data(0 To 17, 0 To 4) As Variant Dim i, j As Long Set WS = Worksheets("Sheet1") For i = LBound(All_Data, 1) To UBound(All_Data, 1) For j = LBound(All_Data, 2) To UBound(All_Data, 2) All_Data(i, j) = WS.Range("A2").Offset(i, j).Value Next j Next i |
So now you know how to put values from worksheet to a multidimensional array. So what if you need to do the reversal. ie-What if you need to put values from an array to a worksheet range. Code is very much similar to the above example. You just need to do a change in one line. Following is an example. Here what we do is, first we get data from first 3 columns to an array. Then reads values from array and put them back to sheet 3.
|
Dim WS As Worksheet Dim All_Data(0 To 17, 0 To 2) As Variant Dim i, j As Long Set WS = Worksheets("Sheet1") For i = LBound(All_Data, 1) To UBound(All_Data, 1) For j = LBound(All_Data, 2) To UBound(All_Data, 2) All_Data(i, j) = WS.Range("A2").Offset(i, j).Value Next j Next i Dim WS_New As Worksheet Set WS_New = Worksheets("Sheet3") For i = LBound(All_Data, 1) To UBound(All_Data, 1) For j = LBound(All_Data, 2) To UBound(All_Data, 2) WS_New.Range("A1").Offset(i, j).Value = All_Data(i, j) Next j Next i |
Here is the result you will get.
