Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Multidimensional Arrays in VBA

        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.

Contact Form

Name

Email *

Message *