Data Types Used by Excel

  • Article

Applies to: Excel 2010 | Office 2010 | VBA | Visual Studio

In this article
ANSI C/C++ Types
Excel-Specific Data Structures
Registration Data Type Codes

Microsoft 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

Starting in Excel 2007:

  • 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.

Starting in Excel 2007:

  • FP12 – a two-dimensional floating-point array structure supporting all the rows and columns starting 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 (starting in Excel 2007), 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 (starting in Excel 2007) 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 *
(starting in Excel 2007)

C%, F%

Null-terminated Unicode wide character string.

unsigned short *
(starting in Excel 2007)

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:

  1. short int *rows

  2. short int *columns

  3. double *array

Array

(starting in Excel 2007)

O%

Passed as three arguments by reference:

  1. int *rows

  2. int *columns

  3. double *array

FP

K

Floating-point array structure.

FP12

(starting in Excel 2007)

K%

Large grid floating-point array structure.

XLOPER

P

Variable-type worksheet values and arrays.

R

Values, arrays, and range references.

XLOPER12

(starting in Excel 2007)

Q

Variable-type worksheet values and arrays.

U

Values, arrays, and range references.

The types C%, F%, D%, G%, K%, O%, Q, and U were all new in Microsoft 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 2010 XLL SDK API Function Reference