User Guide : Scripting : Variables : Array Variables
Share this page             
Array Variables
A variable with the array dimension is a single variable with many compartments to store values. Unlike an array, a scalar variable has one compartment that can store only one value. Both scalar and array variables are declared using the Dim, Private, or Public statement.
To store the names of all the months you can declare 12 scalar variables.
Private Month1
Month1 = "January"
Private Month2
Month2 = "February"
Private Month3
Month3 = "March"
Private Month12
Month12 = "December"
However, this method requires coding a large number of types. Using an array is a more elegant and efficient way to store a group of related values.
Referencing Elements in Arrays
Arrays can have up to three dimensions and up to 1,000,000 elements. Each element of an array stores one value and is referenced by its index (coordinate position). The index of the first element of an array is called its lower bound, while the index of the last element is called its upper bound. By default, an array is indexed beginning with zero. The declaration must include the number of elements inside parentheses. In the months example, the lower bound is zero, while the upper bound is 12. The upper and lower bounds of an array can be inspected using the LBound and UBound keywords. For arrays with more than one dimension, the index of each dimension must be included to reference an individual element. The entire array can be referenced using just the array name, with no index. This is useful for assigning the same value to every element or clearing all the values in the array.
The syntax is dim array([lbound to] ubound). Therefore, if there is only one value, it is ubound, with lbound set to either the default of 0, or the value of the option base statement.
Declaring Arrays
To declare an array, use the Dim, Private, or Public statement with the array name, then include the size in parentheses. Upper and lower bounds can be explicitly declared using the To clause.
Example: One-Dimensional Array
{Dim | Private | Public} ArrayName(NumberOfElements)
Dim MyArray(11)
{Dim | Private | Public} ArrayName(LowerBound To UpperBound)
Dim MyArray(0 to 11)
Where ArrayName is the array variable name; NumberOfElements is the size, in elements, of the array; LowerBound is the index number of the first element; UpperBound is the index number of the last element.
Example: Multi-Dimensional Array
{Dim | Private | Public} ArrayName(Dimension1[,Dimension2][,Dimension3])
Dim MyArray(3, 4, 5)
{Dim | Private | Public} ArrayName(LowerBound1 To UpperBound1 [,LowerBound2 To UpperBound2][,LowerBound3 To UpperBound3])
Dim MyArray(0 to 2, 0 to 3, 0 to 4)
Where ArrayName is the array variable name; Dimension1 is the size, in elements, of the array's first dimension; Dimension2 is the size, in elements, of the second dimension (optional); Dimension3 is the size, in elements, of the third dimension (optional). LowerBound and UpperBound are the upper and lower bounds of the respective array dimensions.
Variables may not be used as size placeholders when initially declaring an array. For example, Dim MyArray(VariableName) is illegal. A map checks for the following at run time:
Upper/lower bounds (+1) are not greater than the limit of 32,766 in each dimension
The total number of elements (all dimensions multiplied together) is not greater than the 100,000-element limit for arrays
Whether an array is indexed from 0 or 1 depends on the setting of the Option Base Statement. If Option Base 1 is not specified, all array indexes begin at zero. The Option Base statement has no effect on arrays declared with explicit bounds.
Note:  When you declare a Dim statement for arrays, be aware that the number of elements is always one greater than the upper bounds because the index starts with zero. For example, if you need five placeholders, dimension your array with a four and index it as (0), (1), (2), (3) and (4).
The lower bound of an array can be determined with the LBound Function. The upper bound of an array can be determined using the UBound Function.
Use an ExecuteExpression action with the TransformationStarted event to declare an array with the Private statement. This creates an array that exists throughout the transformation, allowing you to use it and the values of its elements across multiple fields and records.
Tip...  To dynamically size an array, initially declare it without a size or explicit bounds. When the number of elements required is known, use the ReDim Statement to resize the array.
Storing Data in Arrays
Assigning values to an element in an array is similar to assigning values to scalar variables. Simply reference an individual element of an array using the array name and the index inside parentheses, then use the assignment operator (=) followed by a value. Until an array element is first assigned a value, it has a Null (empty) value.
In the months example, the values are set as follows:
Month(0) = "January"
Month(1) = "February"
Month(2) = "March"
Month(11) = "December"
With larger arrays, assigning each element individually is tedious. A more efficient way to accomplish this task is to use a scalar variable to store a counter value and a For...Next statement to loop through the elements. The index of the element is then dynamically referenced using that counter variable.
' Declare a 1-dimension array and a counter variable
Dim MyArray(4)
Dim X
' Loop through the array to assign values
For X = 0 To 4
  MyArray(X) = X * 5
Next X
For two- or three-dimension arrays, multiple For...Next statements and counter variables can be nested to loop through all the elements, in each dimension.
' Declare a 2-dimension array and counter variables
Dim MyArray(5,10)
Dim X
Dim Y
' Loop through the 2nd dimension of the array
For Y = 0 To 10
' Loop through the 1st dimension of the array
  For X = 0 To 5
    MyArray(X,Y) = X * Y
  Next X
Next Y
The array indexes can be combined with metadata expressions to assign values from a source record to elements in an array. The following example loads the first four fields of the file into a 1-dimension array:
Map Window / TransformationStarted Event / ExecuteExpression Action
' Declare a 1-dimension array and a counter variable
Dim MyArray(3)
Dim X
Map Window / Source Record Layout 1 / RecordEnded Event / ExecuteExpression Action
Assign a target field a value from the array. Create this expression for the target field 2: MyArray(2).
' Loop through the four array elements and copy their values
' to the first four target fields
For X = 0 To 3
Records("R1").Fields(X + 1)
Relative field positions start counting at one, unlike the default lower bound for array indices, which is zero. In order to use the same counter variable to loop through both the array elements and the source fields, the value of that counter must be incremented by 1 before it is used to reference the field position. Alternatively, the default lower bound for arrays can be changed to 1 using the Option Base statement.
Since the array is loaded with the RecordEnded event, the values of each array element get refreshed with each new source record.
Retrieving Data from Arrays
Retrieving values from array elements is similar to retrieving values from scalar variables. Simply list the variable name where the value should be copied, followed by the assignment operator (=), then the array name and the element index inside parentheses.
TempVariable = Month(1)
The code above uses the array from the months example. The value of the second array element is copied into a scalar variable. The value is then logged:
To use the value of an array element in a function or target field expression, simply list the array name and the element index. In the months example, you can rewrite the values without the intermediate variable, like so:
Arrays with two dimensions can be used to store multiple fields and multiple records. When used in combination with event-condition-action rules, arrays become powerful tools to store summary data, such as number of records output by record type and errors per field.