Microsoft Excel ptgs

The following table contains all ptgs that appear in BIFF files. All other ptgs are reserved.

The ptgSheet and ptgEndSheet tokens are no longer used. The external sheet references are contained in the ptgNameX, ptgRef3d, and ptgArea3d tokens.

Name	Ptg	Type

ptgExp	01h	control
ptgTbl	02h	control
ptgAdd	03h	operator
ptgSub	04h	operator
ptgMul	05h	operator
ptgDiv	06h	operator
ptgPower	07h	operator
ptgConcat	08h	operator
ptgLT	09h	operator
ptgLE	0Ah	operator
ptgEQ	0Bh	operator
ptgGE	0Ch	operator
ptgGT	0Dh	operator
ptgNE	0Eh	operator
ptgIsect	0Fh	operator
ptgUnion	10h	operator
ptgRange	11h	operator
ptgUplus	12h	operator
ptgUminus	13h	operator
ptgPercent	14h	operator
ptgParen	15h	control
ptgMissArg	16h	operand
ptgStr	17h	operand
ptgAttr	19h	control
ptgSheet	1Ah	(ptg DELETED)
ptgEndSheet	1Bh	(ptg DELETED)
ptgErr	1Ch	operand
ptgBool	1Dh	operand
ptgInt	1Eh	operand
ptgNum	1Fh	operand
ptgArray	20h	operand, reference class
ptgFunc	21h	operator
ptgFuncVar	22h	operator
ptgName	23h	operand, reference class
ptgRef	24h	operand, reference class
ptgArea	25h	operand, reference class
ptgMemArea	26h	operand, reference class
ptgMemErr	27h	operand, reference class
ptgMemNoMem	28h	control
ptgMemFunc	29h	control
ptgRefErr	2Ah	operand, reference class
ptgAreaErr	2Bh	operand, reference class
ptgRefN	2Ch	operand, reference class
ptgAreaN	2Dh	operand, reference class
ptgMemAreaN	2Eh	control
ptgMemNoMemN	2Fh	control
ptgNameX	39h	operand, reference class
ptgRef3d	3Ah	operand, reference class
ptgArea3d	3Bh	operand, reference class
ptgRefErr3d	3Ch	operand, reference class
ptgAreaErr3d	3Dh	operand, reference class
ptgArrayV	40h	operand, value class
ptgFuncV	41h	operator
ptgFuncVarV	42h	operator
ptgNameV	43h	operand, value class
ptgRefV	44h	operand, value class
ptgAreaV	45h	operand, value class
ptgMemAreaV	46h	operand, value class
ptgMemErrV	47h	operand, value class
ptgMemNoMemV	48h	control
ptgMemFuncV	49h	control
ptgRefErrV	4Ah	operand, value class
ptgAreaErrV	4Bh	operand, value class
ptgRefNV	4Ch	operand, value class
ptgAreaNV	4Dh	operand, value class
ptgMemAreaNV	4Eh	control
ptgMemNoMemNV	4Fh	control
ptgFuncCEV	58h	operator
ptgNameXV	59h	operand, value class
ptgRef3dV	5Ah	operand, value class
ptgArea3dV	5Bh	operand, value class
ptgRefErr3dV	5Ch	operand, value class
ptgAreaErr3dV	5Dh	operand, value class
ptgArrayA	60h	operand, array class
ptgFuncA	61h	operator
ptgFuncVarA	62h	operator
ptgNameA	63h	operand, array class
ptgRefA	64h	operand, array class
ptgAreaA	65h	operand, array class
ptgMemAreaA	66h	operand, array class
ptgMemErrA	67h	operand, array class
ptgMemNoMemA	68h	control
ptgMemFuncA	69h	control
ptgRefErrA	6Ah	operand, array class
ptgAreaErrA	6Bh	operand, array class
ptgRefNA	6Ch	operand, array class
ptgAreaNA	6Dh	operand, array class
ptgMemAreaNA	6Eh	control
ptgMemNoMemNA	6Fh	control
ptgFuncCEA	78h	operator
ptgNameXA	79h	operand, array class (NEW ptg)
ptgRef3dA	7Ah	operand, array class (NEW ptg)
ptgArea3dA	7Bh	operand, array class (NEW ptg)
ptgRefErr3dA	7Ch	operand, array class (NEW ptg)
ptgAreaErr3dA	7Dh	operand, array class (NEW ptg)

Extended ptgs in BIFF8

In BIFF8, expended ptgs are used to store natural-language formulas. These ptgs contain a ptgExtend (ptg = 18h), followed by a 1-byte extended ptg type, called an eptg, and then followed by extended data if applicable. Extended ptgs are listed in the following table.

Eptg	Eptg Type	Size	Extended info?	Operand Type

0h	Reserved
1h	eptgElfLel	4	No	Error
2h	eptgElfRw	4	No	Reference
3h	eptgElfCol	4	No	Reference
4h – 5h	Reserved
6h	eptgElfRwV	4	No	Value
7h	eptgElfColV	4	No	Value
8h – 9h	Reserved
Ah	eptgElfRadical	13	No	Reference
Bh	eptgElfRadicalS	13	Yes	Reference
Ch	eptgElfRwS	4	Yes	Reference
Dh	eptgElfColS	4	Yes	Reference
Eh	eptgElfRwSV	4	Yes	Value
Fh	eptgElfColSV	4	Yes	Value
10h	eptgElfRadicalLel	4	No	Error
11h – 18h	Reserved
19h – 1Ah	Invalid values
1Bh	Reserved
1Ch	Reserved
1Dh	eptgSxName	4	No	Value
1Eh	Reserved

The data contained in eptgs is not documented.

For eptgs that have extended info, the extra information is appended to the saved parsed expression, immediately following the last token. The format of the extra information is as follows:

First 4 bytes: the lowest 30 bits of these bytes is the count (cLoc) of 4-byte structures following these 4 bytes.

