I've these multi-dimensional array in Excel
Dim world(192,15,40) as Variant
Dim country(192,15) as Variant
I can't find a way to insert the whole country into world. something like:
world()()(2)=country
or
country(:,:,20)=currentSheet.Range("A1:F50")
or just reference a region to insert it back on the table
Range.value=world(20:50,1:50,2)
Is this possible?
Thanks.
I am not sure what you are trying to achieve so cannot just correct your syntax. I hope the following gives enough background for you to correct your code.
If you write:
Dim X as Variant
X = .Range("A1").Value
then X will be a single value.
If you write:
Dim X as Variant
X = .Range("A1:F50").Value
then X will be a two dimensional array sized as (1 to 50, 1 to 6). That is the first dimension is for rows and the second dimension is for columns. This is the opposite of normal practice but matches .Cells(Row,Column).
You can also write:
Dim X(1 to 192) as Variant
X(2) = .Range("A1:F50").Value
See my answer to Excel macro loading arrays for more information on jagged arrays.
I don't think there's a single command you can use to accomplish this. The best way I know would be to loop through the dimensions you want to copy:
For i = LBound(country, 1) to UBound(country, 1)
For j = LBound(country, 2) to UBound(country, 2)
world(i, j, 2) = country(i, j)
Next j
Next i
Using LBound and UBound will ensure you get all of the data from each dimension of the array.
The VBA Variant is pretty flexible, and you can use it to do what you want, but not to do exactly what you're asking. They key is to ditch the 3-D array and instead use an array that contains other arrays.
I might be rearranging the meaning of your indices here, but consider something like this:
Dim china
china = [china!Q42:X99]
Dim india
india = [india!Q42:X99]
...
Dim world(1 to 242)
world(1) = china
world(2) = india
...
[report!Q42:X99] = world(42)
This example uses a 1-D array of Variants, each element of which is a 2-D array of Variants that comes from various Excel ranges. If you can think of a 3-D array as a cube, think of this structure as a (1-D) book of (2-D) pages.
Normally, messing with data using only arrays (nested or otherwise) can be fraught with peril. However, in VBA code supporting an Excel spreadsheet it can often be the most natural way to do things. It's possible to define Types and Classes in VBA, but many times that's overkill because both the source of your data and the final results of your calculation are just 2-D ranges on a worksheet somewhere.
Here are some relevant earlier answers of mine to related questions:
