can you show me how does a 3 dimensional arrays in Excel looks like?

i only known the two dimensional array in excel rows and column.

i am learning about arrays.

can you show me in example what is the 3 or 4 dimensional arrays in excel vba?
Who is Participating?
Bill PrewCommented:

As you point out there are typically several ways to solve a problem.  You have mentioned User Defined Types (UDTs) and I agree they can be very useful.  However I typically think of them as a way to gather several different 'attributes' together in a single named variable, where as I think of arrays as multidimensional collections of the same type of data.  Subtle difference, but it's how I think of it.

Also, arrays have the feature of accessing elements by numeric index values that can be driven at runtime.  This is good and bad, it allows looping and 'pointer' type referencing to be done, but it doesn't associate a 'name' to an index value.  I'm not sure there is a way to dynamically address an element in a UDT, is there?  And can you loop over the items in the UDT if you don't know their names?

Not saying one is better than the other, just want Flora to understand that they are different and have pros and cons.  I would think it's useful to have a solid understanding of arrays, and then as VBA knowledge grows add a good understanding of UDTs, structures, collections, dictionaries, etc.

Bill PrewCommented:
Take a look at this explanation.

Martin LissOlder than dirtCommented:
Here's the best way to do it.

Option Explicit

Private Type FourDimention
    D1 As String
    D2 As String
    D3 As String
    D4 As String
End Type

Sub Demo()

Dim my4d(1 To 2) As FourDimention

my4d(1).D1 = "a"
my4d(1).D2 = "b"
my4d(1).D3 = "c"
my4d(1).D4 = "d"

my4d(2).D1 = "1"
my4d(2).D2 = "2"
my4d(2).D3 = "3"
my4d(2).D4 = "4"

MsgBox my4d(1).D2
MsgBox my4d(2).D4

End Sub

Open in new window

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

FloraAuthor Commented:
this is a very good explanation Bill. thanks.

do you have an example that is used in excel?
Martin LissOlder than dirtCommented:
Flora, the link Bill posted shows the traditional way multi-dimension arrays are built, but why bother with the complications when a one-diension array like mine will do the same more easily?
Bill PrewCommented:
One dimension and two dimension arrays are not uncommon.  Three dimensional are less common.

And since an Excel spreadsheet is in general a two dimensional array conceptually, a lot of time work is done on the sheets rather than arrays.  But for a lot of analysis arrays can be faster.

An example of using a three dimension array might be to crunch a large set of numbers that is organized by year, month and day.  You could load the data into a three dimensional array something like:

Dim MyData(9, 11, 30)

and then if we assume the first year we are loading with be 2000 and place in MyData(0,,) then a few array locations for various data would be:

1/1/2000 = MyData(0, 0, 0)
1/2/2000 = MyData(0, 0, 1)
2/1/2000 = MyData(0, 1, 0)
2/2/2000 = MyData(0, 1, 1)
1/1/2001 = MyData(1, 0, 0)
1/2/2001 = MyData(1, 0, 1)
2/1/2001 = MyData(1, 1, 0)
2/2/2001 = MyData(1, 1, 1)

Once the data is loaded you can do FOR loops over the array and do calculations on the numbers in the array.

FloraAuthor Commented:
Thank you very much Bill and Martin.


you led me to the right direction, thanks a lot.
Bill PrewCommented:

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.