The next 4*cLoc bytes are reserved.

As with array constants, if a formula contains more than one eptg with extended information, the token values for the eptgs are appended to the saved parsed expression in order: first the values for the first eptg, then the values for the second eptg, and so on.

Likewise, an expression containing both eptgs and array constants will append the eptg and array constant data in the order that they occur in the parsed expression.

Expression Evaluation

Calculation of Microsoft Excel formulas is a straightforward process. A last-in, first-out (LIFO) stack, the operand stack, is maintained during calculation. When an operand is encountered, it's pushed onto the stack. When an operator is encountered, it operates on the topmost operand or operands. Operator precedence is irrelevant at evaluation time; operators are handled as soon as they're encountered.

There are three kinds of operators: unary, binary, and function. Unary operators, such as the minus sign that negates a number, operate only on the top operand. Binary operators, such as the addition operator, operate on the top two operands. Function operators, which implement Microsoft Excel functions, operate on a variable number of operands, depending on how many arguments the function accepts.

All operators work by popping the required operands from the stack, performing calculations, and then pushing the result back onto the operand stack.

Scanning a Parsed Expression

One fairly common operation you can perform on parsed expressions is to scan them, taking appropriate actions at each ptg. You can do this with a loop by using a pointer variable that points to the next ptg to scan. However, you must increment this pointer carefully, because different ptgs may have token values of different lengths.

One approach is to maintain an array with one element per ptg. Each element contains the size of the token value. To increment the pointer, add the array element corresponding to the current ptg to the pointer. One way of reducing the array size is to limit the array indexes to the range 0–3Fh and then index it using the reference-class ptg (the base ptg) instead of the value-class or array-class ptg. This is possible because the token value is the same for all classes of a particular ptg. For more information about operand classes, see "ptg Values for Operand Tokens" on page 454.

There are two tokens, ptgStr and ptgAttr (when bitFAttrChoose is true), that have variable length and are therefore exceptions to the preceding description. The first token, ptgStr, is followed by a variable-length string. The token value specifies the length of the string, so the pointer can be incremented by reading the string length (cch) and then adding the string length to the pointer.

The other token is ptgAttr when bitFAttrChoose is true. In this case, the token value contains an optimized CHOOSE function, which contains a variable-length sequence of word offsets in the cases (value1, value2, … arguments) for the CHOOSE function. For these, you can use the wCases field to calculate the pointer increment.

Unary Operator Tokens

The unary operator tokens for Microsoft Excel are described in the following paragraphs. These operators pop the top argument from the operand stack, perform a calculation, and then push the result back onto the operand stack.

ptgUplus: Unary Plus (ptg = 12h)

Has no effect on the operand.

ptgUminus: Unary Minus (ptg = 13h)

Negates the operand on the top of the stack.

ptgPercent: Percent Sign (ptg = 14h)

Divides the top operand by 100.

Binary Operator Tokens

There are several binary operator ptgs. All binary operator ptgs pop the top two arguments from the operand stack, perform the associated calculation, and then push the result back onto the operand stack.

ptgAdd: Addition (ptg = 03h)

Adds the top two operands.

ptgSub: Subtraction (ptg = 04h)

Subtracts the top operand from the second-to-top operand.

ptgMul: Multiplication (ptg = 05h)

Multiplies the top two operands.

ptgDiv: Division (ptg = 06h)

Divides the top operand by the second-to-top operand.

ptgPower: Exponentiation (ptg = 07h)

Raises the second-to-top operand to the power of the top operand.

ptgConcat: Concatenation (ptg = 08h)

Appends the top operand to the second-to-top operand.

ptgLT: Less Than (ptg = 09h)

Evaluates to TRUE if the second-to-top operand is less than the top operand; evaluates to FALSE otherwise.

ptgLE: Less Than or Equal (ptg = 0Ah)

Evaluates to TRUE if the second-to-top operand is less than or equal to the top operand; evaluates to FALSE otherwise.

ptgEQ: Equal (ptg = 0Bh)

Evaluates to TRUE if the top two operands are equal; evaluates to FALSE otherwise.

ptgGE: Greater Than or Equal (ptg = 0Ch)

Evaluates to TRUE if the second-to-top operand is greater than or equal to the top operand; evaluates to FALSE otherwise.

ptgGT: Greater Than (ptg = 0Dh)

Evaluates to TRUE if the second-to-top operand is greater than the top operand; evaluates to FALSE otherwise.

ptgNE: Not Equal (ptg = 0Eh)

Evaluates to TRUE if the top two operands are not equal; evaluates to FALSE otherwise.

ptgIsect: Intersection (ptg = 0Fh)

Computes the intersection of the top two operands. This is the Microsoft Excel space operator.

ptgUnion: Union (ptg = 10h)

Computes the union of the top two operands. This is the Microsoft Excel comma operator.

ptgRange: Range (ptg = 11h)

Computes the minimal bounding rectangle of the top two operands. This is the Microsoft Excel colon operator.

Operand Tokens: Constant

These operand tokens push a single constant operand onto the operand stack.

ptgMissArg: Missing Argument (Operand, ptg = 16h)

Indicates a missing argument to a Microsoft Excel function. For example, the second (missing) argument to the function DCOUNT(Database,,Criteria) would be stored as a ptgMissArg.

ptgStr: String Constant (Operand, ptg = 17h)

Indicates a string constant ptg followed by a string length field (00 to FFh) and the actual string.

Offset	Name	Size	Contents

0	cch	1	The length of the string
1	rgch	var	The string

ptgStr requires special handling when parsed expressions are scanned. For more information, see "Scanning a Parsed Expression" on page 450.

In BIFF8, the rgch contains a unicode string. For more information, see "Unicode Strings in BIFF8" on page 264.

ptgErr: Error Value (Operand, ptg = 1Ch)

