Excel 2010 Macro Arrays
An array is a type of variable used to hold more than one piece of data. For example, if you have to work with the name and address of a client, you probably would assign one variable for the name and another for the address of the client. Instead, use an array, which can hold both pieces of information. This applies for not just one client, but for hundreds.
Declare an array by adding parentheses after the array name. The parentheses contain the number of elements in the array:
Dim myArray (2)
This creates an array, myArray, which contains three elements. Three elements are included because the index count starts at 0 by default:
myArray(0) = 10
myArray(1) = 20
myArray(2) = 30
If the index count needs to start on 1, use Option Base 1. This forces the count to start at 1. To do this, place the Option Base statement in the declarations section of the module:
Option Base 1
Dim myArray(2)
This now forces the array to have only two elements.You can also create an array independent of the Option Base statement by declaring its lower bound:
Dim myArray (1 to 10)
Dim BigArray (100 to 200)
How to Pass an Array
Arrays can be passed into other procedures just like strings, integers, and other variables, thus making for more efficient and easier-to-read code. The following sub, PassAnArray, passes the array, myArray, into the function RegionSales. The data in the array is summed for the specified region and the result returned to the sub:
Sub PassAnArray()
Dim myArray() As Variant
Dim myRegion As String
myArray = Range(“mySalesData”) ‘named range containing all the data
myRegion = InputBox(“Enter Region - Central, East, West”)
MsgBox myRegion &“ Sales are: “ & Format(RegionSales(myArray, _
myRegion), “$#,#00.00”)
End Sub
Function RegionSales(ByRef BigArray As Variant, sRegion As String) As Long
Dim myCount As Integer
RegionSales = 0
For myCount = LBound(BigArray) To UBound(BigArray)
‘The regions are listed in column 1 of the data, hence the 1st column of the
array
If BigArray(myCount, 1) = sRegion Then
‘The data to sum is the 6th column in the data
RegionSales = BigArray(myCount, 6) + RegionSales
End If
Next myCount