Dec 292017
 
Binary File Spec for MS EXCEL.
File BIFF.ZIP from The Programmer’s Corner in
Category Tutorials + Patches
Binary File Spec for MS EXCEL.
File Name File Size Zip Size Zip Type
BIFF.DOC 109647 27038 deflated

Download File BIFF.ZIP Here

Contents of the BIFF.DOC file


MICROSOFT EXCEL BINARY FILE FORMAT
----------------------------------

Mark O'Brien
Microsoft Corporation
18-Feb-1988

Table of Contents
-----------------
Introduction
General BIFF Record Format
Rows and Columns Within BIFF
Cell Table - Concepts
Cell Records
Record Types
Cell Attributes
Order of Records
Finding Values From BIFF Files
Excel Formulas
Expression Evaluation
Unary Operators
Binary Operators
Operand Tokens - Constant
Operand Tokens - Classes
Operand Tokens - Base
Control Tokens
Function Operators
Reserved Ptg's
Scanning a Parsed Expression
Excel Function Table
Command Equivalent Function Table
List of Ptg's

Introduction
------------
BIFF (BInary File Format) is the file format in which Excel
documents are saved on disk. A BIFF file is a complete
description of an Excel document.

BIFF was designed to satisfy the following goals:
- Easy to understand and use
- Easy to expand the file format for future needs
- Files should save and load quickly

BIFF files consist of sequences of variable-length records.
There are many different types of BIFF records. For example,
one record type describes a formula entered into a cell;
one describes the size and location of a window into a document;
another describes a picture format.

General BIFF Record Format
--------------------------
Although different BIFF record types contain different
information, every record follows the same basic format:
- Record type.This tells us what kind of data
the record contains (e.g. a formula, a window, or
a picture format).
- Record length. This tells us how long the data
contained in the record is. The length of a record
depends on the type of data it contains. For example,
a window record may always be the same length,
containing just the size and location of a window, while
a formula record varies in length, depending on the
length of the formula itself.
- Record data.This is the variable-length portion of
the record containing the actual data.

All BIFF records are in the following format:
OffsetLengthContents
--------------------
0wordrecord type
2wordlength of data portion
4variesdata portion of record

The data portion of a BIFF record must be no longer than 2080
bytes long. Thus, counting the record type and length fields,
the maximum length of a BIFF record is 2084 bytes.

Within this document, all numbers are decimal numbers unless they are
preceded by "0x", in which case they are hexadecimal.

Some portions of BIFF records are marked as RESERVED. These portions
are unavailable for application use. If they are marked "RESERVED -
must be zero", then a BIFF-related application should ensure that
their contents are always filled with zeros. If they are marked
simply RESERVED, then they do not need to be set to any particular
value.

Rows and Columns Within BIFF
----------------------------
Within BIFF files, rows and columns are always stored zero-based,
rather than one-based as they appear on the screen. For example,
cell A1 is stored as row 0, column 0; cell B3 is row 2, column 1.

Cell Table - Concepts
---------------------
Microsoft Excel uses a sparse cell table to reduce memory
requirements as much as possible. Cells that don't have values or
formulas in them, have default format attributes, and are not
referenced in any other formulas, are undefined cells and do not
have any memory allocated for them.

For example, if a worksheet has a value in cell A3 and the formula
=A3+A4 in cell B10, then the only defined cells on the worksheet are
A3, A4, and B10. No other cells need to exist. Entire rows can be
undefined, if they have no defined cells in them. In this case,
only rows 3, 4, and 10 are defined.

Cell Records
------------
The term "cell record" refers to a BIFF record that defines a
cell on an Excel document. A cell record is one of the following
types:
BLANK
INTEGER
NUMBER
LABEL
BOOLERR
FORMULA

The following records can occur in conjunction with cell records:
CONTINUE
ARRAY
TABLE
TABLE2

Record Types
------------
Here are the record types defined in BIFF. The record type
and record length fields have been omitted from the descriptions,
but they are present in every record.

BOF record - beginning of file (type = 9)
OffsetNameSizeContents
----------------------
4vers2version number
6dt2document type
0x10 = worksheet
0x40 = macro sheet

Description
The BOF must be the first record in every BIFF file. The
version number for Excel documents is currently 2; Microsoft
may change this number in the future, as BIFF is modified for
future needs.

Currently defined version numbers are:
ValueNameMeaning
----------------
2versExcelExcel document
3versMPMultiplan document

All other version numbers are reserved for future use by
Microsoft.

The high byte of the version number contains flag bits.
Current flag values are:
MaskNameMeaning
---------------
0x0100bitFMP=1 if the BIFF file is a
Multiplan document
0xFE00RESERVED for future use -
must be zeros

The dt field specifies whether the document is a worksheet or
a macro sheet.Chart BIFF files are different and are not
described in this document.

FILEPASS record - file password key (type = 47)
Description
The FILEPASS record is used for an Excel document which was
saved with a password in the File Save As command. If this
record appears, it must directly follow the BOF record. All
subsequent BIFF records will be encrypted, so you cannot read
a password-protected BIFF file.

Note that this record specifies a file password, as opposed
to the PASSWORD record, which specifies a document password.

INDEX record - ROW record index (type = 11)
OffsetNameSizeContents
----------------------
4ibRtName4absolute file position of the
first NAME record
8rwMic2first row that exists
on the document
10rwMac2last row that exists
on the document, plus 1
12rgibRwvararray of absolute file positions
of the blocks of ROW records

Description
The INDEX record is used to optimize searching through a file
for a particular cell or name.This record is optional; if
it occurs, it must occur directly after the document's
FILEPASS record. If the document has no FILEPASS record,
then the INDEX record must occur directly after the BOF record.

The ibRtName field gives the absolute file offset (0 =
beginning of file) of the first NAME record. rwMic and rwMac
are the range of defined rows in the document.The rgibRw
field is an array of 4-byte absolute file offsets to the
document's ROW records.

Excel always writes an INDEX record when it saves a BIFF
file. If you are writing a BIFF file, you should probably not
attempt to write an INDEX record.

The INDEX record is explained more fully in the section
"Finding Values From BIFF Files."

CALCCOUNT record - iteration count (type = 12)
OffsetNameSizeContents
----------------------
4cIter2iteration count

Description
The CALCCOUNT record specifies the iteration count
as set in the Options Calculation command.

CALCMODE record - calculation mode (type = 13)
OffsetNameSizeContents
----------------------
4fAutoRecalc2calculation mode
=0 for manual
=1 for automatic
=-1 for automatic, no tables
Description
The CALCMODE record specifies the calculation mode
as set in the Options Calculation command.

PRECISION record - precision (type = 14)
OffsetNameSizeContents
----------------------
4fFullPrec2document precision
=1 for full precision
=0 for precision as displayed

Description
The PRECISION record specifies the precision
as set in the Options Calculation command.

REFMODE record - reference mode (type = 15)
OffsetNameSizeContents
----------------------
4fRefA12reference mode
=1 for A1 mode
=0 for R1C1 mode

Description
The REFMODE record specifies the reference mode
as set in the Options Desktop command.

DELTA record - maximum iteration change (type = 16)
OffsetNameSizeContents
----------------------
4numDelta8maximum change for iteration

Description
The DELTA record specifies the maximum change for an
iterative model, as set in the Options Calculation command.
The number is in 8-byte IEEE floating point format.

ITERATION record - iteration flag (type = 17)
OffsetNameSizeContents
----------------------
4fIter2iteration flag
=1 for iteration on
=0 for iteration off

Description
The ITERATION record specifes the state of iteration
as set in the Options Calculation command.

1904 record - date system (type = 34)
OffsetNameSizeContents

----------------------
4f19042=1 if the document uses the 1904
date system
=0 otherwise

Description
The 1904 record specifies the date system used in an Excel
document, as specified in the Options Calculation command.

BACKUP record - file backup option (type = 64)
OffsetNameSizeContents
----------------------
4fBackupFile2=1 if Excel should save a backup
version of the file when it is
saved
=0 otherwise

Description
The BACKUP record specifies whether or not Excel should save
backup versions of a BIFF file, as specified in the "Create
Backup File" checkbox in the Save As dialog box.

PRINT ROW HEADERS record - print row headers flag (type = 42)
OffsetNameSizeContents
----------------------
4fPrintRwCol2=1 if we should print row and
column headers when printing
the document
=0 otherwise

Description
The PRINT ROW HEADERS record controls whether or not Excel
prints row and column headers when printing the document.

PRINT GRIDLINES record - print gridlines flag (type = 43)
OffsetNameSizeContents
----------------------
4fPrintGrid2=1 if we should print gridlines
when printing the document
=0 otherwise

Description
The PRINT GRIDLINES record controls whether or not Excel
prints gridlines when printing the document.

HORIZONTAL PAGE BREAKS record - row page breaks (type = 27)
OffsetNameSizeContents
----------------------
4cbrk2number of page breaks
6rgrwvararray of rows

Description
The HORIZONTAL PAGE BREAKS record contains a list of explicit
row page breaks. The cbrk field contains the number of page
breaks. rgrw is an array of 2-byte integers specifying
rows. Excel sets a page break before each row in the list.
The rows must be sorted in increasing order.

VERTICAL PAGE BREAKS record - column page breaks (type = 26)
OffsetNameSizeContents
----------------------
4cbrk2number of page breaks
6rgcolvararray of columns

Description
The VERTICAL PAGE BREAKS record contains a list of explicit
column page breaks. The cbrk field contains the number of
page breaks. rgcol is an array of 2-byte integers specifying
columns. Excel sets a page break before each column in the
list. The columns must be sorted in increasing order.

DEFAULT ROW HEIGHT record - default row height (type = 37)
OffsetNameSizeContents
----------------------
4miyRwGhost2default row height

Description
The DEFAULT ROW HEIGHT record specifies the height of all
undefined rows in the document. The miyRwGhost field
contains the row height in units of 1/20 of a point.
This record does not affect the row heights of any rows
that are explicitly defined.

FONT record - document font (type = 49)
OffsetNameSizeContents
----------------------
4dy2height of the font
6grbit2font attributes
8cch1length of font name
9rgchvarthe font name

Description
The FONT record describes an entry in the Excel document's
font table. There are up to four different fonts on an Excel
document, numbered 0 to 3. FONT records are read into the
font table in the order in which they are encountered in the
BIFF file.

The dy field gives the height of the font in units of 1/20
of a point. grbit contains the font attributes as follows:

OffsetBitsMaskNameContents
--------------------------
07-00xFFRESERVED - must be zeros
17-40xF0RESERVED - must be zeros
30x08fStrikeout=1 if the font is struck
out
20x04fUnderline=1 if the font is
underlined
10x02fItalic =1 if the font is italic
00x01fBold=1 if the font is bold

cch and rgch contain the font's face name.

FONT2 record - more font information (type = 50)
Description
The FONT2 record contains system-specific information about
the font defined in the previous FONT record. This record is
optional. If you are writing a BIFF file, do not write a
FONT2 record.

HEADER record - print header string (type = 20)
OffsetNameSizeContents
----------------------
4cch1length of string
5rgchvarthe string

Description
The HEADER record specifies a print header string for a
document. This string appears at the top of every page
when the document is printed.

FOOTER record - print footer string (type = 21)
OffsetNameSizeContents
----------------------
4cch1length of string
5rgchvarthe string

Description
The FOOTER record specifies a print footer string for a
document. This string appears at the bottom of every
page when the document is printed.

LEFT MARGIN record - left print margin (type = 38)
OffsetNameSizeContents
----------------------
4num8left margin

Description
The LEFT MARGIN record specifies the left margin in inches
when a document is printed. The num field is in 8-byte IEEE
floating point format.

RIGHT MARGIN record - (type = 39)
OffsetNameSizeContents
----------------------
4num8right margin

Description
The RIGHT MARGIN record specifies the right margin in inches
when a document is printed. The num field is in 8-byte IEEE
floating point format.

TOP MARGIN record - (type = 40)
OffsetNameSizeContents
----------------------
4num8top margin

Description
The TOP MARGIN record specifies the top margin in inches when
a document is printed.The num field is in 8-byte IEEE
floating point format.

BOTTOM MARGIN record - (type = 41)
OffsetNameSizeContents
----------------------
4num8bottom margin

Description
The BOTTOM MARGIN record specifies the bottom margin in
inches when a document is printed. The num field is in 8-
byte IEEE floating point format.

COLWIDTH record - column width (type = 36)
OffsetNameSizeContents
----------------------
4colFirst1first column in the range
5colLast 1last column in the range
6dx2column width

Description
The COLWIDTH record sets the column width for a range of
columns specified by colFirst and colLast. The dx field is
an unsigned integer specifying the column width in units of
1/256 of a character.

EXTERNCOUNT record - count of externally referenced documents (type = 22)
OffsetNameSizeContents
----------------------
4cxals2number of externally referenced
documents

Description
The EXTERNCOUNT record specifies the number of documents that
are referenced externally from an Excel document.

Both external references and Dynamic Data Exchange (DDE)
references are counted here. For external references, only
the supporting sheet name counts. For DDE references, the
application-topic pair counts.

For example, suppose a worksheet contains the following
formulas:
=SALES.XLS!Gross-SALES.XLS!Profits
=Signal|System!Formats
=Signal|StockInfo!IBM

This worksheet would have an EXTERNCOUNT of three: SALES.XLS,
Signal|System, and Signal|StockInfo.

EXTERNSHEET record - externally referenced document (type = 23)
OffsetNameSizeContents
----------------------
4cch1length of document name
5rgchvardocument name

Description
The EXTERNSHEET record specifes a document which is
referenced externally from an Excel document. There must be
as many EXTERNSHEET records in a BIFF file as were specified
in the EXTERNCOUNT record. The order of EXTERNSHEET records
in a BIFF file is important and should not be changed.

The document that is externally referenced is called the
supporting document. The document which refers to it is
called the dependent document.

The cch field gives the length of the supporting document
name, which is contained in the rgch field. Whenever
possible, document names are encoded to make BIFF files
compatible with file systems other than DOS. Encoded
document names are identified by the first character of the
rgch field. The following special characters are recognized:

NameValueMeaning
----------------
chEmpty 0empty sheetname
chEncode1encoded pathname
chSelf2self-referential external
reference

chEmpty is used to store an external reference to the empty
sheet, as in the formula =!$A$1. chSelf is used to store an
external reference where the dependent and supporting
documents are the same, for example a worksheet SALES.XLS
which contains the formula =SALES.XLS!$A$1.

chEncode is used when the DOS file name of the supporting
document has been translated to a less system-dependent name.
The following special characters are recognized in an encoded
document name:

NameValueRelated DOS keys
-------------------------
chVolume1:
chSameVolume2none
chDownDir3.\
chUpDir 4..\

The chVolume key is used to specify a DOS drive letter in a
document name.It is followed by the drive letter. This
replaces the DOS-specific ':' character, as in
=C:SALES.XLS!Gross.

The chSameVolume key is used when the drive letter was
omitted, to indicate that the supporting document is on the
same DOS drive as the dependent document, as in
=SALES.XLS!Gross.

The chDownDir key is used to go down a directory level. It
is followed by the subdirectory name. This replaces the
implicit DOS-specific sequence ".\", meaning subdirectory of
the current directory.An example of such an external
reference is =AUGUST\SALES.XLS!Gross.

The chUpDir key is used to go up a directory level. It
replaces the DOS-specific sequence "..\", meaning the parent
directory of the current directory.

DDE references are encoded differently. Only one translation
is ever performed on a DDE reference, on the '|' character:

NameValueRelated DOS keys
-------------------------
chDde3|

EXTERNNAME record - externally referenced name (type = 35)
OffsetNameSizeContents
----------------------
4cch1length of the name
5rgchvarthe name

Description
The EXTERNNAME record specifes a name which is referenced
externally from an Excel document. All EXTERNNAME records
associated with a supporting document must directly follow
the EXTERNSHEET record for the document.

The order of EXTERNNAME records in a BIFF file is important
and should not be changed.

An externally referenced name is one of the following:
- A worksheet or macro sheet name in an external
reference. In the formula =SALES.XLS!Gross, the
name "Gross" is an externally referenced name.
- A DDE topic.In the formula =Signal|StockInfo!IBM,
the topic "IBM" is an externally referenced name.

When the externally referenced name is a DDE topic, Excel may
append the most recent values for the topic to the EXTERNNAME
record. The values are written in the same format as array
constant values in parsed expressions.See the explanation
of "ptgArray" in the "Operand Tokens - Base" section for a
full description of this format.

If there are many values, the EXTERNNAME record may
become so long that it must be split into multiple records.
In this case, the EXTERNNAME record will be followed by one
or more CONTINUE records.

FORMAT record - cell format (type = 30)
OffsetNameSizeContents
----------------------
4cch1length of format string
5rgchvarpicture format string

Description
The FORMAT record describes a picture format on the document.

All the FORMAT records should appear together in a BIFF file.
The order of FORMAT records in an existing BIFF file is
important and should not be changed. You can add new formats
to a file, but they should be added at the end of the FORMAT
list.

NAME record - user-defined name (type = 24)
OffsetNameSizeContents
----------------------
4grbit1name attributes
5grbitPli1name attributes
6chKey1keyboard shortcut
7cch1length of the name text
8cce1length of the name's definition
9rgchvartext of the name
varrgcevarparsed expression for the name's
definition
varcceDup1length of the name's definition
(this is a duplicate of the cce
field)

Description
The NAME record describes a user-defined name on the
document. The cch field contains the length the name text;
the text itself is in rgch. cce is the length of the name
definition, and rgce contains the definition. The location
of rgce within the record depends on the length of the name
text. Following rgce, the length of the name definition
appears again.

The name definition is stored in Excel's internal compressed
format. See the section "Excel Formulas" for an explanation.

The grbit field contains bit attributes of the name:

BitsMaskNameContents
--------------------
7-30xF8RESERVED - must be zeros
20x04fCalcExp=1 if the name contains a
complex function
=0 otherwise
10x02fProc=1 for a Function or
Command name
=0 otherwise
00x01RESERVED - must be zero

The fCalcExp bit is set if the name definition contains one
or more of the following:
- A function that returns an array (e.g. TREND,
MINVERSE)
- The ROW or COLUMN function
- A user-defined function

The fProc bit is set if the name is a Function or Command
name on a macro sheet.

grbitPli and chKey are meaningful only when the fProc bit is
set in the grbit field. grbitPli contains bit attributes for
Function or Command names:

BitsMaskNameContents
--------------------
7-20xFCRESERVED - must be zeros
10x02fRun=1 for Command names
00x01fFunc=1 for Function names

chKey is the keyboard shortcut for a Command name. If the
name is not a command name or has no keyboard shortcut, then
chKey will be 0.

All the NAME records should appear together in a BIFF file.
The order of NAME records in an existing BIFF file is
important and should not be changed. You can add new names
to a file, but they should be added at the end of the NAME
list. Excel saves out the names in alphabetical order, but
this is not a requirement; Excel will sort the name list, if
necessary, when it loads a BIFF file.

DIMENSIONS record - cell table size (type = 0)
OffsetNameSizeContents
----------------------
4rwMic2first defined row on the document
6rwMac2last defined row on the document,
plus 1
8colMic2first defined column on the document
10colMac2last defined column on the document,
plus 1

Description
The DIMENSIONS record contains the minimum and maximum bounds
of the document. It tells us very quickly the approximate
size of the document.

Note that both the rwMac and colMac fields are 1 greater
than the actual last row and column. For example, for
a worksheet that exists between cells B3 and D6, we would
have rwMic = 2, colMic = 1, rwMac = 6, colMac = 4.

COLUMN DEFAULT record - default cell attributes (type = 32)
OffsetNameSizeContents
----------------------
4colMic2first column that has a default
cell
6colMac2last column that has a default
cell, plus 1
8rgrgbAttrvararray of default cell attributes

Description
The COLUMN DEFAULT record is an optional record that controls
the formats of cells that aren't defined on the worksheet.
This is a space-saving technique. By specifying a default
cell for a particular column, you are telling Excel that all
undefined cells in the column should have the specified cell
attributes. Default cells do not affect the formats of cells
that are explicitly defined.

For example, if you want all of column C to be left-aligned,
then you could define all 16,384 cells in the column and
specify that each one be left-aligned. This would require a
large amount of storage to represent all 16,384 cells. Or,
you could simply set the default cell for column C to be
left-aligned, and not define any cells at all in column C.

The rgrgbAttr field is an array of rgbAttr fields, with the
range of the array being colMic to colMac-1, inclusive. Each
rgbAttr field is 3 bytes long. See the "Cell Attributes"
section for a description of the rgbAttr field.

If the COLUMN DEFAULT record is present, it must appear in
the file before any ROW records or cell records.

ROW record - row descriptor (type = 8)
OffsetNameSizeContents
----------------------
4rw2row number
6colMic2first defined column in the row
8colMac2last defined column in the row,
plus 1
10miyRw2row height
12irwMac2Microsoft internal use
14fDefault1=1 if the row has default cell
attributes
=0 otherwise
15dbRtcell2relative file offset to the cell
records for this row
17rgbAttr 3default cell attributes

Description
A ROW record describes a single row on an Excel document.
colMic and colMac give the range of defined columns in the
row. miyRw is the row height in units of 1/20 of a point.
irwMac is used by Microsoft Excel to optimize loading the
file; if you are creating a BIFF file, set this field to 0.

The miyRw field may have the 0x8000 bit set, indicating that
the row is standard height. The low 15 bits must still
contain the row height.

Each row can have default cell attributes which control the
format of all undefined cells in the row. This is a space-
saving technique. By specifying default cell attributes for a
particular row, you are effectively formatting all the
undefined cells in the row, but without using up memory for
those cells. Default cell attributes do not affect the
formats of cells that are explicitly defined.

For example, if you want all of row 3 to be left-aligned,
then you could define all 256 cells in the row and specify
that each one be left-aligned. This would require storage for
each of the 256 cells. Or, you could simply set the default
cell for row 3 to be left-aligned, and not define any cells
at all in row 3.

The fDefault field indicates whether a default cell is
present or not. If it is, then rgbAttr contains the default
cell attributes. See the "Cell Attributes" section for a
description of the rbgAttr field.

dbRtcell is a relative file offset to the cell records for
the row. This is described in the section "Finding Values
From BIFF Files."

BLANK record - blank cell (type = 1)
OffsetNameSizeContents
----------------------
4rw2row
6col2column
8rgbAttr 3cell attributes

Description
A BLANK record describes a cell with no formula or
value.

See the "Cell Attributes" section for a description of
the rgbAttr field.

INTEGER record - cell with constant integer (type = 2)
OffsetNameSizeContents
----------------------
4rw2row
6col2column
8rgbAttr 3cell attributes
11w2unsigned integer value

Description
An INTEGER record describes a cell containing a constant
unsigned integer in the range 0 - 65535. Negative numbers and
numbers outside this range must be stored as NUMBER records.

See the "Cell Attributes" section for a description of
the rgbAttr field.

NUMBER record - cell with constant floating point number (type = 3)
OffsetNameSizeContents
----------------------
4rw2row
6col2column
8rgbAttr 3cell attributes
11num8floating point number value

Description
A NUMBER record describes a cell containing a constant
floating point number.The number is in 8-byte IEEE floating
point format.

See the "Cell Attributes" section for a description of
the rgbAttr field.

LABEL record - cell with constant string (type = 4)
OffsetNameSizeContents
----------------------
4rw2row
6col2column
8rgbAttr 3cell attributes
11cch1length of the string
12rgchvarthe string

Description
A LABEL record describes a cell with a constant string.
The string length is in the range 0 - 255.

See the "Cell Attributes" section for a description of
the rgbAttr field.

BOOLERR record - cell with constant boolean or error (type = 5)
OffsetNameSizeContents
----------------------
4rw2row
6col2column
8rgbAttr 3cell attributes
11bBoolErr1boolean or error value
12fError1specifies boolean or error
=1 for error
=0 for boolean

Description
A BOOLERR record describes a cell containing a constant
boolean or error value.

Boolean values are 1 for TRUE and 0 or FALSE.
Error values are as follows:
0#NULL!
7#DIV/0!
15#VALUE!
23#REF!
29#NAME?
36#NUM!
42#N/A

See the "Cell Attributes" section for a description of
the rgbAttr field.

FORMULA record - cell with a formula (type = 6)
OffsetNameSizeContents
----------------------
4rw2row
6col2column
8rgbAttr 3cell attributes
11num8current value of formula
19sbRecalc1recalc flag
=0 if the formula is calculated
=nonzero if the formula needs
to be calculated
=3 if the formula is part
of a matrix that needs
to be calculated
20cce1length of parsed expression
21rgcevarparsed expression

Description
A FORMULA record describes a cell with a formula.

The sbRecalc field tells us whether the formula needs to be
recalculated upon loading the file. Normally, when formulas
are saved in BIFF files, they are fully calculated. In some
cases, however, this is not possible. If the formula
contains a circular reference or a "volatile" function which
can never be considered truly calculated, like RAND() or
NOW(), then we indicate that the formula needs to be
calculated upon loading.

Any nonzero value for sbRecalc indicates that the formula
needs to be calculated. The special value of 3 is reserved
for FORMULA records belonging to cells which are part of
matrices, when the entire matrix itself needs to be calculated.

The num field contains the current value of the formula in 8-
byte IEEE format. For formulas that evaluate not to numbers
but to strings, booleans, or error values, the last two bytes
of the num field will be 0xFFFF. This covers the sign bit,
the exponent, and four bits of the fraction.

A boolean is stored in the num field as follows:
OffsetNameSizeContents
----------------------
0otBool1=1 always
11RESERVED - must be zero
2f1boolean value
33RESERVED - must be zero
6fExprO2=0xFFFF always

An error is stored in the num field as follows:
OffsetNameSizeContents
----------------------
0otErr1=2 always
11RESERVED - must be zero
2err1error value
33RESERVED - must be zero
6fExprO2=0xFFFF always

See the BOOLERR record for a description of boolean
and error values.

A string is stored in the num field as follows:
OffsetNameSizeContents
----------------------
0otString1=0 always
15RESERVED - must be zero
6fExprO2=0xFFFF always

The string value itself is not stored in the num field;
instead, it is stored in a separate BIFF record, the
STRING record.

The parsed expression is the cell's formula, stored in
Excel's internal compressed format. See the section
"Excel Formulas" for an explanation.

See the "Cell Attributes" section for a description of
the rgbAttr field.

ARRAY record - array formula (type = 33)
OffsetNameSizeContents
----------------------
4rwFirst 2first row of the array
6rwLast2last row of the array
8colFirst1first column of the array
9colLast 1last column of the array
10sbRecalc1recalc flag
=0 if the array is calculated
=nonzero if the array needs
to be calculated
11cce1length of parsed expression
12rgcevarparsed expression

Description
An ARRAY record describes a formula which was array-entered
into a range of cells. The range in which the array is
entered is given by rwFirst, rwLast, colFirst, and colLast.

The ARRAY record occurs directly after the FORMULA record
for the upper left corner cell of the array, i.e. cell
(rwFirst, colFirst).

The sbRecalc field tells whether the array needs to be
recalculated upon loading or not. See the FORMULA record for
a description of this field. Note that in an ARRAY record,
unlike a FORMULA record, sbRecalc will never have the value 3.

The parsed expression is the array formula, stored in
Excel's internal compressed format. See the section
"Excel Formulas" for an explanation.

CONTINUE record - (type = 60)
OffsetNameSizeContents
----------------------
4rgcevarparsed expression

Description
Some parsed formulas are so long that they are split up into
sections and written out as separate records. The first
section appears in the FORMULA or ARRAY record; subsequent
sections appear in CONTINUE records. Parsed expressions will
be discussed in detail in future documents.

Some EXTERNNAME records are also long enough to need CONTINUE
records.

STRING record - string value of a formula (type = 7)
OffsetNameSizeContents
----------------------
4cch1length of the string
5rgchvarthe string

Description
A STRING record appears after a FORMULA record whose
formula currently evaluates to a string. If the formula
is part of an array, then the STRING record occurs after
the ARRAY record.

TABLE record - one-input table definition (type = 54)
OffsetNameSizeContents
----------------------
4rwFirst 2first row of the table
6rwLast2last row of the table
8colFirst1first column of the table
9colLast 1last column of the table
10sbRecalc1recalc flag
=0 if the table is calculated
=nonzero if the table needs
to be calculated
11fRw1=1 if this is a row input table
=0 if this is a column input table
12rwInp2row of the input cell
14colInp2column of the input cell

Description
A TABLE record describes a one-input row or column table
created through the Data Table command.

The area in which the table is entered is given by rwFirst,
rwLast, colFirst, and colLast.This is the interior of
the table; it does not include the outer row or column,
which contains table formulas or input values.

The sbRecalc field tells whether the array needs to be
recalculated upon loading or not. See the FORMULA record for
a description of this field. Note that in an TABLE record,
unlike a FORMULA record, sbRecalc will never have the value 3.

fRw tells us whether the input cell is a row input cell
or a column input cell. In either case, the input cell
is given by (rwInp, colInp).

rwInp is -1 in the case where the input cell is a deleted
reference, i.e. displays as #REF!. colInp is unused in this
case.

TABLE2 record - two-input table definition (type = 55)
OffsetNameSizeContents
----------------------
4rwFirst 2first row of the table
6rwLast2last row of the table
8colFirst1first column of the table
9colLast 1last column of the table
10sbRecalc1recalc flag
=0 if the table is calculated
=nonzero if the table needs
to be calculated
111RESERVED - must be zero
12rwInpRw 2row of the row input cell
14colInpRw2column of the row input cell
16rwInpCol2row of the column input cell
18colInpCol2column of the column input cell

Description
A TABLE2 record describes a two-input table created
through the Data Table command.

This record is the same as the TABLE record, with the
following exceptions:
- There is no fRw field. The byte is unused.
- There are two input cells, a row input cell
and a column input cell.
- Either input cell, or both input cells, may
have a row of -1 to indicate that the
corresponding input cell is a deleted
reference, i.e. displays as #REF!.

PROTECT record - worksheet protection (type = 18)
OffsetNameSizeContents
----------------------
4fLock2=1 if the document is protected
=0 if the document is not
protected

Description
The PROTECT record specifies whether or not an Excel
document has been protected through the Options Protect
Document command.

Note that this record specifies a document password, as
opposed to the FILEPASS record, which specifies a file
password.

WINDOW PROTECT record - window protection (type = 25)
OffsetNameSizeContents
----------------------
4fLockWn 2=1 if the windows of the document
are protected
=0 otherwise

Description
The WINDOW PROTECT record specifies whether or not the
document's windows are protected, as specified in the Protect
Document command.

PASSWORD record - worksheet password (type = 19)
OffsetNameSizeContents
----------------------
4wPassword2encrypted password for a
protected document

Description
The PASSWORD record contains the encrypted password for
a document protected through the Options Protect Document
command.

NOTE record - notes (type = 28)
OffsetNameSizeContents
----------------------
4rw2row of the note
6col2column of the note
8cch2length of the note
10rgchvarthe note

Description
The NOTE record specifies a note associated with a cell. The
cell is given by the rw and col fields. cch is the length of
the note; rgch contains the text of the note.

Notes longer than 2048 characters must be spread across
multiple NOTE records, each one containing at most 2048
characters. The first NOTE record contains the following
fields:

OffsetNameSizeContents
----------------------
4rw2row of the note
6col2column of the note
8cch2total length of the note (>2048)
10rgch2048the first 2048 characters of the note

Each subsequent NOTE record for the note contains the
following fields:

OffsetNameSizeContents
----------------------
4rw2=-1 always
62RESERVED - must be zero
8cch2length of this section of the note
(<=2048)
10rgchvarsection of the note

WINDOW1 record - basic window information (type = 61)
OffsetNameSizeContents
----------------------
4x2horizontal position of the
window
6y2vertical position of the window
8dx2width of the window
10dy2height of the window
12fHidden 1=1 if the window is hidden
=0 otherwise

Description
The WINDOW1 record provides basic information about an
Excel window. The x and y fields give the location of the
window in units of 1/20 of a point, relative to the upper
left corner of the desktop. dx and dy give the window size,
also in units of 1/20 of a point. fHidden is used to specify
a hidden window.

If you are creating a BIFF file, you can omit the WINDOW1
record, and Excel will create a default window into your
document.

WINDOW2 record - advanced window information (type = 62)
OffsetNameSizeContents
----------------------
4fDspFmla1=1 if the window should display
formulas
=0 if the window should display
values
5fDspGrid1=1 if the window should display
gridlines
=0 otherwise
6fDspRwCol1=1 if the window should display
row and column headers
=0 otherwise
7fFrozen 1=1 if the panes in the window
should be frozen
=0 otherwise
8fDspZeros1=1 if the window should display
zero values
=0 if the window should suppress
display of zero values
9rwTop2top row visible in the window
11colLeft 2leftmost column visible in the
window
13fDefaultHdr1=1 if the row/column headers and
gridlines should be drawn in
the default foreground color
=0 otherwise
14rgbHdr4row/column headers and gridline
color

Description
The WINDOW2 record contains a fuller description of an Excel
window. This record is optional. If it appears, it must
directly follow the WINDOW1 record for the window it describes.

The fDspFmla, fDspGrid, fDspRwCol, and fDspZeros fields are
window properties as set in the Options Display command.
fFrozen is as set through the Options Freeze/Unfreeze Panes
commands.

fDefaultHdr is 1 if the window's row and column headers and
gridlines should be drawn in the window's default foreground
color.If this field is 0, then the RGB color in rgbHdr is
used instead.

PANE Record - window split information (type = 65)
OffsetNameSizeContents
----------------------
4x2horizontal position of the split,
or zero if none
6y2vertical position of the split,
or zero if none
8rwTop2top row visible in the bottom pane
10colLeft 2leftmost column visible in the
right pane
12pnnAct1pane number of the active pane

Description
The PANE record describes the number and position of panes in
a window. The x and y fields give the position of the
vertical and horizontal splits, respectively, in units of
1/20 of a point. Either of these fields may be zero,
indicating that the window is not split in the corresponding
direction.

For a window with a horizontal split, rwTop is the topmost
row visible in the bottom pane or panes. For a window with a
vertical split, colLeft gives the leftmost column visible in
the right pane or panes.

The pnnAct field tells which pane is the active pane. It
contains one of the following values:
0Bottom right
1Top right
2Bottom left
3Top left

If the document window associated with a pane has frozen
panes, as specified in the WINDOW2 record, then x and y have
special meaning. If there is a vertical split, then x
contains the number of columns visible in the top pane. If
there is a horizontal split, then y contains the number of
rows visible in the left pane.Both types of splits can be
present in a window, as in unfrozen panes.

SELECTION record - selection within a pane (type = 29)
OffsetNameSizeContents
----------------------
4pnn1pane number
5rwAct2row number of the active cell
7colAct2column number of the active cell
9irefAct 2reference number of the active cell
11cref2number of references in the
selection
13rgrefvararray of references

Description
The SELECTION record specifies which cells are selected in a
pane of a split window. This record may also be used to
specify selected cells in a window which does not have any
splits.

The pnn field tells which pane we are describing. It
contains one of the following values:
0Bottom right
1Top right
2Bottom left
3Top left
For a window which has no splits, use pnn = 3.

rwAct and colAct specify which cell in the selection is
the active cell.

The selection itself consists of rgref, a variable length
array of references. The number of references in the record
is given by the cref field. Each reference is six bytes long
and contains the following fields:

OffsetNameSizeContents
----------------------
0rwFirst 2first row in the reference
2rwLast2last row in the reference
4colFirst1first column in the reference
5colLast 1last column in the reference

irefAct is a zero-based index into the array of references,
specifying which reference contains the active cell.

If a selection is so large that it won't fit in the maximum
size BIFF record, 2084 bytes, then it is broken down into
multiple consecutive SELECTION records. Each record contains
a portion of the larger selection. Only the cref and rgref
fields vary in the multiple records; the pnn, rwAct, colAct,
and irefAct fields are the same over all records in the
group.On each record, the cref field contains the number of
references found on that record alone.

EOF record - end of file (type = 10)
Description
The EOF record must be the last record in the file.
It has no data associated with it.

Cell Attributes
---------------
This section describes the cell attribute field found in the ROW,
BLANK, INTEGER, NUMBER, LABEL, BOOLERR, FORMULA, and COLUMN DEFAULT
records. The field is three bytes long and consists of bit fields:

OffsetBitsMaskNameContents
--------------------------
070x80fHidden =1 if the cell is hidden
60x40fLocked =1 if the cell is locked
5-0RESERVED - must be zeros
17-60xC0ifntfont number
5-00x3Fifmtthe cell's format code
270x80fShade=1 if the cell is shaded
60x40fBottom =1 if the cell has a
bottom border
50x20fTop=1 if the cell has a
top border
40x10fRight=1 if the cell has a
right border
30x08fLeft=1 if the cell has a
left border
2-00x07alcthe cell's alignment code

The ifnt field is a zero-based index into the document's table of
fonts. The ifmt field is a zero-based index into the document's table
of picture formats. See the FONT and FORMAT records for details.

The alc field has one of the following values:
0General
1Left
2Center
3Right
4Fill
7(Multiplan only) Default alignment

Order of Records
----------------
Here is the order in which records are written in a BIFF file:
BOF
FILEPASS
INDEX
CALCCOUNT
CALCMODE
PRECISION
REFMODE
DELTA
ITERATION
1904
BACKUP
PRINT ROW HEADERS
PRINT GRIDLINES
HORIZONTAL PAGE BREAKS
VERTICAL PAGE BREAKS
DEFAULT ROW HEIGHT
FONT
FONT2
HEADER
FOOTER
LEFT MARGIN
RIGHT MARGIN
TOP MARGIN
BOTTOM MARGIN
COLWIDTH
EXTERNCOUNT
EXTERNSHEET
EXTERNNAME
FORMAT
NAME
DIMENSIONS
COLUMN DEFAULT
Cell table
ROW, BLANK, INTEGER, NUMBER, LABEL,
BOOLERR, FORMULA, ARRAY, STRING,
TABLE, TABLE2
PROTECT
WINDOW PROTECT
PASSWORD
NOTE
WINDOW1
WINDOW2
PANE
SELECTION
EOF

Finding Values From BIFF Files
------------------------------
This section explains how to look up a cell value in a BIFF file,
without having to load the file into Excel. You can look up values
only in BIFF files that are not password-protected; protected BIFF
files are encrypted and cannot be read.

One way to find the value of a particular cell in a BIFF file is to
read every BIFF record, until we find a cell record for the cell. If
we find one, we return its value. If we reach the EOF record without
finding a cell record for the cell, then we return zero.

Fortunately, we don't have to go through such an exhaustive search.
We can narrow down the area that we have to search by using BIFF's
INDEX and ROW records.

If a non-protected BIFF file has an INDEX record, it will be the
second record in the file (immediately after the BOF record).
If the second record is not an INDEX record, then we must resort
to the exhaustive record search described above. Or, alternatively,
we could simply fail the search and return some sort of error code.

Having located the INDEX record, we fetch the rwMic and rwMac fields,
which tell us the range of defined rows on the document. If the row
we are searching for is outside of that range, then we know right
away that the desired cell doesn't exist, so we can return zero.

The next step is to locate the ROW record for the row of the
desired cell. To do this, we need to understand how Excel saves
ROW records and cell records.

When Excel saves a document in BIFF format, it divides the document
into blocks of 32 rows, starting at the first defined row on the
document. Since rwMic is by definition the first defined row, the
first block consists of rows rwMic through rwMic+31; the second, from
rwMic+32 through rwMic+63; and in general, the i-th block, assuming
that i is zero-based, consists of rows (rwMic+i*32) through
(rwMic+i*32+31).

Excel writes a block of ROW records to the file, then follows this
with all the cell records for cells in those rows. This process is
repeated until all ROW and cell records have been written.

The INDEX record contains an array of file pointers to the blocks
of ROW records. Working backwards from our rule above for ROW
blocks, we see that to locate the block for row 'rw', we fetch
array element (rw-rwMic)/32. Here, the '/' operator is integer
division that truncates.

Having found the proper array element, we position the BIFF file at
that location. The file pointer that we fetched from the array is an
absolute byte offset from the beginning of the file, which is byte 0.
For example, if the file pointer were 17,540, then we would position
the file at byte 17,540.

The file is now positioned at the correct block of ROW records. The
next step is to search for the correct ROW record. Since Excel
documents have sparse cell tables, blocks of ROW records contain only
the defined rows within the block range. This means that if the row
we are searching for doesn't exist, then it won't have a ROW record
in the BIFF file.

We must read at most 32 records at this point.If we do not find a
ROW record for the desired row, then we know that the row doesn't
exist, so we can return zero. We know that the row doesn't exist
as soon as we find a non-ROW record, or a ROW record for a row beyond
the one we are searching for.

Having found the correct ROW record, we fetch the colMic and colMac
fields, which tell us the range of defined columns in the row. If the
column we are searching for lies outside of the defined range, then
we know that the desired cell doesn't exist, and we can return zero.

From the ROW record, we can now determine the position within the
file of the cell records for the desired row. The next step is to
position the file at that point and search for the cell record for
the desired cell.

The dbRtcell field contains the offset to cells for the ROW record.
This field is limited to 16 bits to save space in BIFF files; thus
the largest offset that will fit is 65,535. In a large Excel
document, however, it is possible for cells to be located farther
than 65,535 bytes from their ROW record. Therefore we encode the
offset to get more value from it.

The first ROW record in a block contains an offset to cells
relative to the second ROW record. This is because after reading
the first ROW record, you are positioned at the second ROW record,
so finding the cells is just a matter of skipping some number of
bytes relative to the current file position.

The second and all subsequent ROW records in a block contain offsets
to cells relative to the previous ROW record's cells. This iterative
approach works like this: after reading the first ROW record, you get
its offset and add it to the current file position to get the
absolute file position of the first ROW's cell records. When you
read the second ROW record, you add the offset contained therein to
your computed position of the first ROW's cells, and you get the
position of the second ROW's cells. Continuing in this manner, you
find that by the time you find the proper ROW record, you have
already computed the absolute file position of its cells, so you
position there and continue your search.

Having computed the file position of our row's cell records, we set
the file there and start sequentially searching for the desired
cell. If we find the cell, we fetch its value and return it. Our
search fails as soon as we encounter a cell record for a cell beyond
ours, or we encounter a record which is not a cell record.

If a ROW has no defined cells, we will set its dbRtcell offset to
zero. If a ROW's cells are more than 64K from the previous ROW's
cells (which is rare but possible), we will write out a zero offset
for that ROW and ALL subsequent ROW records in the same block.All
this means is that we have to search a little harder for the correct
cell record: instead of being able to start our search at cells in
the desired row, we will have to start searching at cells in some
previous row.

Excel Formulas
--------------
This section describes how Excel stores formulas within BIFF
files.Formulas appear in FORMULA, ARRAY, and NAME records.

In this section, the term "formula" is a synonym for "parsed
expression"; it is the internal tokenized representation of
an Excel formula.

Formulas are stored in a reverse Polish scheme. A formula consists of
a sequence of parse tokens, each of which is either an operand,
operator, or a control token. Operand tokens provide values;
operator tokens perform arithmetic operations upon the operands; and
control tokens assist in formula evaluation by describing properties
of the formula.

A token consists of two parts: a token type and a token value. Token
types are called "ptg's" in Excel; they are one byte long, ranging in
value from 1 to 0x7F. Ptg's above 0x7F are reserved for internal
Excel use.

The ptg specifies only what kind of information is contained
in a token. The information itself is stored in the token value,
immediately following the ptg in the parsed expression. Some
tokens consist only of a ptg, without an accompanying token
value; for example, to specify an addition operation, only the token
type, ptgAdd, is required. But to specify an integer operand, both
the ptg, ptgInt, and the token value, an integer, must be
specified.

As an illustration, consider the parsed expression for =5+6. This
parsed expression consists of three tokens: two integer operands and
an operator.

ptgInt 0x0005 ptgInt 0x0006ptgAdd
< token 1 > < token 2>

Notice that each ptgInt is immediately followed by the
integer token value.

In many cases, the token value consists of a structure of two or more
fields. In describing structures for these cases, offset zero is
assumed to be the first byte of the token value, i.e. the first byte
immediately following the token type.

Unless otherwise noted, all tokens can occur in FORMULA, ARRAY, and
NAME records. Some tokens do not appear in one or more of these
record types; they are explained as encountered.

Expression Evaluation
---------------------
The evaluation of Excel formulas is a straightforward process. One
LIFO stack, the operand stack, is maintained during evaluation. When
an operand is encountered, it is 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 are encountered.

There are three kinds of operators: unary, binary, and function.
Unary operators, like the minus sign which negates a number, operate
only on the topmost operand. Binary operators, like the addition
operator, operate on the top two operands. Function operators, which
implement 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 pushing the result back onto the operand
stack.

Unary Operators
---------------
Here are the unary operator tokens. All of these operators pop the
top argument from the operand stack, perform a calculation, and push
the result back onto the operand stack.

ptgUplus - unary plus (ptg = 0x12)
This operator has no effect.

ptgUminus - unary minus (ptg = 0x13)
Negates the top operand.

ptgPercent - percent sign (ptg = 0x14)
Divides the top operand by 100

Binary Operators
----------------
Here are the binary operator ptg's. All of these operators pop the
top two arguments from the operand stack, perform a calculation, and
push the result back onto the operand stack.

ptgAdd - addition (ptg = 0x03)
Adds the top two operands together.

ptgSub - subtraction (ptg = 0x04)
Subtracts the top operand from the second-to-top.

ptgMul - multiplication (ptg = 0x05)
Multiplies the top two operands.

ptgDiv - division (ptg = 0x06)
Divides the top operand by the second-to-top.

ptgPower - exponentiation (ptg = 0x07)
Raises the second-to-top operand to the power of the top operand.

ptgConcat - concatenation (ptg = 0x08)
Appends the top operand to the second-to-top operand.

ptgLT - less than (ptg = 0x09)
Evaluates to TRUE if the second-to-top operand is less than the top
operand; FALSE otherwise.

ptgLE - less than or equal (ptg = 0x0A)
Evaluates to TRUE if the second-to-top operand is less than or equal
to the top operand; FALSE otherwise.

ptgEQ - equal (ptg = 0x0B)
Evaluates to TRUE if the top two operands are equal; FALSE otherwise.

ptgGE - greater than or equal (ptg = 0x0C)
Evaluates to TRUE if the second-to-top operand is greater than or
equal to the top operand; FALSE otherwise.

ptgGT - greater than (ptg = 0x0D)
Evaluates to TRUE if the second-to-top operand is greater than the
top operand; FALSE otherwise.

ptgNE - not equal (ptg = 0x0E)
Evaluates to TRUE if the top two operands are not equal; FALSE
otherwise.

ptgIsect - intersection (ptg = 0x0F)
This is the Excel space operator. It computes the intersection of
the top two operands.

ptgUnion - union (ptg = 0x10)
This is the Excel comma operator. It computes the union of the top
two operands.

ptgRange - range (ptg = 0x11)
This is the Excel colon operator. It computes the minimal bounding
rectangle of the top two operands.

Operand Tokens - Constant
-------------------------
The following operand tokens push a single constant operand onto the
operand stack.

ptgMissArg - missing argument (operand, ptg = 0x16)
Missing argument to an Excel function. For example, the second
argument to DCOUNT(Database,,Criteria) would be stored as a
ptgMissArg.

ptgStr - string constant (operand, ptg = 0x17)
String constant. Followed by the string.

OffsetNameSizeContents
----------------------
0cch1length of the string
1rgchvarthe string

ptgStr requires special handling when parsed expressions are
scanned. See the section "Scanning a Parsed Expression" for an
explanation.

ptgErr - error value (operand, ptg = 0x1C)
Error constant. Followed by the error value. See the BOOLERR record
for a list of error values.

OffsetNameSizeContents
----------------------
0err1Excel error value

ptgBool - boolean (operand, ptg = 0x1D)
Boolean constant. Followed by a byte value.

OffsetNameSizeContents
----------------------
0f1=1 for TRUE
=0 for FALSE

ptgInt - integer (operand, ptg = 0x1E)
Integer constant. Followed by a word value.

OffsetNameSizeContents
----------------------
0w2unsigned integer value

ptgNum - number (operand, ptg = 0x1F)
Numeric constant. Followed by an 8-byte IEEE floating point number.

OffsetNameSizeContents
----------------------
0num8IEEE floating point number

Operand Tokens - Classes
------------------------
As described above, operand tokens push operand values onto the
operand stack.These values are divided into three different
classes, depending on what type of value the formula expects from the
operand. The type of value is determined at parse time by the
context of the operand.

REFERENCE CLASS. Some operands are required by context to be
references. In this case, the term "reference" is a general term
meaning the specification of one or more areas on an Excel document,
without regard for the underlying cell values in those areas. When
the Excel expression evaluator encounters a reference type operand,
it pushes only the reference itself onto the operand stack; it does
not dereference it to find the underlying cell values.

For example, consider the formula CELL("width",B5), which returns the
column width of cell B5. Clearly, only the reference to cell B5 is
important here; the value stored at cell B5 is irrelevant to the cell'
s width.

VALUE CLASS. This is the most common type of operand; it pushes a
single dereferenced value onto the operand stack.

For example, consider the formula A1+1. Here, we are interested in
the value stored in cell A1, so we dereference the A1 reference.

ARRAY CLASS. This operand pushes an array of values onto the operand
stack.The values may be specified either in an array constant or in
a reference to cells.

For example, consider the formula SUM({1,2,3;4,5,6}). Here, to
evaluate the SUM function, the expression evaluator must push an
entire array of values onto the operand stack.

The three classes of operand tokens are numerically divided as
follows:

Operand ClassPtg's
------------------
Reference0x20 - 0x3F
Value0x40 - 0x5F
Array0x60 - 0x7F

Notice that the numerical difference between ptg classes is 0x20.
This is the basis for forming the class variants of ptg's. Class
variants of ptg's are formed from the reference class ptg, also known
as the "base" ptg. To form the value class ptg from the base ptg,
you add 0x20 to the ptg and append "V" (for "value") to the ptg
name. To form the array class ptg from the base ptg, you add 0x40 to
the ptg and append "A" (for "array") to the ptg name. These rules are
summarized below for a hypothetical ptg called ptgFoo:

ClassNamePtg
------------
ReferenceptgFooptgFoo
ValueptgFooV ptgFoo + 0x20
ArrayptgFooA ptgFoo + 0x40

For example, the base ptg which specifies a cell reference is ptgRef,
which is equal to 0x24. Thus the reference class ptg is ptgRef,
which is 0x24; the value class ptg is ptgRefV, which is 0x44; and the
array class ptg is ptgRefA, which is 0x64.

Here is a useful method for computing the base ptg from any class
variant:

if (ptg & 0x40)
{
/* We have a value class ptg. We need to set the
0x20 bit to make it reference class, then strip
off the high order bits. */
ptgBase = (ptg | 0x20) & 0x3F;
}
else
{
/* We have a reference or array class ptg. The 0x20
bit is already set, so we just have to strip off
the high order bits. */
ptgBase = ptg & 0x3F;
}

A more efficient implementation in C is to define a macro which
computes the base ptg:

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

This macro is safe, i.e. it can be used on any ptg without damage.

Operand Tokens - Base
---------------------
This section lists the operand tokens in their base form (also known
as reference class).

ptgArray - array constant (operand, ptg = 0x20)
Array constant. Followed by six bytes.

OffsetNameSizeContents
----------------------
06RESERVED

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 does not 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 as follows:

OffsetNameSizeContents
----------------------
0ccol1number of columns in the array
constant
1crw2number of rows in the array constant
3rgvalvarthe array values

256-column arrays are stored with a ccol of zero, since the true
number of columns does not fit into a byte field. This is acceptable
since there are no zero-column array constants.

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 of these values
are as follows:

OffsetNameSizeContents
----------------------
0grbit1=0x01 for a number
1num8IEEE floating point number

OffsetNameSizeContents
----------------------
0grbit1=0x02 for a string
1cch1length of the string
2rgchvarthe string

If a formula contains more than one array constant, then 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, and so on.

If a formula contains very long array constants, then the BIFF record
containing the parsed expression may overflow into CONTINUE records
to accomodate all of the array values. An individual array value is
never split between records; record boundaries occur between
successive array values.

In practice, the reference class ptgArray never appears in an Excel
formula; only the value and array classes are used.

ptgName - name (operand, ptg = 0x23)
This ptg specifies the usage of an Excel name.The token value
specifies which name is referenced.

OffsetNameSizeContents
----------------------
0ilbl2index of the referenced name
25RESERVED - must be zeros

For local (i.e. non-external) name references, the ilbl field
specifies a 1-based index into the document's own name table. The
order of this name table is the order of NAME records in the BIFF file.

For external name references, the ilbl field specifies a 1-based
index into the table of externally referenced names defined on the
supporting document. The order of this name table is the order of
EXTERNNAME records which are associated with the supporting
document.

ptgRef - cell reference (operand, ptg = 0x24)
This ptg specifies a reference to a single cell. It is followed by
the row and column of the reference. The row is encoded as bit fields.

OffsetNameSizeContents
----------------------
0grbitRw 2row bit fields (see below)
2col1column of the reference

Only the low 14 bits of the grbitRw field store the row number of the
reference. The high 2 bits specify whether the row or column portion
of the reference is a relative reference. Here is the bit field
structure of the grbitRw field:

BitsMaskNameContents
--------------------
150x8000fRwRel=1 if the row portion of the
reference is relative
=0 otherwise
140x4000fColRel =1 if the column portion of the
reference is relative
=0 otherwise
13-00x3FFFrwthe row number of the reference

For example, cell C5 is row number 4, column number 2 (since
Excel stores cell references zero-based). Therefore the absolute
reference $C$5 is stored as

ptgRef 0x0004 0x02.

The relative reference C5 is stored as

ptgRef 0xC004 0x02.

The mixed reference $C5 (absolute row, relative column) is stored as

ptgRef 0x4004 0x02.

ptgArea - area reference (operand, ptg = 0x25)
This ptg specifies a reference to a rectangle of cells. It is
followed by the first row of the rectangle, last row, first column,
and last column. Both the first row and last row are stored as bit
fields.

OffsetNameSizeContents
----------------------
0grbitRwFirst2first row bit fields (see below)
2grbitRwLast2last row bit fields (see below)
4colFirst1first column of the reference
5colLast 1last column of the reference

The high order 2 bits of grbitRwFirst specify whether the first row
or first column are relative references. The high order 2 bits of
grbitRwLast specify whether the last row or last column are relative
references. See the ptgRef token for a fuller explanation of these
bit fields.

For example, consider references to the area C5:D8. C5 is row 4,
column 2; D8 is row 7, column 3 (since Excel stores cell references
zero-based). Therefore the absolute reference $C$5:$D$8 is stored as

ptgArea 0x0004 0x0007 0x02 0x03.

The relative reference C5:D8 is stored as

ptgArea 0xC004 0xC007 0x02 0x03.

The mixed reference C$5:$D8 (absolute first row, relative first
column, relative last row, absolute last column) is stored as

ptgArea 0x4004 0x8007 0x02 0x03.

ptgMemArea - constant reference subexpression (operand, ptg = 0x26)
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). Here are three examples of reference expressions:

- A1,C3,D3:D5.This evaluates to two single cells and a
3x1 area.
- (A1:C3) (B2:D4). This evaluates to a 2x2 area.
- Name:C3. This evaluates to the smallest area which
contains both C3 and all the cells referenced in "Name".

Many reference expressions evaluate to constant references. In the
examples above, the first two expressions always evaluate to the same
reference. The third example does not evaluate to a constant
reference, since it depends on the name "Name", and Name's definition
might change, which would cause the reference expression to evaluate
differently.

When a reference expression does evaluate to a constant reference,
Excel stores the constant reference in the parsed formula through a
ptgMemArea token. This saves time during expression evaluation, since
part of the expression being evaluated will have been pre-evaluated.
This part of the expression is known as a reference subexpression.

ptgMemArea only occurs in FORMULA and ARRAY records, never in NAME
records.

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, but the value is appended to the saved parsed expression,
immediately following the last token.

The format of the length is as follows:

OffsetNameSizeContents
----------------------
03RESERVED
3cce1length of the reference subexpression

Immediately following this part of the token value is the reference
subexpression itself, whose length is given by the cce field.

The rest of the token value, i.e. the value of the reference
subexpression, is appended to the parsed expression in the
following format:

OffsetNameSizeContents
----------------------
0cref2number of rectangles to follow
2rgrefvararray of rectangles

Each rectangle is six bytes long and contains the following fields:

OffsetNameSizeContents
----------------------
0rwFirst 2first row
2rwLast2last row
4colFirst1first column
5colLast 1last column

If a formula contains more than one ptgMemArea, then 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, and
so on.

If a formula contains very long reference expressions, then the BIFF
record containing the parsed expression may overflow into CONTINUE
records to accomodate all of the array values. An individual
rectangle is never split between records; record boundaries occur
between successive rectangles.

ptgMemErr - bad constant reference subexpression (operand, ptg = 0x27)
This ptg is closely related to ptgMemArea. It is used for
pre-evaluating reference subexpressions which do not evaluate to
references.

For example, consider the formula SUM(C:C 3:3). The argument to the
SUM function is a valid reference subexpression, which generates a
ptgMemArea for pre-evaluation.If the user deletes column C, then
the formula adjusts to SUM(#REF! 3:3).In this case, the argument to
SUM is still a constant reference subexpression, but it does not
evaluate to a reference. Therefore a ptgMemErr is used for
pre-evaluation.

The token value consists of the error value and length of the
reference subexpression. Its format is as follows:

OffsetNameSizeContents
----------------------
02RESERVED
2err1error value
3cce1length of the reference subexpression

See the BOOLERR record for a list of error values.

ptgRefErr - deleted cell reference (operand, ptg = 0x2A)
This ptg specifies a cell reference that was adjusted to #REF! as a
result of spreadsheet editing (e.g. cut and paste, delete). It is
followed by three unused bytes.

OffsetNameSizeContents
----------------------
03RESERVED

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

ptgAreaErr - deleted area reference (operand, ptg = 0x2C)
This ptg specifies an area reference that was adjusted to #REF! as a
result of spreadsheet editing (e.g. cut and paste, delete). It is
followed by six unused bytes.

OffsetNameSizeContents
----------------------
06RESERVED

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

ptgRefN - cell reference within a name (operand, ptg = 0x2C)
This ptg only occurs in the parsed expression of a NAME record, never
in a FORMULA or ARRAY record. It specifies a reference to a single
cell. It is followed by the row and column of the reference. The
row is encoded as bit fields.

The only difference between ptgRefN and ptgRef is the way relative
references are stored.Relative references within name definitions
are stored as offsets, not as row and column numbers. For example,
if a name "Prev_cell" is defined to the relative reference
=R[-2]C[-3] (assuming R1C1 mode), then the parsed expression for
Prev_cell is

ptgRefN 0xFFFE 0xFD.

The row offset, -2, in hexadecimal is 0xFFFE; the column offset, -3,
is 0xFFFD. The row portion of the token value consists of the low 14
bits of the row offset, plus two high-order '1' bits to indicate that
both the row and column portions are relative references. The column
portion of the token value is simply the low byte of 0xFFFD.

If instead the name is =R[-2]C3, i.e. with an absolute column
reference, then the parsed expression is

ptgRefN 0xBFFE 0x02.

ptgAreaN - area reference within a name (operand, ptg = 0x2D)
This ptg only occurs in the parsed expression of a NAME record, never
in a FORMULA or ARRAY record. It specifies a reference to a rectangle
of cells. It is followed by the first row of the rectangle, last
row, first column, and last column. Both the first row and last row
are stored as bit fields.

The only difference between ptgAreaN and ptgArea is the way relative
references are stored.See ptgRefN for an explanation of this.

Control Tokens
--------------
ptgExp - array formula (ptg = 0x01)
This ptg indicates an array formula. It only occurs in a FORMULA
record, never in an ARRAY or NAME record. When ptgExp occurs in a
formula, it is the only token in the formula. This indicates that
the cell containing the formula is part of an array; the array
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.

OffsetNameSizeContents
----------------------
0rwFirst 2row number of upper left corner
2colFirst1column number of upper left corner

ptgTbl - data table (ptg = 0x02)
This ptg indicates a data table. It only occurs in a FORMULA record,
never in an ARRAY or NAME record. When ptgTbl occurs in a formula,
it is 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 which
contain input values to be substituted in the table do not contain
ptgTbl.

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

OffsetNameSizeContents
----------------------
0rwFirst 2row number of upper left corner
2colFirst1column number of upper left corner

ptgParen - parenthesis (ptg = 0x15)
This ptg is used only in unparsing a parsed expression, not in
evaluating it.It indicates that the previous token in the parsed
expression should be parenthesized. If the previous token is an
operand, then only that operand is parenthesized. If the previous
token is an operator, then the operator and all of its operands are
parenthesized.

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

ptgInt 0x0001 ptgInt 0x0002 ptgParen ptgAdd,

and only the operand 2 is parenthesized. But the formula (1+2)
is stored as

ptgInt 0x0001 ptgInt 0x0002 ptgAdd ptgParen,

so the parenthesized quantity consists of the ptgAdd operator and
both of its operands.

ptgAttr - special attribute (ptg = 0x19)
This ptg is used for a variety of purposes. In all cases, the token
value consists of a byte of flags and a byte dependent on the flags.

OffsetNameSizeContents
----------------------
0grbit1bit flags
1b1data

The grbit field contains the following flags:

BitMaskNameContents
-------------------
00x01bitFAttrSemi=1 if the formula contains a
"volatile" function
10x02bitFAttrIf=1 to implement an optimized IF
function
20x04bitFAttrChoose=1 to implement an optimized CHOOSE
function
30x08bitFAttrGoto=1 to jump to another location
within the parsed expression
40x10bitFAttrSum=1 to implement an optimized SUM
function
50x20bitFAttrBaxcel=1 if the formula is a BASIC-style
assignment statement

bitFAttrSemi is set to 1 if the formula contains a volatile function,
i.e. a function which is calculated in every recalculation. The
volatile functions in Excel are:
INDEX
RAND
NOW
AREAS
ROWS
COLUMNS
CELL
INDIRECT

If ptgAttr is used to indicate a volatile function, then it must be
the first token in the parsed expression. The b field is unused.

bitFAttrGoto instructs the expression evaluator to skip part of
the parsed expression during evaluation. The b field specifies
the number of bytes to skip, minus one.

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

bitFAttrIf immediately follows the condition portion of the parsed
expression. The b 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 is a bitFAttrGoto token
which causes a jump to beyond the FALSE subexpression. In this way,
Excel only evaluates the correct subexpression, instead of evaluating
both of them and discarding the wrong one.

The FALSE subexpression is optional in Excel. If it is missing,
then the b field of the bitFAttrIf token specifies an offset to
beyond the TRUE subexpression.

bitFAttrChoose indicates an optimized CHOOSE function.The b field
specifies the number of cases in the CHOOSE function, and is followed
by a sequence of byte offsets to those cases. The number of byte
offsets in the sequence is one more than the number of cases in the
CHOOSE function. Here is the format of this complex token value:

OffsetNameSizeContents
----------------------
0grbit1bitFAttrChoose
1cCases1the number of cases in the CHOOSE
function
2rgbvara sequence of byte offsets to the
CHOOSE cases. The number of bytes
in this field is equal to the cCases
field, plus one.

bitFAttrChoose requires special handling when parsed expressions
are scanned. See the section "Scanning a Parsed Expression" for
an explanation.

bitFAttrSum indicates an optimized SUM function. This is only used
to optimize SUM functions with a single argument. The b field is
unused.

ptgSheet - external reference (ptg = 0x1A)
This ptg indicates the start of an external reference.The token
value indicates which sheet is being externally referenced. When
this token is encountered during evaluation, it indicates that any
following references to cells or names are external references, not
local references, until the matching ptgEndSheet token is encountered.

OffsetNameSizeContents
----------------------
04RESERVED
4ixals2index of the supporting sheet
61RESERVED - must be zero

The ixals field specifies a 1-based index into the table of
externally referenced documents. The order of this table is the
order of EXTERNSHEET records.

ptgEndSheet - end external reference (ptg = 0x1B)
This ptg indicates the end of an external reference. It is followed
by three bytes.

OffsetNameSizeContents
----------------------
03RESERVED

ptgMemNoMem - incomplete constant reference subexpression (ptg = 0x28)
This ptg is closely related to ptgMemArea. It is used to indicate a
constant reference subexpression which could not be pre-evaluated
because of low memory conditions. It only occurs in FORMULA and
ARRAY records, never in NAME records.

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

OffsetNameSizeContents
----------------------
03RESERVED
3cce1length of the reference subexpression

ptgMemFunc - variable reference subexpression (ptg = 0x29)
This ptg indicates a reference subexpression which does not evaluate
to a constant reference. Any reference subexpression containing one
or more of the following will generate a ptgMemFunc:

- an Excel function
- a usage of a name
- an external reference.

Here are examples of the three kinds of ptgMemFunc's:

- INDEX(ref,row_num,column_num,area_num):C3
- Name:$B$2
- SALES.XLS!$A$1:SALES.XLS!$C$3

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

OffsetNameSizeContents
----------------------
0cce1length of the reference subexpression

ptgMemAreaN - reference subexpression within a name (ptg = 0x2E)
This ptg only occurs in the parsed expression of a NAME record, never
in a FORMULA or ARRAY record. It indicates a constant reference
subexpression within a name definition. Unlike ptgMemArea,
ptgMemAreaN is not used to pre-evaluate the reference subexpression.

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

OffsetNameSizeContents
----------------------
0cce1length of the reference subexpression

ptgMemNoMemN - incomplete reference subexpression within a name (control,
ptg = 0x2F)
This ptg is closely related to ptgMemAreaN. It is used to indicate a
constant reference subexpression within a name which could not be
evaluated because of low memory conditions. It only occurs in NAME
records, never in FORMULA or ARRAY records.

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

OffsetNameSizeContents
----------------------
0cce1length of the reference subexpression

Function Operators
------------------
Here are the function operator ptg's. All of these operators pop
arguments from the operand stack, compute a function, and 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
Excel function. Some Excel functions always require a fixed number of
arguments, while others may accept a variable number of arguments.
The SUM function, for example, accepts from 1 to 14 arguments.

Although they are operators, function tokens also behave like
operands in that they can occur in any of the three ptg classes
(reference, value, and array).

ptgFunc - Excel function (operator, ptg = 0x21)
Indicates an Excel function with a fixed number of arguments.
Followed by the index of the function within the function table.
See the section "Excel Function Table" for a list of Excel functions.

OffsetNameSizeContents
----------------------
0iftab1index of the function

ptgFuncVar - Excel function (operator, ptg = 0x22)
Indicates an Excel function with a variable number of arguments.
Followed by the number of arguments and the index of the function
within the function table. See the section "Excel Function Table" for
a list of Excel functions.

OffsetNameSizeContents
----------------------
0carg1number of arguments to the function
1iftab1index of the function

ptgFuncCE - command-equivalent function (operator, ptg = 0x38)
Indicates an Excel command-equivalent function. Followed by the
number of arguments and the index of the function within the command-
equivalent function table. See the section "Command Equivalent
Function Table" for a list of Excel command-equivalent functions.

OffsetNameSizeContents
----------------------
0carg1number of arguments to the function
1icetab1index of the function

Reserved Ptg's
--------------
All ptg's between 0 and 0xFF not explicitly mentioned in this
document are reserved by Microsoft for future use.

Scanning a Parsed Expression
----------------------------
One fairly common operation on parsed expressions is to scan them,
taking appropriate actions at each ptg. This is accomplished with a
loop using a pointer variable, which points to the next ptg to scan.
However, this pointer must be incremented carefully, since different
ptg's may have token values of different lengths.

One good solution to this problem is to maintain a array, with one
element per ptg, containing the size of the token value. To
increment the pointer, you simply add the array element corresponding
to the current ptg. A possible space optimization here is to limit
the array indices to the range 0 - 0x3F, and then index it using the
base ptg instead of the fully classed ptg. This works because
the token value is the same for all classes of a particular ptg.

There are two tokens which are variable length, and so do not fit
this framework. These tokens must be handled as special cases in
any formula scanning loop.

The first exception is ptgStr, which is followed by a variable length
string. The token value specifies the length of the string, so the
pointer is incrementing by fetching and adding the string length from
the token value.

The other exception is the bitFAttrChoose token of ptgAttr. The
token value contains a variable number of bytes in sequence. The
number of bytes in the sequence is specified in the token value, so
the proper method of incrementing is to fetch and add the sequence
length.

Here is sample C code which scans a parsed expression:

Scan(rgb, cb)
char rgb[];/* The parsed expression */
int cb; /* The length of the parsed expression */
{
char *pb;/* Pointer to the current token */
char *pbMac;/* Pointer to the end of the p.e. */
int ptg;/* Raw ptg */
int ptgBase;/* Base ptg */
extern char token_size[];/* Array of token value sizes */

#define bitFAttrChoose0x04/* CHOOSE type of ptgAttr */

pb = rgb;
pbMac = &rgb[cb];
while (pb < pbMac)
{
/* Fetch the next token and determine its base type.
Note that the postincrement conveniently leaves pb
pointing to the token value. */
ptg = *pb++;
ptgBase = PtgBase(ptg);
switch (ptgBase)
{
...
case ptgAttr:
/* Check for a CHOOSE ptgAttr and skip over the
table of offsets if found. */
if (*pb & bitFAttrChoose)
pb += *(pb + 1) + 1;
break;
case ptgStr:
/* String constant. Skip over the size byte and
the string itself. */
pb += *pb + 1;
break;
default:
/* Look up the token value size and add it to the
pointer. The token_size array is only indexed
by the base ptg as a space optimization, since
the token sizes of the value and array classes
are the same as the base class'. */
pb += token_size[ptgBase];
break;
...
}
}
}

Excel Function Table
--------------------
Here is a list of Excel functions sorted by index. The Excel name
for a function index is "iftab". iftab's appear in ptgFunc and
ptgFuncVar tokens. Unused iftab's are reserved for future use.

Function iftab
-------- -----
COUNT 0x00
IF 0x01
ISNA 0x02
ISERROR 0x03
SUM 0x04
AVERAGE 0x05
MIN 0x06
MAX 0x07
ROW 0x08
COLUMN 0x09
NA 0x0A
NPV 0x0B
STDEV 0x0C
DOLLAR 0x0D
FIXED 0x0E
SIN 0x0F
COS 0x10
TAN 0x11
ATAN 0x12
PI 0x13
SQRT 0x14
EXP 0x15
LN 0x16
LOG10 0x17
ABS 0x18
INT 0x19
SIGN 0x1A
ROUND 0x1B
LOOKUP 0x1C
INDEX 0x1D
REPT 0x1E
MID 0x1F
LEN 0x20
VALUE 0x21
TRUE 0x22
FALSE 0x23
AND 0x24
OR 0x25
NOT 0x26
MOD 0x27
DCOUNT 0x28
DSUM 0x29
DAVERAGE 0x2A
DMIN 0x2B
DMAX 0x2C
DSTDEV 0x2D
VAR 0x2E
DVAR 0x2F
TEXT 0x30
LINEST 0x31
TREND 0x32
LOGEST 0x33
GROWTH 0x34
GOTO 0x35
HALT 0x36
RETURN 0x37
PV 0x38
FV 0x39
NPER 0x3A
PMT 0x3B
RATE 0x3C
MIRR 0x3D
IRR 0x3E
RAND 0x3F
MATCH 0x40
DATE 0x41
TIME 0x42
DAY 0x43
MONTH 0x44
YEAR 0x45
WEEKDAY 0x46
HOUR 0x47
MINUTE 0x48
SECOND 0x49
NOW 0x4A
AREAS 0x4B
ROWS 0x4C
COLUMNS 0x4D
OFFSET 0x4E
ABSREF 0x4F
RELREF 0x50
ARGUMENT 0x51
SEARCH 0x52
TRANSPOSE 0x53
ERROR 0x54
STEP 0x55
TYPE 0x56
ECHO 0x57
SET.NAME 0x58
CALLER 0x59
DEREF 0x5A
WINDOWS 0x5B
SERIES 0x5C
DOCUMENTS 0x5D
ACTIVE.CELL 0x5E
SELECTION 0x5F
RESULT 0x60
ATAN2 0x61
ASIN 0x62
ACOS 0x63
CHOOSE 0x64
HLOOKUP 0x65
VLOOKUP 0x66
LINKS 0x67
INPUT 0x68
ISREF 0x69
GET.FORMULA 0x6A
GET.NAME 0x6B
SET.VALUE 0x6C
LOG 0x6D
EXEC 0x6E
CHAR 0x6F
LOWER 0x70
UPPER 0x71
PROPER 0x72
LEFT 0x73
RIGHT 0x74
EXACT 0x75
TRIM 0x76
REPLACE 0x77
SUBSTITUTE 0x78
CODE 0x79
NAMES 0x7A
DIRECTORY 0x7B
FIND 0x7C
CELL 0x7D
ISERR 0x7E
ISTEXT 0x7F
ISNUMBER 0x80
ISBLANK 0x81
T 0x82
N 0x83
FOPEN 0x84
FCLOSE 0x85
FSIZE 0x86
FREADLN 0x87
FREAD 0x88
FWRITELN 0x89
FWRITE 0x8A
FPOS 0x8B
DATEVALUE 0x8C
TIMEVALUE 0x8D
SLN 0x8E
SYD 0x8F
DDB 0x90
GET.DEF 0x91
REFTEXT 0x92
TEXTREF 0x93
INDIRECT 0x94
REGISTER 0x95
CALL 0x96
ADD.BAR 0x97
ADD.MENU 0x98
ADD.COMMAND 0x99
ENABLE.COMMAND 0x9A
CHECK.COMMAND 0x9B
RENAME.COMMAND 0x9C
SHOW.BAR 0x9D
DELETE.MENU 0x9E
DELETE.COMMAND 0x9F
GET.CHART.ITEM 0xA0
DIALOG.BOX 0xA1
CLEAN 0xA2
MDETERM 0xA3
MINVERSE 0xA4
MMULT 0xA5
FILES 0xA6
IPMT 0xA7
PPMT 0xA8
COUNTA 0xA9
CANCEL.KEY 0xAA
FOR 0xAB
WHILE 0xAC
BREAK 0xAD
NEXT 0xAE
INITIATE 0xAF
REQUEST 0xB0
POKE 0xB1
EXECUTE 0xB2
TERMINATE 0xB3
RESTART 0xB4
HELP 0xB5
GET.BAR 0xB6
PRODUCT 0xB7
FACT 0xB8
GET.CELL 0xB9
GET.WORKSPACE 0xBA
GET.WINDOW 0xBB
GET.DOCUMENT 0xBC
DPRODUCT 0xBD
ISNONTEXT 0xBE
GET.NOTE 0xBF
NOTE 0xC0
STDEVP 0xC1
VARP 0xC2
DSTDEVP 0xC3
DVARP 0xC4
TRUNC 0xC5
ISLOGICAL 0xC6
DCOUNTA 0xC7
DELETE.BAR 0xC8

Here is a list of Excel functions sorted alphabetically by
function:

Function iftab
-------- -----
ABS 0x18
ABSREF 0x4F
ACOS 0x63
ACTIVE.CELL 0x5E
ADD.BAR 0x97
ADD.COMMAND 0x99
ADD.MENU 0x98
AND 0x24
AREAS 0x4B
ARGUMENT 0x51
ASIN 0x62
ATAN 0x12
ATAN2 0x61
AVERAGE 0x05
BREAK 0xAD
CALL 0x96
CALLER 0x59
CANCEL.KEY 0xAA
CELL 0x7D
CHAR 0x6F
CHECK.COMMAND 0x9B
CHOOSE 0x64
CLEAN 0xA2
CODE 0x79
COLUMN 0x09
COLUMNS 0x4D
COS 0x10
COUNT 0x00
COUNTA 0xA9
DATE 0x41
DATEVALUE 0x8C
DAVERAGE 0x2A
DAY 0x43
DCOUNT 0x28
DCOUNTA 0xC7
DDB 0x90
DELETE.BAR 0xC8
DELETE.COMMAND 0x9F
DELETE.MENU 0x9E
DEREF 0x5A
DIALOG.BOX 0xA1
DIRECTORY 0x7B
DMAX 0x2C
DMIN 0x2B
DOCUMENTS 0x5D
DOLLAR 0x0D
DPRODUCT 0xBD
DSTDEV 0x2D
DSTDEVP 0xC3
DSUM 0x29
DVAR 0x2F
DVARP 0xC4
ECHO 0x57
ENABLE.COMMAND 0x9A
ERROR 0x54
EXACT 0x75
EXEC 0x6E
EXECUTE 0xB2
EXP 0x15
FACT 0xB8
FALSE 0x23
FCLOSE 0x85
FILES 0xA6
FIND 0x7C
FIXED 0x0E
FOPEN 0x84
FOR 0xAB
FPOS 0x8B
FREAD 0x88
FREADLN 0x87
FSIZE 0x86
FV 0x39
FWRITE 0x8A
FWRITELN 0x89
GET.BAR 0xB6
GET.CELL 0xB9
GET.CHART.ITEM 0xA0
GET.DEF 0x91
GET.DOCUMENT 0xBC
GET.FORMULA 0x6A
GET.NAME 0x6B
GET.NOTE 0xBF
GET.WINDOW 0xBB
GET.WORKSPACE 0xBA
GOTO 0x35
GROWTH 0x34
HALT 0x36
HELP 0xB5
HLOOKUP 0x65
HOUR 0x47
IF 0x01
INDEX 0x1D
INDIRECT 0x94
INITIATE 0xAF
INPUT 0x68
INT 0x19
IPMT 0xA7
IRR 0x3E
ISBLANK 0x81
ISERR 0x7E
ISERROR 0x03
ISLOGICAL 0xC6
ISNA 0x02
ISNONTEXT 0xBE
ISNUMBER 0x80
ISREF 0x69
ISTEXT 0x7F
LEFT 0x73
LEN 0x20
LINEST 0x31
LINKS 0x67
LN 0x16
LOG 0x6D
LOG10 0x17
LOGEST 0x33
LOOKUP 0x1C
LOWER 0x70
MATCH 0x40
MAX 0x07
MDETERM 0xA3
MID 0x1F
MIN 0x06
MINUTE 0x48
MINVERSE 0xA4
MIRR 0x3D
MMULT 0xA5
MOD 0x27
MONTH 0x44
N 0x83
NA 0x0A
NAMES 0x7A
NEXT 0xAE
NOT 0x26
NOTE 0xC0
NOW 0x4A
NPER 0x3A
NPV 0x0B
OFFSET 0x4E
OR 0x25
PI 0x13
PMT 0x3B
POKE 0xB1
PPMT 0xA8
PRODUCT 0xB7
PROPER 0x72
PV 0x38
RAND 0x3F
RATE 0x3C
REFTEXT 0x92
REGISTER 0x95
RELREF 0x50
RENAME.COMMAND 0x9C
REPLACE 0x77
REPT 0x1E
REQUEST 0xB0
RESTART 0xB4
RESULT 0x60
RETURN 0x37
RIGHT 0x74
ROUND 0x1B
ROW 0x08
ROWS 0x4C
SEARCH 0x52
SECOND 0x49
SELECTION 0x5F
SERIES 0x5C
SET.NAME 0x58
SET.VALUE 0x6C
SHOW.BAR 0x9D
SIGN 0x1A
SIN 0x0F
SLN 0x8E
SQRT 0x14
STDEV 0x0C
STDEVP 0xC1
STEP 0x55
SUBSTITUTE 0x78
SUM 0x04
SYD 0x8F
T 0x82
TAN 0x11
TERMINATE 0xB3
TEXT 0x30
TEXTREF 0x93
TIME 0x42
TIMEVALUE 0x8D
TRANSPOSE 0x53
TREND 0x32
TRIM 0x76
TRUE 0x22
TRUNC 0xC5
TYPE 0x56
UPPER 0x71
VALUE 0x21
VAR 0x2E
VARP 0xC2
VLOOKUP 0x66
WEEKDAY 0x46
WHILE 0xAC
WINDOWS 0x5B
YEAR 0x45

Command Equivalent Function Table
---------------------------------
Here is a list of command equivalent functions sorted by index. The
Excel name for a comand equivalent function index is "icetab".
icetab's appear in ptgFuncCE tokens. Unused icetab's are reserved for
future use.

Command Equivalent icetab
------------------ ------
BEEP 0x00
OPEN 0x01
OPEN.LINKS 0x02
CLOSE.ALL 0x03
SAVE 0x04
SAVE.AS 0x05
FILE.DELETE 0x06
PAGE.SETUP 0x07
PRINT 0x08
PRINTER.SETUP 0x09
QUIT 0x0A
NEW.WINDOW 0x0B
ARRANGE.ALL 0x0C
SIZE 0x0D
MOVE 0x0E
FULL 0x0F
CLOSE 0x10
RUN 0x11
SET.PRINT.AREA 0x16
SET.PRINT.TITLES 0x17
SET.PAGE.BREAK 0x18
REMOVE.PAGE.BREAK 0x19
FONT 0x1A
DISPLAY 0x1B
PROTECT.DOCUMENT 0x1C
PRECISION 0x1D
A1.R1C1 0x1E
CALCULATE.NOW 0x1F
CALCULATION 0x20
DATA.FIND 0x22
EXTRACT 0x23
DATA.DELETE 0x24
SET.DATABASE 0x25
SET.CRITERIA 0x26
SORT 0x27
DATA.SERIES 0x28
TABLE 0x29
FORMAT.NUMBER 0x2A
ALIGNMENT 0x2B
STYLE 0x2C
BORDER 0x2D
CELL.PROTECTION 0x2E
COLUMN.WIDTH 0x2F
UNDO 0x30
CUT 0x31
COPY 0x32
PASTE 0x33
CLEAR 0x34
PASTE.SPECIAL 0x35
EDIT.DELETE 0x36
INSERT 0x37
FILL.RIGHT 0x38
FILL.DOWN 0x39
DEFINE.NAME 0x3D
CREATE.NAMES 0x3E
FORMULA.GOTO 0x3F
FORMULA.FIND 0x40
SELECT.LAST.CELL 0x41
SHOW.ACTIVE.CELL 0x42
GALLERY.AREA 0x43
GALLERY.BAR 0x44
GALLERY.COLUMN 0x45
GALLERY.LINE 0x46
GALLERY.PIE 0x47
GALLERY.SCATTER 0x48
COMBINATION 0x49
PREFERRED 0x4A
ADD.OVERLAY 0x4B
GRIDLINES 0x4C
SET.PREFERRED 0x4D
AXES 0x4E
LEGEND 0x4F
ATTACH.TEXT 0x50
ADD.ARROW 0x51
SELECT.CHART 0x52
SELECT.PLOT.AREA 0x53
PATTERNS 0x54
MAIN.CHART 0x55
OVERLAY 0x56
SCALE 0x57
FORMAT.LEGEND 0x58
FORMAT.TEXT 0x59
PARSE 0x5B
JUSTIFY 0x5C
HIDE 0x5D
UNHIDE 0x5E
WORKSPACE 0x5F
FORMULA 0x60
FORMULA.FILL 0x61
FORMULA.ARRAY 0x62
DATA.FIND.NEXT 0x63
DATA.FIND.PREV 0x64
FORMULA.FIND.NEXT 0x65
FORMULA.FIND.PREV 0x66
ACTIVATE 0x67
ACTIVATE.NEXT 0x68
ACTIVATE.PREV 0x69
UNLOCKED.NEXT 0x6A
UNLOCKED.PREV 0x6B
COPY.PICTURE 0x6C
SELECT 0x6D
DELETE.NAME 0x6E
DELETE.FORMAT 0x6F
VLINE 0x70
HLINE 0x71
VPAGE 0x72
HPAGE 0x73
VSCROLL 0x74
HSCROLL 0x75
ALERT 0x76
NEW 0x77
CANCEL.COPY 0x78
SHOW.CLIPBOARD 0x79
MESSAGE 0x7A
PASTE.LINK 0x7C
APP.ACTIVATE 0x7D
DELETE.ARROW 0x7E
ROW.HEIGHT 0x7F
FORMAT.MOVE 0x80
FORMAT.SIZE 0x81
FORMULA.REPLACE 0x82
SEND.KEYS 0x83
SELECT.SPECIAL 0x84
APPLY.NAMES 0x85
REPLACE.FONT 0x86
FREEZE.PANES 0x87
SHOW.INFO 0x88

SPLIT 0x89
ON.WINDOW 0x8A
ON.DATA 0x8B
DISABLE.INPUT 0x8C
LIST.NAMES 0x8F
FILE.CLOSE 0x90
SAVE.WORKSPACE 0x91
DATA.FORM 0x92
COPY.CHART 0x93
ON.TIME 0x94
WAIT 0x95
FORMAT.FONT 0x96
FILL.UP 0x97
FILL.LEFT 0x98
DELETE.OVERLAY 0x99
SHORT.MENUS 0x9B
CHANGE.LINK 0xA6
CALCULATE.DOCUMENT 0xA7
ON.KEY 0xA8
APP.RESTORE 0xA9
APP.MOVE 0xAA
APP.SIZE 0xAB
APP.MINIMIZE 0xAC
APP.MAXIMIZE 0xAD
MAIN.CHART.TYPE 0xB9
OVERLAY.CHART.TYPE 0xBA
SELECT.END 0xBB

Here is a list of command equivalent functions sorted
alphabetically by function name.

Command Equivalent icetab
------------------ ------
A1.R1C1 0x1E
ACTIVATE 0x67
ACTIVATE.NEXT 0x68
ACTIVATE.PREV 0x69
ADD.ARROW 0x51
ADD.OVERLAY 0x4B
ALERT 0x76
ALIGNMENT 0x2B
APPLY.NAMES 0x85
APP.ACTIVATE 0x7D
APP.MAXIMIZE 0xAD
APP.MINIMIZE 0xAC
APP.MOVE 0xAA
APP.RESTORE 0xA9
APP.SIZE 0xAB
ARRANGE.ALL 0x0C
ATTACH.TEXT 0x50
AXES 0x4E
BEEP 0x00
BORDER 0x2D
CALCULATE.DOCUMENT 0xA7
CALCULATE.NOW 0x1F
CALCULATION 0x20
CANCEL.COPY 0x78
CELL.PROTECTION 0x2E
CHANGE.LINK 0xA6
CLEAR 0x34
CLOSE 0x10
CLOSE.ALL 0x03
COLUMN.WIDTH 0x2F
COMBINATION 0x49
COPY 0x32
COPY.CHART 0x93
COPY.PICTURE 0x6C
CREATE.NAMES 0x3E
CUT 0x31
DATA.DELETE 0x24
DATA.FIND 0x22
DATA.FIND.NEXT 0x63
DATA.FIND.PREV 0x64
DATA.FORM 0x92
DATA.SERIES 0x28
DEFINE.NAME 0x3D
DELETE.ARROW 0x7E
DELETE.FORMAT 0x6F
DELETE.NAME 0x6E
DELETE.OVERLAY 0x99
DISABLE.INPUT 0x8C
DISPLAY 0x1B
EDIT.DELETE 0x36
EXTRACT 0x23
FILE.CLOSE 0x90
FILE.DELETE 0x06
FILL.DOWN 0x39
FILL.LEFT 0x98
FILL.RIGHT 0x38
FILL.UP 0x97
FONT 0x1A
FORMAT.FONT 0x96
FORMAT.LEGEND 0x58
FORMAT.MOVE 0x80
FORMAT.NUMBER 0x2A
FORMAT.SIZE 0x81
FORMAT.TEXT 0x59
FORMULA 0x60
FORMULA.ARRAY 0x62
FORMULA.FILL 0x61
FORMULA.FIND 0x40
FORMULA.FIND.NEXT 0x65
FORMULA.FIND.PREV 0x66
FORMULA.GOTO 0x3F
FORMULA.REPLACE 0x82
FREEZE.PANES 0x87
FULL 0x0F
GALLERY.AREA 0x43
GALLERY.BAR 0x44
GALLERY.COLUMN 0x45
GALLERY.LINE 0x46
GALLERY.PIE 0x47
GALLERY.SCATTER 0x48
GRIDLINES 0x4C
HIDE 0x5D
HLINE 0x71
HPAGE 0x73
HSCROLL 0x75
INSERT 0x37
JUSTIFY 0x5C
LEGEND 0x4F
LIST.NAMES 0x8F
MAIN.CHART 0x55
MAIN.CHART.TYPE 0xB9
MESSAGE 0x7A
MOVE 0x0E
NEW 0x77
NEW.WINDOW 0x0B
ON.DATA 0x8B
ON.KEY 0xA8
ON.TIME 0x94
ON.WINDOW 0x8A
OPEN 0x01
OPEN.LINKS 0x02
OVERLAY 0x56
OVERLAY.CHART.TYPE 0xBA
PAGE.SETUP 0x07
PARSE 0x5B
PASTE 0x33
PASTE.LINK 0x7C
PASTE.SPECIAL 0x35
PATTERNS 0x54
PRECISION 0x1D
PREFERRED 0x4A
PRINT 0x08
PRINTER.SETUP 0x09
PROTECT.DOCUMENT 0x1C
QUIT 0x0A
REMOVE.PAGE.BREAK 0x19
REPLACE.FONT 0x86
ROW.HEIGHT 0x7F
RUN 0x11
SAVE 0x04
SAVE.AS 0x05
SAVE.WORKSPACE 0x91
SCALE 0x57
SELECT 0x6D
SELECT.CHART 0x52
SELECT.END 0xBB
SELECT.LAST.CELL 0x41
SELECT.PLOT.AREA 0x53
SELECT.SPECIAL 0x84
SEND.KEYS 0x83
SET.CRITERIA 0x26
SET.DATABASE 0x25
SET.PAGE.BREAK 0x18
SET.PREFERRED 0x4D
SET.PRINT.AREA 0x16
SET.PRINT.TITLES 0x17
SHORT.MENUS 0x9B
SHOW.ACTIVE.CELL 0x42
SHOW.CLIPBOARD 0x79
SHOW.INFO 0x88
SIZE 0x0D
SORT 0x27
SPLIT 0x89
STYLE 0x2C
TABLE 0x29
UNDO 0x30
UNHIDE 0x5E
UNLOCKED.NEXT 0x6A
UNLOCKED.PREV 0x6B
VLINE 0x70
VPAGE 0x72
VSCROLL 0x74
WAIT 0x95
WORKSPACE 0x5F

List of Ptg's
-------------
Here is a list of all ptg's that appear in BIFF files. All other
ptg's are reserved for future use.

NamePtgType
-----------
ptgExp0x01control
ptgTbl0x02control
ptgAdd0x03operator
ptgSub0x04operator
ptgMul0x05operator
ptgDiv0x06operator
ptgPower0x07operator
ptgConcat0x08operator
ptgLT0x09operator
ptgLE0x0Aoperator
ptgEQ0x0Boperator
ptgGE0x0Coperator
ptgGT0x0Doperator
ptgNE0x0Eoperator
ptgIsect0x0Foperator
ptgUnion0x10operator
ptgRange0x11operator
ptgUplus0x12operator
ptgUminus0x13operator
ptgPercent0x14operator
ptgParen0x15control
ptgMissArg0x16operand
ptgStr0x17operand
ptgAttr 0x19control
ptgSheet0x1Acontrol
ptgEndSheet0x1Bcontrol
ptgErr0x1Coperand
ptgBool 0x1Doperand
ptgInt0x1Eoperand
ptgNum0x1Foperand
ptgArray0x20operand, reference class
ptgFunc 0x21operator
ptgFuncVar0x22operator
ptgName 0x23operand, reference class
ptgRef0x24operand, reference class
ptgArea 0x25operand, reference class
ptgMemArea0x26operand, reference class
ptgMemErr0x27operand, reference class
ptgMemNoMem0x28control
ptgMemFunc0x29control
ptgRefErr0x2Aoperand, reference class
ptgAreaErr0x2Boperand, reference class
ptgRefN 0x2Coperand, reference class
ptgAreaN0x2Doperand, reference class
ptgMemAreaN0x2Econtrol
ptgMemNoMemN0x2Fcontrol
ptgFuncCE0x38operator
ptgArrayV0x40operand, value class
ptgFuncV0x41operator
ptgFuncVarV0x42operator
ptgNameV0x43operand, value class
ptgRefV 0x44operand, value class
ptgAreaV0x45operand, value class
ptgMemAreaV0x46operand, value class
ptgMemErrV0x47operand, value class
ptgMemNoMemV0x48control
ptgMemFuncV0x49control
ptgRefErrV0x4Aoperand, value class
ptgAreaErrV0x4Boperand, value class
ptgRefNV0x4Coperand, value class
ptgAreaNV0x4Doperand, value class
ptgMemAreaNV0x4Econtrol
ptgMemNoMemNV0x4Fcontrol
ptgFuncCEV0x58operator
ptgArrayA0x60operand, array class
ptgFuncA0x61operator
ptgFuncVarA0x62operator
ptgNameA0x63operand, array class
ptgRefA 0x64operand, array class
ptgAreaA0x65operand, array class
ptgMemAreaA0x66operand, array class
ptgMemErrA0x67operand, array class
ptgMemNoMemA0x68control
ptgMemFuncA0x69control
ptgRefErrA0x6Aoperand, array class
ptgAreaErrA0x6Boperand, array class
ptgRefNA0x6Coperand, array class
ptgAreaNA0x6Doperand, array class
ptgMemAreaNA0x6Econtrol
ptgMemNoMemNA0x6Fcontrol
ptgFuncCEA0x78operator


 December 29, 2017  Add comments

Leave a Reply