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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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

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

do you have an example that is used in excel?
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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 PrewIT / Software Engineering ConsultantCommented:
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.

Bill PrewIT / Software Engineering ConsultantCommented:

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FloraAuthor Commented:
Thank you very much Bill and Martin.


you led me to the right direction, thanks a lot.
Bill PrewIT / Software Engineering ConsultantCommented:

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.