|Important||This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here.|
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.
This topic addresses issues of XLL compatibility in different versions of Microsoft Office Excel.
You should consider including definitions similar to these in your XLL project code and replacing all instances of literal numbers used in this context. This will greatly clarify code that is version specific, and reduce the likelihood of version-related bugs in the form of innocuous-looking numbers.
#define MAX_XL11_ROWS 65536 #define MAX_XL11_COLS 256 #define MAX_XL12_ROWS 1048576 #define MAX_XL12_COLS 16384 #define MAX_XL11_UDF_ARGS 30 #define MAX_XL12_UDF_ARGS 255 #define MAX_XL4_STR_LEN 255u #define MAX_XL12_STR_LEN 32767u
You should detect which version is running using Excel4(xlfGetWorkspace, &version, 1, &arg), where arg is a numeric XLOPER set to 2 and version is a string XLOPER which can then be coerced to an integer. For Microsoft Office Excel 2007, this is 12. You should do this in, or from, the xlAutoOpen function. You can then set a global variable that informs all of the modules in your project which version of Excel is running. Your code can then decide whether to call the C API using Excel12 and XLOPER12s, or using Excel4 using XLOPERs.
You can call XLCallVer to discover the C API version, but this does not indicate which of the pre-2007 versions you are running.
Consider an XLL function that takes a string and returns a value that can be any of the worksheet data types. In Excel 2003 and Excel 2007, you could export a function registered as type "PD" and prototyped as follows where the string is passed as a length-counted byte string.
LPXLOPER WINAPI my_xll_fn(unsigned char *arg);
Although this works perfectly well, there are several reasons why this is not the ideal interface to your code when you are running Excel 2007:
It is subject to the limitations of C API byte strings and cannot access the long Unicode strings supported in Excel.
Although Excel 2007 can pass and accept XLOPERs, internally it converts them to XLOPER12s, so there is an implicit conversion overhead in Excel 2007 that is not there when the code runs in Excel 2003.
It may be that this function can be made thread safe, but if the type string is changed to PD$, registration fails in Excel 2003.
For these reasons, ideally, you should export a function for your Excel 2007 users that was registered as QD%$, assuming your code is thread safe, and prototyped as follows.
LPXLOPER12 WINAPI my_xll_fn_v12(wchar_t *arg);
Another reason why you might want to register a different function when running Excel 2007 is that it permits XLL functions to take up to 255 arguments, instead of the 30 limit of Excel 2003.
Fortunately, you can have the benefits of both by exporting both versions from your project. You can then detect the running Excel version and conditionally register the most appropriate function. For more information and an example implementation, see Developing Add-ins (XLLs) in Excel 2007.
This approach leads to the possibility that a worksheet running in Excel 2003 could display different results than the same sheet running in Excel 2007. For example, Excel 2003 would map a Unicode string in an Excel 2003 worksheet cell to an ASCII byte-string and truncate it before passing it to an XLL function. Excel 2007 will pass an unconverted Unicode string to an XLL function registered in the right way. This could lead to a different result. You should be aware of this possibility and the consequences to your users, not just in the upgrade to Excel 2007. For example, some built-in numeric functions were improved between Excel 2000 and Excel 2003.
Analysis Toolpak (ATP) functions are part of Excel 2007. Previously, an XLL could only call an ATP function by using xlUDF. In Excel 2007 the ATP functions should be called using the function enumerations defined in xlcall.h. The example in Calling User-defined Functions from DLLs demonstrates the two different methods.