This ptg is followed by the 1-byte error value (err). For a list of error values, see "BOOLERR" on page 290.

Offset	Name	Size	Contents

0	err	1	An error value

ptgBool: Boolean (Operand, ptg = 1Dh)

This ptg is followed by a byte that represents TRUE or FALSE.

Offset	Name	Size	Contents

0	f	1	= 1 for TRUE = 0 for FALSE

ptgInt: Integer (Operand, ptg = 1Eh)

This ptg is followed by a word that contains an unsigned integer.

Offset	Name	Size	Contents

0	w	2	An unsigned integer value

ptgNum: Number (Operand, ptg = 1Fh)

This ptg is followed by an 8-byte IEEE floating-point number.

Offset	Name	Size	Contents

0	num	8	An IEEE floating-point number

Operand Tokens

Operand tokens push operand values onto the operand stack. These values fall into one of three classes — reference class, value class, or array class — depending on what type of value the formula expects from the operand. The type of value is determined by the context of the operand when the formula is parsed by Microsoft Excel.

Reference Class

Some operands are required by context to evaluate to references. In this case, the term reference is a general term meaning one or more areas on a Microsoft Excel worksheet.

When the Microsoft Excel expression evaluator encounters a reference class operand, it pushes only the reference itself onto the operand stack; it doesn't de-reference it to return the underlying value or values. For example, the function CELL("width",B5) pushes the reference class operand ptgRef (24h) for the second argument. This function returns the column width of cell B5; therefore, only the reference to B5 is required, and there's no need to de-reference to the value stored in cell B5.

Value Class

This is the most common type of operand. Value class operands push a single de-referenced value onto the operand stack. For example, the formula =A1+1 pushes a value class operand ptgRefV (44h) for the cell reference A1.

Array Class

This operand pushes an array of values onto the operand stack. You can specify the values in an array constant or in a reference to cells. For example, the formula =SUM({1,2,3;4,5,6}) pushes an array class ptgArrayA (60h) to represent the arguments to the function.

ptg Values for Operand Tokens

The three classes of operand tokens are divided numerically, as shown in the following table.

Operand class	Ptg values

Reference	20h–3Fh
Value	40h–5Fh
Array	60h–7Fh

The arithmetic difference between ptg classes is 20h. This is the basis for forming the class variants of ptgs. Class variants of ptgs are formed from the reference class ptg, also known as the base ptg. To form the value class ptg from the base ptg, add 20h to the ptg and append V (for "value") to the ptg name. To form the array class ptg from the base ptg, add 40h to the ptg and append A (for "array") to the ptg name. These rules are summarized in the following table for a sample base ptg, ptgRef.

Class	Name	Ptg

Reference	ptgRef	24h
Value	ptgRefV	44h
Array	ptgRefA	64h

The following example is a suggested method for calculating the base ptg from any class variant.

     if (ptg & 40h)
          {
          /* Value class ptg. Set the 20h bit to 
             make it Reference class, then strip
             off the high-order bits. */
          ptgBase = (ptg | 20h) & 3Fh;
          }
     else
          {
          /* Reference or Array class ptg. The 20h bit 
             is already set, so just have to strip off
             the high-order bits. */
          ptgBase = ptg & 3Fh;
          }

A more efficient implementation would define a macro that computes the base ptg, as in the following example.

#define PtgBase(ptg) (((ptg & 0x40) ? (ptg | 0x20): ptg) & 0x3F)

Operand Tokens: Base

This section describes the operand tokens in their base form (also known as reference class operand tokens).

ptgArray: Array Constant (Operand, ptg = 20h)

Array constant followed by 7 reserved bytes.

The token value for ptgArray consists of the array dimensions and the array values. ptgArray differs from most other operand tokens in that the token value doesn't follow the token type. Instead, the token value is appended to the saved parsed expression, immediately following the last token. The format of the token value is shown in the following table.

Offset	Name	Size	Contents

0	ccol	1	The number of columns in the array constant
1	crw	2	The number of rows in the array constant
3	rgval	var	The array values

An array with 256 columns is stored with a ccol = 0, because a single byte cannot store the integer 256. This is unambiguous, because a 0-column array constant is meaningless.

The number of values in the array constant is equal to the product of the array dimensions, crw*ccol. Each value is either an 8-byte IEEE floating-point number or a string. The two formats for these values are shown in the following tables.

IEEE Floating-Point Number

Offset	Name	Size	Contents

0	grbit	1	= 01h
1	num	8	IEEE floating-point number

String

Offset	Name	Size	Contents

0	grbit	1	= 02h
1	cch	1	The length of the string
2	rgch	var	The string

If a formula contains more than one array constant, the token values for the array constants are appended to the saved parsed expression in order: first the values for the first array constant, then the values for the second array constant, and so on.

If a formula contains very long array constants, the FORMULA, ARRAY, or NAME record containing the parsed expression may overflow into CONTINUE records (to accommodate all of the array values). In such cases, an individual array value is never split between records, but record boundaries are established between adjacent array values.

The reference class ptgArray never appears in a Microsoft Excel formula; only the ptgArrayV and ptgArrayA classes are used.

ptgName: Name (Operand, ptg = 23h) — BIFF8

This ptg stores the index to a name. The ilbl field is a 1-based index to the table of NAME records in the workbook.

Offset	Name	Size	Contents

0	ixti	2
2	ilbl	2	Index to the NAME table
4	(reserved)	2	Reserved; must be 0 (zero)

ptgName: Name (Operand, ptg = 23h) — BIFF7 and earlier

This ptg stores the index to a name. The ilbl field is a 1-based index to the table of NAME records in the workbook.

Offset	Name	Size	Contents

0	ilbl	2	Index to the NAME table
2	(reserved)	12	Reserved; must be 0 (zero)

ptgRef: Cell Reference (Operand, ptg = 24h) — BIFF8

