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. To simplify our tutorial, example on a two-dimensional array is used. 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




* 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"

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts