#10 How to Manipulate Data with Multidimensional and Dynamic Arrays in Excel VBA

Sub MultiDimArray()

Dim myArray(5, 3) As Integer
myArray(1, 1) = 25
myArray(2, 1) = 35
myArray(1, 2) = 45
myArray(2, 2) = 55

Debug.Print myArray(1, 1)
Debug.Print myArray(2, 1)
Debug.Print myArray(1, 2)
Debug.Print myArray(2, 2)

End Sub

Sub DynamicArray()

Dim myArray() As Integer

ReDim myArray(5)

myArray(1) = 10
myArray(2) = 20

Debug.Print myArray(1)
Debug.Print myArray(2)

ReDim Preserve myArray(10)
myArray(6) = 30
Debug.Print myArray(6)

End Sub

Sub SalesTotals()
Dim sales(1 To 3, 1 To 3) As Integer
Dim totalSales(1 To 3) As Integer
Dim i As Integer, j As Integer

' Reading data into the array
For i = 1 To 3
For j = 1 To 3
sales(i, j) = Sheet1.Cells(i + 1, j + 1).Value
Next j
Next i

' Calculating total sales
For i = 1 To 3
For j = 1 To 3
totalSales(i) = totalSales(i) + sales(j, i)
Next j
Next i

' Writing total sales back to the worksheet
For i = 1 To 3
Sheet1.Cells(5, i + 1).Value = totalSales(i)
Next i

End Sub