Home > System Defined Functions Reference > System Defined Functions Addendum > ArrayInitFromSheet

ArrayInitFromSheet

ArrayInitFromSheet(

AttributeName,
SheetName [,
RangeName or CellID = “A1” [,
RowHeadings = False [,
ColumnHeadings = False [,
RowData = False ] ] ] ] )

Although the name of this function is Init, it does not imply it is only executed at SimInit time. It can be used in any attribute assignment and executed at any designed time, just like ArrayInit().

Empty cells are not used to initialize the corresponding array elements.

Return Value:

The cell ID of the bottom right cell of the effective range, that is, the actual range of data that are used to set the attribute array, is returned.

Arguments:

AttributeName is the name of the attribute, for example S.Sales, T.ProcTimes.

SheetName is the name of a Basic Sheet in double quotes. It must exist, that is, must be a component of the active document.

RangeName or CellID (String, default value is “A1”). It is either the name of a defined Named Range in SheetName or a cell ID (e.g. “C3” or “E12”). These two can share the same field of this function because Basic Sheet does not allow a range name that will conflict with cell ID. Refer to function CellID for details on cell ID. With this argument, a block of cells of the sheet are identified. If a CellID is used, all cells to the right and below of the specified cell are read.

RowHeadings (Boolean, default value is False). This argument indicates whether the specified range contains row headings. Headings are used to index array elements. Empty cell is an invalid heading. If row headings are required for indexing and no row heading exists, then row numbers are used. Row numbers are from 1 to the number of rows of the effective block. If RowHeadings is True, from the first cell in the column for row headings to the last cell or the cell before the empty cell are the effective rows. Only those data that are in the effective rows will be used for initialization.

ColumnHeadings (Boolean, default value is False). This argument indicates whether the specified range contains column headings. Headings are used to index array elements. An empty cell is an invalid heading. If there is no column heading but they are required for indexing, then column numbers are used. Column numbers starts from 1, not the string “A”, to number of columns of the effective block. If ColumnHeadings is True, from the first cell in the row for column headings to the last one or the one before the empty cell are the effective columns. Only those data that are in the effective columns will be used for initialization.

RowData (Boolean, default value is False). This argument specifies whether data listed in the specified range (effective block) become row headings or column headings. If RowData is False, column headings (or numbers) are used for the first indexes, and row headings (or numbers) are used for the second indexes. If RowData is True, row headings (or numbers) are used for the first indexes, and column headings (or numbers) are used for the second indexes.

Resulting Array Dimensions

This function does not always result in a two-dimensional (2-D) array. Internally, all array elements are indexed as a two-dimensional array initially, but then may become a one-dimensional (1-D) array when the function completes. When the specified range contains both row and column headings or contains multiple rows and columns of data (without heading row and heading column), it will be made a 2-D array. However, when data in the effective block can be either a 1-D or 2-D array, the simulator makes them a 1-D array. In particular, if one of the indexes in all the elements are 1 (and not a user-provided heading), then that index is eliminated and the array becomes a 1-D array.

The following examples show the special cases where a 2-Dimensional (2-D) array can be made a 1-Dimensional (1-D) array by iGrafx, with some examples of where it must remain a 2-D array:

  1. Data listed in just one row or column with no heading at all

    2. data

    data

    data

    RowHeadings = False   ColHeadings = False
    RowData = True, initially the array shall be X[ 1, j ]
    RowData = False, initially the array shall be X[ j, 1 ]

    The array becomes a 1-D array indexed with column numbers, i.e. X[ j ].

    Data

    Data

    Data

    Data

    RowHeadings = False   ColHeadings = False
    RowData = True, initially the array shall be X[ i, 1 ]
    RowData = False, initially the array shall be X[ 1, i ]

    The array becomes a 1-D array indexed with row numbers, i.e. X[ i ].

    Data

    RowHeadings = False   ColHeadings = False
    RowData = True, initially the array shall be X[1, 1].
    RowData = False, initially the array shall be X[1, 1].

    The array becomes a 1-D array, i.e. X[ 1 ].

  2. Data listed in one row with its row heading but no column headings

    3. Row HD1

    Data

    Data

    RowHeadings = True   ColHeadings = False
    RowData = True, the array shall be X[ RH1,      j ].
    RowData = False, the array shall be X[       j, RH1 ].

    Row headings are user provided, and cannot be eliminated. This is a 2-D array.

    Row HD1

    Data

    RowHeadings = True   ColHeadings = False
    RowData = True, initially the array shall be X[ RH1,     1 ].
    RowData = False, initially the array shall be X[      1, RH1 ].

    The array becomes a 1-D array indexed with user provided row heading, i.e. X[ RH1 ].

  3. Data listed in one row with column headings but no row heading

    4. Col HD1

    Col HD2   

    Col HD3

    data

    data

    Data

    RowHeadings = False   ColHeadings = True
    RowData = True, initially the array shall be X[     1, CHj ].
    RowData = False, initially the array shall be X[ CHj,     1 ].

    The array becomes a 1-D array indexed with column headings, X[ CHj ].

  4. Data listed in one column with its column heading but no row headings

    5. Col HD1

    data

    data

    data

    data

    RowHeadings = False   ColHeadings = True
    RowData = True, the array shall be X[      i, CH1 ].
    RowData = False, the array shall be X[ CH1,      i ].

    Column headings is user provided, and cannot be eliminated. This is a 2-D array.

    Col HD1

    data

    RowData = True, initially the array shall be X[      1, CH1 ].
    RowData = False, initially the array shall be X[ CH1,      1 ].

    The array becomes a 1-D array indexed with user provided column heading, X[ CH1 ].

  5. Data listed in one column with row headings but no column heading

Row HD1

data

Row HD2

data

Row HD3

data

Row HD4

data

RowHeadings = True   ColHeadings = False
RowData = True, initially the array shall be X[ RHi,     1 ].
RowData = False, initially the array shall be X[     1, RHi ].

The array becomes a 1-D array indexed with row headings, X[ RHi ].