This ptg specifies a reference to a single cell. It's followed by references for the row and column that contain the cell. The column number is encoded.

Offset	Name	Size	Contents

0	rw	2	The column of the reference
2	grbitCol	2	(See the following table)

Only the low-order 14 bits of the grbitCol field store the column number of the reference. The 2 MSBs specify whether the row and column references are relative or absolute. The following table shows the bit structure of the grbitCol field.

Bits	Mask	Name	Contents

15	8000h	fRwRel	= 1 if the row offset is relative = 0 otherwise
14	4000h	fColRel	= 1 if the column offset is relative = 0 otherwise
13–0	3FFFh	col	The column number or column offset (0-based)

For example, cell C5 is row number 4, column number 2 (Microsoft Excel stores 0-based cell references). Therefore, the absolute reference $C$5 is stored in a ptgRef, as shown in the following file fragment.

     24 04 00 02 00

In this case, rw = 0004h and grbitCol = 0002h. Notice that bits 14 and 15 of grbitCol are both 0 (zero).

The relative reference C5 is stored in a ptgRef, as shown in the following file fragment.

     24 04 00 02 C0

In this case, where grbitCol = C004h and col = 02h, bits 14 and 15 of grbitCol are both 1.

Mixed references are stored in the same way, with appropriate coding in grbitCol.

ptgRef: Cell Reference (Operand, ptg = 24h) — BIFF7 and earlier

This ptg specifies a reference to a single cell. It's followed by references for the row and column that contain the cell. The row number is encoded.

Offset	Name	Size	Contents

0	grbitRw	2	(See the following table)
2	col	1	The column of the reference

Only the low-order 14 bits of the grbitRw field store the row number of the reference. The 2 MSBs specify whether the row and column references are relative or absolute. The following table shows the bit structure of the grbitRw field.

Bits	Mask	Name	Contents

15	8000h	fRwRel	= 1 if the row offset is relative = 0 otherwise
14	4000h	fColRel	= 1 if the column offset is relative = 0 otherwise
13–0	3FFFh	rw	The row number or row offset (0-based)

     24 04 00 02

In this case, grbitRw = 0004h and col = 02h. Notice that bits 14 and 15 of grbitRw are both 0 (zero).

The relative reference C5 is stored in a ptgRef, as shown in the following file fragment.

     24 04 C0 02

In this case, where grbitRw = C004h and col = 02h, bits 14 and 15 of grbitRw are both 1.

Mixed references are stored in the same way, with appropriate coding in grbitRw.

ptgArea: Area Reference (Operand, ptg = 25h) — BIFF8

This ptg specifies a reference to a rectangle (range) of cells. ptgArea is followed by 8 bytes that define the first row, last row, first column, and last column of the rectangle. The numbers of the first and last columns are encoded.

Offset	Name	Size	Contents

0	rwFirst	2	The first row of the reference
2	rwLast	2	The last row of the reference
4	grbitColFirst	2	(See the following table)
6	grbitColLast	2	(See the following table)

Only the low-order 14 bits of the grbitColFirst and grbitColLast fields store the column offsets of the reference. The 2 MSBs of each field specify whether the row and column offset are relative or absolute. The following table shows the bit structure of the grbitColFirst and grbitColLast fields.

Bits	Mask	Name	Contents

15	8000h	fRwRel	= 1 if the row offset is relative = 0 otherwise
14	4000h	fColRel	= 1 if the column offset is relative = 0 otherwise
13–0	3FFFh	col	The column number or column offset (0-based)

ptgArea: Area Reference (Operand, ptg = 25h) — BIFF7 and earlier

This ptg specifies a reference to a rectangle (range) of cells. ptgArea is followed by 6 bytes that define the first row, last row, first column, and last column of the rectangle. The numbers of the first and last rows are encoded.

Offset	Name	Size	Contents

0	grbitRwFirst	2	(See the following table)
2	grbitRwLast	2	(See the following table)
4	colFirst	1	The first column of the reference
5	colLast	1	The last column of the reference

Only the low-order 14 bits of the grbitRwFirst and grbitRwLast fields store the row offsets of the reference. The 2 MSBs of each field specify whether the row and column offset are relative or absolute. The following table shows the bit structure of the grbitRwFirst and grbitRwLast fields.

Bits	Mask	Name	Contents

15	8000h	fRwRel	= 1 if the row offset is relative = 0 otherwise
14	4000h	fColRel	= 1 if the column offset is relative = 0 otherwise
13–0	3FFFh	rw	The row number or row offset (0-based)

ptgMemArea: Constant Reference Subexpression (Operand, ptg = 26h)

This ptg is used to optimize reference expressions. A reference expression consists of operands — usually references to cells or areas — joined by reference operators (intersection, union, and range). Three examples of reference expressions are given in the following table.

Reference expression	Evaluates to

(A1,C3,D3:D5)	Two single cells and a 3x1 area
(A1:C3) (B2:D4)	A 2x2 area (the space character is the intersection operator)
(Name C3)	The smallest area that contains both C3 and all the cells referenced in Name (the space character is the intersection operator)

Many reference expressions evaluate to constant references. In the preceding examples, the first two expressions always evaluate to the same reference. The third example doesn't evaluate to a constant reference because the name's definition may change, which might cause the reference expression to evaluate differently.

When a reference expression evaluates to a constant reference, Microsoft Excel stores the constant reference in the parsed formula through a ptgMemArea token. This saves time during expression evaluation, because the constant part of the expression is pre-evaluated. This part of the expression is known as a reference subexpression.

The token value for ptgMemArea consists of two parts: the length of the reference subexpression, and the value of the reference subexpression. The length is stored immediately following the ptgMemArea, whereas the value is appended to the saved parsed expression, immediately following the last token.

The format of the length is shown in the following table.

Offset	Name	Size	Contents

