Arrays

Using arrays, you can group related sets of data together.

A one-dimensional array is like a column of tabular data, a two-dimensional array is like a spreadsheet with rows and columns, and a three-dimensional array is like a 3D grid.

In VBScript, we can create arrays up to 60 dimensions.

Arrays are declared much like regular variables except you follow the variable name with information describing the size and dimensions of the array.

Dim testArray(9) à Array with ten data elements.

Values in an array always begin at 0 and end at the number of data points in the array minus 1. This is the reason an array with 10 data points is initialized as testArray(9).

Use the index position to set values for the array as well, as in the following:

test(0) = “first element”
test(1) = “second element”

Multiple dimensions are created by separating the size of each dimension with commas, such as currentArray(3,3,3) or testArray(2,5,5,4). You can create a two-dimensional array with five columns each with four rows of data points as follows:

Dim myArray(4,3)

Then, if you want to obtain the value of a specific cell in the spreadsheet, you can use the following:

theValue = arrayName(columns -1, rows -1)

in which columns is the column position of the cell and rows is the row position of the cell. Following this, you can get the value of the cell in column 3, row 2 with this statement:

myValue = myArray(2,1)

Sizing arrays on-the-fly allows you to use input from users to drive the size of an array. You declare a dynamic array without specifying its dimensions, as follows:

Dim userArray()

Then size the array later using the ReDim function:

ReDim userArray(currValues – 1)

ReDim userArray(numColumns – 1, numRows – 1)

You can also use ReDim to change the size of an existing array. For example, you can increase the size of an array from 10 elements to 20 elements. However, when you change the size of an existing array, the array’s data contents are destroyed. To prevent this, use the Preserve keyword, as follows:

ReDim Preserve userArray(numColumns – 1, numRows – 1)