### 2.4 WS array constant

- an
**array constant**is usually interpreted as a WS data structure - the concept of an
**constant array**in VBA, using the Const keyword is not supported

An array constant in a formula or defined name (RefersTo):

- is enclosed within curly braces {…}
- uses a comma "," to separate each element in a row. This concept is similar to a record is a CSV file
- uses a semi-colon ";" to mark the end of each row, except the last row
- can contain numbers, text, logical values (TRUE and FALSE) and error values (like #N/A!)
- uses double quotes "" to indicate text
- cannot contain functions, references, formulas, or other array constants
- that is a multi-dimensional array constant, must be rectangular with the same number of elements in each row
- Note: percentages must be decimal or text (0.1 or "10%")
- In this example we assume that Windows Regional Settings is set to Australia, and the
**Application.DecimalSeparator**property equals "."

### 1. An array constant in a WS formula

Examples of row, column, and multi-dimensional array constants are shown here:

- Row array (1 x 4)
={11,12,13,14}

- Column array (3 x 1)
={11;21;31}

- Multi-dimensional (3 x 4)
={11,12,13,14;21,22,23,24;31,32,33,34}

To enter the formula, select a WS range of the correct dimension, type in the formula, then press the **Control + Shift + Enter** key combination. An array constant with a single element is possible but unnecessary.

### 2. An array constant as a defined name

Continuing with the example from figure 1, display the **Refers To:** panel (figure 2). Ensure that the string commences with the formula equals (=) character.

The range shown in figure 1 is named **ThreeX4**, and has been moved 3 columns to the right. The view in figure 3 shows the details for the array name and array **ThreeX4** constant **ThreeByFour**.

### 3. Examples of WS array constants

### 3.1 WS SUBTOTAL function

Reference: Data analysis :: subtotals

Name: ListSubTotal RefersTo: ={"average",101,1;"count",102,2;"counta",103,3;"max",104,4;"min",105,5;"product",106,6;"stdev",107,7;"stdevp",108,8;"sum",109,9;"var",110,10;"varp",111,11} =SUBTOTAL(VLOOKUP(RC[-1],ListSubTotal,2,FALSE),Data) + N("Excludes hidden values") Name: Stats5x1 RefersTo: ={"Average","StDev","Min","Max","Count"}

### 3.2 TAX table array constant

Reference: Arrays, and sub-arrays, with application to tax rates

Name: TaxArray1516 RefersTo: ={0,0,"0%";18201,0,"19.0%";37001,3572,"32.5%";80001,17547,"37.0%";180001,54547,"45.0%"} =VLOOKUP(RC[-1],TaxArray1516,2,TRUE) +(RC[-1]-VLOOKUP(RC[-1],TaxArray1516,1,TRUE)+1) *VLOOKUP(RC[-1],TaxArray1516,3,TRUE)

**Development platform**: Excel 2016 365 ProPlus 64-bit- Workbook file: xlf-infile-notes-2.4.xlsx [13 KB]