0	(reserved)	4
4	cce	2	The length of the reference subexpression

Immediately following this part of the token value is the reference subexpression itself.

The rest of the token value (that is, the value of the reference subexpression) is appended to the parsed expression in the format shown in the following table.

Offset	Name	Size	Contents

0	cref	2	The number of rectangles to follow
2	rgref	var	An array of rectangles

Each rgref rectangle is 6 bytes long and contains the fields listed in the following table.

Offset	Name	Size	Contents

0	rwFirst	2	The first row
2	rwLast	2	The last row
4	colFirst	1	The first column
5	colLast	1	The last column

If a formula contains more than one ptgMemArea, the token values are appended to the saved parsed expression in order: first the values for the first ptgMemArea, then the values for the second ptgMemArea, and so on.

If a formula contains very long reference expressions, the BIFF record containing the parsed expression may be too long to fit in a single record. Microsoft Excel will use CONTINUE records to store long formulas. However, an individual rgref rectangle is never split between records; record boundaries occur between successive rectangles. For more information about the CONTINUE records, see "CONTINUE" on page 295.

ptgMemErr: Erroneous Constant Reference Subexpression (Operand, ptg = 27h)

This ptg is closely related to ptgMemArea. It's used for pre-evaluating reference subexpressions that don't evaluate to references.

For example, consider the formula =SUM(C:C 3:3), which is the sum of the intersection of column C and row 3 (the space between C:C and 3:3 is the intersection operator). The argument to the SUM function is a valid reference subexpression that generates a ptgMemArea for pre-evaluation. However, if you delete column C, the formula adjusts to =SUM(#REF! 3:3). In this case, the argument to SUM is still a constant reference subexpression, but it doesn't evaluate to a reference. Therefore, a ptgMemErr is used for pre-evaluation.

The token value consists of the error value and the length of the reference subexpression. Its format is shown in the following table.

Offset	Name	Size	Contents

0	(reserved)	4
4	cce	2	The length of the reference subexpression

The reference subexpression will contain a ptgRefErr or ptgAreaErr.

ptgRefErr: Deleted Cell Reference (Operand, ptg = 2Ah) — BIFF8

This ptg specifies a cell reference that was adjusted to #REF! as a result of worksheet editing (such as cutting, pasting, and deleting). The ptgRefErr is followed by4 unused bytes.

Offset	Name	Size	Contents

0	(reserved)	4

The original base type of the adjusted ptg is ptgRef or ptgRefN.

ptgRefErr: Deleted Cell Reference (Operand, ptg = 2Ah) — BIFF7 and earlier

This ptg specifies a cell reference that was adjusted to #REF! as a result of worksheet editing (such as cutting, pasting, and deleting). The ptgRefErr is followed by 3 unused bytes.

Offset	Name	Size	Contents

0	(reserved)	3

The original base type of the adjusted ptg is ptgRef or ptgRefN.

ptgAreaErr: Deleted Area Reference (Operand, ptg = 2Bh) — BIFF8

This ptg specifies an area reference that was adjusted to #REF! as a result of worksheet editing (such as cutting, pasting, and deleting). The ptgAreaErr is followed by 8 unused bytes.

Offset	Name	Size	Contents

0	(reserved)	8

The original base type of the adjusted ptg is ptgArea or ptgAreaN.

ptgAreaErr: Deleted Area Reference (Operand, ptg = 2Bh) — BIFF7 and earlier

This ptg specifies an area reference that was adjusted to #REF! as a result of worksheet editing (such as cutting, pasting, and deleting). The ptgAreaErr is followed by 6 unused bytes.

Offset	Name	Size	Contents

0	(reserved)	6

The original base type of the adjusted ptg is ptgArea or ptgAreaN.

ptgRefN: Cell Reference Within a Shared Formula (Operand, ptg = 2Ch) — BIFF8

Similar to its ptgRef counterpart, the ptgRefN specifies a reference to a single cell. It's followed by references for the row and column that contain the cell; the row number of the cell is encoded as bit fields.

In BIFF5 and later, ptgRefN is used only in shared formulas. In earlier versions of Microsoft Excel, ptgRefN was used in names.

Offset	Name	Size	Contents

0	rw	2	The row (or row offset) of the reference
2	grbitCol	2	(See the following table)

Bits	Mask	Name	Contents

15	8000h	fRwRel	= 1 if the row offset is relative = 0 otherwise
14	4000h	fColRel	= 1 if the column offset is relative = 0 otherwise
13–0	3FFFh	col	The column number or column offset (0-based)

The only difference between ptgRefN and ptgRef is in the way relative references are stored. Relative references in shared formulas are stored as offsets, not as row and column numbers (as in ptgRef). For more information, see "SHRFMLA" on page 386.

ptgRefN: Cell Reference Within a Shared Formula (Operand, ptg = 2Ch) — BIFF7 and earlier

In BIFF5 and later, ptgRefN is used only in shared formulas. In earlier versions of Microsoft Excel, ptgRefN was used in names.

Offset	Name	Size	Contents

0	grbitRw	2	(See the following table)
2	col	1	The column (or column offset) of the reference

Bits	Mask	Name	Contents

15	8000h	fRwRel	= 1 if the row offset is relative = 0 otherwise
14	4000h	fColRel	= 1 if the column offset is relative = 0 otherwise
13–0	3FFFh	rw	The row number or row offset (0-based)

ptgAreaN: Area Reference Within a Shared Formula (Operand, ptg = 2Dh) — BIFF8

The ptgAreaN token specifies a reference to a rectangle of cells. Both the first column and last column are encoded.

In BIFF5 and later, ptgAreaN is used only in shared formulas. In earlier versions, it was used in names.

Offset	Name	Size	Contents

0	rwFirst	2	The first row of the absolute reference or relative reference
2	rwLast	2	The last row of the absolute reference or relative reference
4	grbitColFirst	2	(See the following table)
6	grbitColLast	2	(See the following table)

Bits	Mask	Name	Contents

15	8000h	fRwRel	= 1 if the row offset is relative = 0 otherwise
14	4000h	fColRel	= 1 if the column offset is relative = 0 otherwise
13–0	3FFFh	col	The column number or column offset (0-based)

The only difference between ptgAreaN and ptgArea is in the way relative references are stored.

ptgAreaN: Area Reference Within a Shared Formula (Operand, ptg = 2Dh) — BIFF7 and earlier

The ptgAreaN token specifies a reference to a rectangle of cells. Both the first row and last row are stored as bit fields.

In BIFF5 and later, ptgAreaN is used only in shared formulas. In earlier versions, it was used in names.

Offset	Name	Size	Contents

0	grbitRwFirst	2	The first row of the absolute reference or relative reference offset bit fields
2	grbitRwLast	2	The last row of the absolute reference or relative reference offset bit fields
4	colFirst	1	The first column of the reference or column offset
5	colLast	1	The last column of the reference or column offset

Bits	Mask	Name	Contents

15	8000h	fRwRel	= 1 if the row offset is relative = 0 otherwise
14	4000h	fColRel	= 1 if the column offset is relative = 0 otherwise
13–0	3FFFh	rw	The row number or row offset (0-based)

The only difference between ptgAreaN and ptgArea is in the way relative references are stored.

ptgNameX: Name or External Name (Operand, ptg = 39h) — BIFF8

This ptg stores the index to a name.

Offset	Name	Size	Contents

0	ixti	2	Index into the EXTERNSHEET record
2	ilbl	2	The index to the NAME or EXTERNNAME table (1-based)
4	(reserved)	2	Reserved; must be 0 (zero)

ptgNameX: Name or External Name (Operand, ptg = 39h) — BIFF7 and earlier

This ptg stores the index to a name. If the name is in the current workbook (in which case ixals is negative), the ilbl field is a 1-based index to the table of NAME records. If the name is in another workbook (that is, if it's an external name), the ilbl field is a 1-based index to the table of EXTERNNAME records.

Offset	Name	Size	Contents

0	ixals	2	The index to the EXTERNSHEET records. If ixals is negative (for example, FFFFh), the name is in the current workbook.
2	(reserved)	8
10	ilbl	2	The index to the NAME or EXTERNNAME table (1-based).
12	(reserved)	12

ptgRef3d: 3-D Cell Reference (Operand, ptg = 3Ah) — BIFF8

This ptg stores a 3-D cell reference (for example, Sheet1:Sheet3!$A$1).

Offset	Name	Size	Contents

0	ixti	2	Index into the EXTERNSHEET record.
2	rw	2	The row of the reference, or the row offset.
4	grbitCol	2	(See the following table.)

Only the low-order 8 bits of the grbitCol field store the column number of the reference. The 2 MSBs specify whether the row and column references are relative or absolute. The following table shows the bit structure of the grbitCol field.

Bits	Mask	Name	Contents

15	8000h	fRwRel	= 1 if the row offset is relative = 0 otherwise
14	4000h	fColRel	= 1 if the column offset is relative = 0 otherwise
13 – 8	3F00h	(reserved)
7 – 0	00FFh	col	The column number or column offset (0-based)

ptgRef3d: 3-D Cell Reference (Operand, ptg = 3Ah) — BIFF7 and earlier

This ptg stores a 3-D cell reference (for example, Sheet1:Sheet3!$A$1). If the reference is to another workbook (in which case ixals is positive), itabFirst isn't used (it will be 0000h), and itabLast is the ixals for the last sheet in the 3-D reference. If either itabFirst or itabLast is equal to FFFFh, that sheet is a deleted sheet.

Offset	Name	Size	Contents

0	ixals	2	The index to the EXTERNSHEET records. If ixals is negative (for example, FFFFh), the reference is in the current workbook.
2	(reserved)	8
10	itabFirst	2	The index to the first sheet in the 3-D reference (0-based); see the text.
12	itabLast	2	The index to the last sheet in the 3-D reference (0-based); see the text.
14	grbitRw	2	(See the following table.)
16	col	1	The column of the reference, or the column offset.

Bits	Mask	Name	Contents

15	8000h	fRwRel	= 1 if the row offset is relative = 0 otherwise
14	4000h	fColRel	= 1 if the column offset is relative = 0 otherwise
13–0	3FFFh	rw	The row number or row offset (0-based)

ptgArea3d: 3-D Area Reference (Operand, ptg = 3Bh) — BIFF8

This ptg stores a 3-D area reference (for example, Sheet1:Sheet3!A1:E9).

Offset	Name	Size	Contents

0	ixti	2	Index into the EXTERNSHEET record.
2	rwFirst	2	The first row in the area.
4	rwLast	2	The last row in the area.
6	grbitColFirst	2	The first column of the reference, or the column offset; see following table.
8	grbitColLast	2	The last column of the reference, or the column offset; see following table.

Only the low-order 8 bits of the grbitColFirst and grbitColLast fields store the column number of the reference. The 2 MSBs specify whether the row and column references are relative or absolute. The following table shows the bit structure of the grbitCol field.

Bits	Mask	Name	Contents

15	8000h	fRwRel	= 1 if the row offset is relative = 0 otherwise
14	4000h	fColRel	= 1 if the column offset is relative = 0 otherwise
13 – 8	3F00h	(reserved)
7 – 0	00FFh	col	The column number or column offset (0-based)

ptgArea3d: 3-D Area Reference (Operand, ptg = 3Bh) — BIFF7 and earlier

This ptg stores a 3-D area reference (for example, Sheet1:Sheet3!A1:E9).

Offset	Name	Size	Contents

0	ixals	2	The index to the EXTERNSHEET records. If ixals is negative (for example, FFFFh), the reference is on another sheet in the same workbook.
2	(reserved)	8
10	itabFirst	2	The index to the first sheet in the 3-D reference (0-based).
12	itabLast	2	The index to the last sheet in the 3-D reference (0-based).
14	grbitRwFirst	2	The first row in the area; see the following table.
16	grbitRwLast	2	The last row in the area; see the following table.
18	colFirst	1	The first column of the reference, or the column offset.
19	colLast	1	The last column of the reference, or the column offset.

Bits	Mask	Name	Contents

15	8000h	fRwRel	= 1 if the row offset is relative = 0 otherwise
14	4000h	fColRel	= 1 if the column offset is relative = 0 otherwise
13–0	3FFFh	rw	The row number or row offset (0-based)

ptgRefErr3d: Deleted 3-D Cell Reference (Operand, ptg = 3Ch)

This ptg stores a 3-D cell reference that was adjusted to #REF! as a result of worksheet editing (such as cutting, pasting, and deleting). The ptgRefErr3d is identical to ptgRef3d.

ptgAreaErr3d: Deleted 3-D Area Reference (Operand, ptg = 3Dh)

This ptg stores a 3-D area reference that was adjusted to #REF! as a result of worksheet editing (such as cutting, pasting, and deleting). The ptgAreaErr3d is identical to ptgArea3d.

Control Tokens

ptgExp: Array Formula or Shared Formula (ptg = 01h)

This ptg indicates an array formula or a shared formula. When ptgExp occurs in a formula, it's the only token in the formula. This indicates that the cell containing the formula is part of an array or part of a shared formula. The actual formula is found in an ARRAY record.

The token value for ptgExp consists of the row and column of the upper-left corner of the array formula.

Offset	Name	Size	Contents

0	rwFirst	2	The row number of the upper-left corner
2	colFirst	2	The column number of the upper-left corner

ptgTbl: Data Table (ptg = 02h)

This ptg indicates a data table. When ptgTbl occurs in a formula, it's the only token in the formula. This indicates that the cell containing the formula is an interior cell in a data table; the table description is found in a TABLE record. Rows and columns that contain input values to be substituted in the table don't contain ptgTbl.

The token value for ptgTbl consists of the row and column of the upper-left corner of the table's interior.

Offset	Name	Size	Contents

0	rwFirst	2	The row number of the upper-left corner
2	colFirst	2	The column number of the upper-left corner

ptgParen: Parenthesis (ptg = 15h)

This ptg is used only when Microsoft Excel unparses a parsed expression (for example, to display it in the formula bar). This ptg isn't used to evaluate parsed expressions. It indicates that the previous token in the parsed expression should be in parentheses. If the previous token is an operand, only that operand is in parentheses. If the previous token is an operator, the operator and all of its operands are in parentheses.

For example, the formula =1+(2) is stored as follows:

     ptgInt     0001h
     ptgInt     0002h
     ptgParen
     ptgAdd

In this case, only the integer operand 2 is in parentheses.

The formula =(1+2) is stored as follows:

     ptgInt     0001h
     ptgInt     0002h
     ptgAdd
     ptgParen

In this example, the parenthesized quantity consists of the ptgAdd operator and both of its operands.

ptgAttr: Special Attribute (ptg = 19h)

This ptg is used for several different purposes. In all cases, the token value consists of a group of flag bits and a data word.

BIFF3 and BIFF4

Offset	Name	Size	Contents

0	grbit	1	Option flags
1	w	2	Data word

BIFF4 when bifFAttrSpace = 1

Offset	Name	Size	Contents

0	grbit	1	Option flags
1	bAttrSpace	1	Spacing attribute
2	bSpace	1	Number of spaces

The grbit field contains the option flags listed in the following table.

Bits	Mask	Name	Contents

0	01h	bitFAttrSemi	= 1 if the formula contains a volatile function
1	02h	bitFAttrIf	= 1 to implement an optimized IF function
2	04h	bitFAttrChoose	= 1 to implement an optimized CHOOSE function
3	08h	bitFAttrGoto	= 1 to jump to another location within the parsed expression
4	10h	bitFAttrSum	= 1 to implement an optimized SUM function
5	20h	bitFAttrBaxcel	= 1 if the formula is a BASIC-style assignment statement
6	40h	bifFAttrSpace	= 1 if the macro formula contains spaces after the equal sign (BIFF3 and BIFF4 only)
7	80	(Unused)

ptgAttr requires special handling when parsed expressions are scanned. For more information, see "Scanning a Parsed Expression" on page 350.

bitFAttrSemi

Set to 1 if the formula contains a volatile function — that is, a function that's calculated in every recalculation. If ptgAttr is used to indicate a volatile function, it must be the first token in the parsed expression. If grbit = bitFAttrSemi, then the b (or w) field is don't-care.

bitFAttrIf

Indicates an optimized IF function. An IF function contains three parts: a condition, a TRUE subexpression, and a FALSE subexpression. The syntax of an associated Microsoft Excel formula would be IF(condition, TRUE subexpression, FALSE subexpression).

bitFAttrIf immediately follows the condition portion of the parsed expression. The b (or w) field specifies the offset to the FALSE subexpression; the TRUE subexpression is found immediately following the ptgAttr token. At the end of the TRUE subexpression, there's a bitFAttrGoto token that causes a jump to beyond the FALSE subexpression. In this way, Microsoft Excel evaluates only the correct subexpression instead of evaluating both of them and discarding the wrong one.

The FALSE subexpression is optional in Microsoft Excel. If it's missing, the b (or w) field specifies an offset to beyond the TRUE subexpression.

bitFAttrChoose

Indicates an optimized CHOOSE function. The cCases (or wCases) field specifies the number of cases in the CHOOSE function. It's followed by an array of word offsets to those cases. The format of this complex token value is shown in the following table.

Offset	Name	Size	Contents

0	grbit	1	bitFAttrChoose (04h).
1	wCases	2	The number of cases in the CHOOSE function.
3	rgw	var	A sequence of word offsets to the CHOOSE cases. The number of words in this field is equal to wCases + 1.

bitFAttrGoto

Instructs the expression evaluator to skip part of the parsed expression during evaluation. The b (or w) field specifies the number of bytes (or words) to skip, minus 1.

bitFAttrSum

Indicates an optimized SUM function (a SUM that has a single argument). For example, the sum of the cells in a 3-D reference — which has the formula =SUM(Sheet1:Sheet3!C11) — generates a ptgAttr with bitFAttrSum TRUE. The b (or w) field is don't-care.

bifFAttrSpace

Indicates that a formula (macro sheet or worksheet) contains spaces or carriage returns. Microsoft Excel retains spaces and returns in macro sheet and worksheet formulas (in version 3.0 and earlier, spaces and returns would have been eliminated when the formula was parsed). The bAttrSpace field contains an attribute code, and the bSpace field contains the number of spaces or returns. The attribute codes are listed in the following table.

Attribute	Value

bitFSpace	00h
bitFEnter	01h
bitFPreSpace	02h
bitFPreEnter	03h
bitFPostSpace	04h
bitFPostEnter	05h
bitFPreFmlaSpace	06h

The bitFSpace and bitFEnter attributes indicate that bSpace contains the number of spaces or returns before the next ptg in the formula.

The bitFPreSpace, bitFPreEnter, bitFPostSpace, and bitFPostEnter attributes occur with a ptgParen. Because one ptgParen represents two matched parentheses, the ptgAttr must encode the position of the space or return if it occurs before either parenthesis. For example, the ptgs that express the worksheet formula = ("spaces" ), which contains four spaces before the opening and closing parentheses, would appear in a formula record as shown in the following table.

Hex dump	Ptg type	Decodes to

17 06 73 70 61 63 65 73	ptgStr	The string "spaces" (operand)
19 40 02 04	ptgAttr	Four spaces before the opening parenthesis
19 40 04 04	ptgAttr	Four spaces after the closing parenthesis
15	ptgParen	The enclose operand (ptgStr) in parentheses

The bitFPreFmlaSpace attribute provides compatibility with BIFF3, where spaces can occur only after the equal sign (before the formula) in macro formulas. If the spaces in a BIFF5/BIFF7 formula are also acceptable in a BIFF3 formula, Microsoft Excel writes a bitFPreFmlaSpace attribute to indicate as much.

ptgMemNoMem: Incomplete Constant Reference Subexpression (ptg = 28h)

This ptg is closely related to ptgMemArea. It's used to indicate a constant reference subexpression that couldn't be pre-evaluated because of insufficient memory.

The token value consists of the length of the reference subexpression, as shown in the following table.

Offset	Name	Size	Contents

0	(reserved)	4
4	cce	2	The length of the reference subexpression

ptgMemFunc: Variable Reference Subexpression (ptg = 29h)

This ptg indicates a reference subexpression that doesn't evaluate to a constant reference. Any reference subexpression that contains one or more of the following items will generate a ptgMemFunc.

Subexpression contains	Example

A function	OFFSET(ACTIVE.CELL(),1,1):$C$2
A name	INDEX(first_cell:$D$2,1,1)
An external reference	SALES.XLS!$A$1:SALES.XLS!$C$3

The token value consists of the length of the reference subexpression.

Offset	Name	Size	Contents

0	cce	2	The length of the reference subexpression

ptgMemAreaN: Reference Subexpression Within a Name (ptg = 2Eh)

This ptg contains a constant reference subexpression within a name definition. Unlike ptgMemArea, ptgMemAreaN isn't used to pre-evaluate the reference subexpression.

The token value consists of the length of the reference subexpression.

Offset	Name	Size	Contents

0	cce	2	The length of the reference subexpression

ptgMemNoMemN: Incomplete Reference Subexpression Within a Name (ptg = 2Fh)

This ptg is closely related to ptgMemAreaN. It's used to indicate a constant reference subexpression within a name that couldn't be evaluated because of insufficient memory.

The token value consists of the length of the reference subexpression, as shown in the following table.

Offset	Name	Size	Contents

0	cce	2	The length of the reference subexpression

Function Operators

The following paragraphs describe the function operator ptgs. All of these operators pop arguments from the operand stack, compute a function, and then push the result back onto the operand stack. The number of operands popped from the stack is equal to the number of arguments passed to the Microsoft Excel function. Some Microsoft Excel functions always require a fixed number of arguments, whereas others accept a variable number of arguments. The SUM function, for example, accepts a variable number of arguments.

Although they're operators, function tokens also behave like operands in that they can occur in any of the three ptg classes: reference, value, or array.

ptgFunc: Function, Fixed Number of Arguments (Operator, ptg = 21h)

This ptg indicates a Microsoft Excel function with a fixed number of arguments. The ptgFunc is followed by the index to the function table.

Offset	Name	Size	Contents

0	iftab	2	The index to the function table; the iftab reference is contained in the file xlcall.h, which is contained on the CD that accompanied this book.

ptgFuncVar: Function, Variable Number of Arguments (Operator, ptg = 22h)

This ptg indicates a Microsoft Excel function with a variable number of arguments. The ptgFuncVar is followed by the number of arguments (1 byte) and then the index to the function table (2 bytes).

Offset	Bits	Mask	Name	Contents

0	6–0	7Fh	cargs	The number of arguments to the function.
	7	80h	fPrompt	= 1, function prompts the user (macro functions that end with a question mark).
1	14–0	7FFFh	iftab	The index to the function table; the iftab reference is contained in the file xlcall.h, which is contained on the CD that accompanied this book.
	15	8000h	fCE	The function is a command-equivalent.