## Saturday, 1 December 2012

### Excel VBA -Multi-Dimensional Array Example

How to Create a Multi-Dimensional Array in VBA
An array can also store multiple dimensional data.Assume you have data of a local store's yearly sale in the following table and you want to store the data in a two-dimensional array:

Year 2003 Year 2004
CD Sale 1,000 1,500
DVD Sale 1,200 2,000

First we create the array as follow:

Dim Arr(2,2)

Then we assign the values into the array. We treat the first dimension as the year and the second dimension as the product sale:

arr(1,1) = 1000
arr(1,2) = 1200
arr(2,1) = 1500
arr(2,2) = 2000

We now display the values of the array with a message box:
Msgbox "Sale of CD in 2003 is " & arr(1,1) & vbCrLf & "Sale of CD in 2004 is " _
& arr(2,1) & vbCrLf & "Sale of DVD in 2003 is " & arr(1,2) & vbCrLf _
& "Sale of DVD in 2004 is " & arr(2,2)

The complete precedure is as followed:

Option Base 1
Sub multDimArray( )
Dim Arr(2,2)

arr(1,1) = 1000
arr(1,2) = 1200
arr(2,1) = 1500
arr(2,2) = 2000

Msgbox "Sale of CD in 2003 is " & arr(1,1) & vbCrLf & "Sale of CD in 2004 is " _
& arr(2,1) & vbCrLf & "Sale of DVD in 2003 is " & arr(1,2) & vbCrLf _
& "Sale of DVD in 2004 is " & arr(2,2)
End Sub

Notes :
* vbCrLf stands for VB Carriage Return Line Feed. It puts a return and a new line as shown in the message box above. The underscore "_" on the back of the first line of the message box means "continue to the next line"