Saturday, December 1, 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 1000 1500
DVD Sale 1200 2000

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



---------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
---------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog