Data Types Used by Excel

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Microsoft Office Excel exchanges several ANSI C/C++ types and also some Excel-specific data structures. These are mentioned here to provide a context for other sections, and they are discussed in detail in the xlfRegister (Form 1) topic.

ANSI C/C++ Types

Numbers

All versions of Excel:

• 8-byte double

• [signed] short [int] – used for Boolean values and also integers

• unsigned short [int]

• [signed long] int

Strings

All versions of Excel:

• [signed] char * – null-terminated byte strings of up to 255 characters

• unsigned char * – length-counted byte strings of up to 255 characters

Excel 2007+ only:

• unsigned short * – Unicode strings of up to 32,767 characters, which can be null-terminated or length-counted

All worksheet numbers in Excel are stored as doubles so that it is not necessary (and in fact introduces a small conversion overhead) to declare add-in functions as exchanging integer types with Excel.

Where you are using integer types, Excel verifies that the inputs are within the limits of the type, and they fail with #NUM! if outside these. The exception is when you are registering a function to take a Boolean argument, implemented using short int. In this case, any non-zero input is converted to 1, and zero is passed straight through.

Excel-Specific Data Structures

All versions of Excel:

• FP – a two-dimensional floating-point array structure supporting up to 65,356 rows by the maximum number columns supported in the given version of Excel.

• XLOPER – a multi-type data structure that can represent all the worksheet data types (including errors), integers, range references, XLM macro sheet flow control types, and an internal binary storage data type.

  Note
  Strings are represented as length-counted byte strings of up to 255 characters length.

Excel 2007+ only:

• FP12 – a two-dimensional floating-point array structure supporting all the rows and columns in Excel 2007.

• XLOPER12 – a multi-type data structure that can represent all the worksheet data types (including errors), integers, range references, XLM macro sheet flow control types, and an internal binary storage data type.

  Note
  Strings are represented as length-counted Unicode strings of up to 32,767 characters long.

Registration Data Type Codes

XLL functions are registered using the C API function xlfRegister, which takes as its third argument a string of letters that encode the return and argument types. This string also contains the information that tells Excel whether the function is volatile, is thread-safe (Excel 2007 only), is macro sheet equivalent, and whether it returns its result by modifying an argument in place.

The following table is reproduced and discussed in more detail in the xlfRegister (Form 1) topic. It is reproduced here in order to provide a context for the rest of this section. For example, a function that takes a length-counted Unicode string (Excel 2007 only) could be described as taking a type C% argument.

Data type	Pass by value	Pass by ref (pointer)	Comments
Boolean	A	L	short (0=false or 1=true)
double	B	E
char *		C, F	Null-terminated ASCII byte string.
unsigned char *		D, G	Length -counted ASCII byte string.
unsigned short * (Excel 2007 only)		C%, F%	Null-terminated Unicode wide character string.
unsigned short * (Excel 2007 only)		D%, G%	Length-counted Unicode wide character string.
unsigned short [int]	H		WORD
[signed] short [int]	I	M	16-bit
[signed long] int	J	N	32-bit
Array		O	Passed as three arguments by reference: short int *rows short int *columns double *array
Array (Excel 2007 only)		O%	Passed as three arguments by reference: int *rows int *columns double *array
FP		K	Floating-point array structure.
FP12 (Excel 2007 only)		K%	Large grid floating-point array structure.
XLOPER		P	Variable-type worksheet values and arrays.
		R	Values, arrays, and range references.
XLOPER12 (Excel 2007 only)		Q	Variable-type worksheet values and arrays.
		U	Values, arrays, and range references.

The types C%, F%, D%, G%, K%, O%, Q, and U are all new in Office Excel 2007 and are not supported in earlier versions. The string types F, F%, G, and G% are used for arguments that are modified-in-place. When XLOPER or XLOPER12 arguments are registered as types P or Q respectively, Excel converts single-cell references to simple values and multi-cell references to arrays when it prepares them.

P and Q types always arrive in your function as one of the following types: xltypeNum, xltypeStr, xltypeBool, xltypeErr, xltypeMulti, xltypeMissing, or xltypeNil, but not xltypeRef or xltypeSRef because these are always dereferenced.

Type O, which is really three arguments on the stack, was introduced for compatibility with Fortran DLLs where arguments are passed by reference. It cannot be used to return a value except by declaring the argument as a modify-in-place return value and placing the results in the referenced values. Type O% extends type O in Excel 2007 so that it can access arrays that cover areas larger than the Office Excel 2003 grid.

See Also

Reference

xlfRegister (Form 1)

Concepts

Excel Programming Concepts

Excel 2007 XLL SDK API Function Reference