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