I've now created a sample code set.
/////////////////////////////////////////////////////////////////////////////
/*
The problem I am trying to demonstrate here is that when using the O% data
type introduced with
Excel 2007 the pointer to the array of doubles seems to be offset from the
correct location by 4 bytes,
or half the size of a double. The problem does not happen when using the O
data type, but the O data
type will not work with larger array sizes that are possible with Excel 2007
(Excel12)
I have written two worksheet functions to demonstrate the problem. Both
functions do the same
simple task of copying an input array of numbers to the output array. The
first is declared using the
O% data type plus a boolean to disable or enable my hacked solution. The
second simply uses the O
data type and requires no hacking.
I have followed the two worksheet functions with three helper functions to
make the DLL work
correctly as and XLL. First I have my own XLRegister12 function that takes a
series of strings as
parameters and registers a function with Excel. xlAutoOpen is en exported
function of the DLL that
Excel calls when it opens an XLL file. It uses the first function to
register the worksheet functions.
xlAutoFree12 is an exported function to make sure that memory I allocate in
the DLL is freed
properly in the DLL.
I have isolated this bit of code so that a project can be built simply to
demonstrate the problem. In
Visual Studio, create a MFC DLL project. Then add the code from here to the
end of this file to the
main CPP file. You will need to include the xlcall.cpp and xlcall.h and
xlcall32.lib files from the Excel
2007 SDK, which is available at (Excel 2007 SDK: Excel 2007 XLL Software
Development Kit)
http://www.microsoft.com/downloads/...D1-93AB-4BD4-AF18-CB6BB487E1C4&displaylang=en
*/
#include "xlcall.h"
/////////////////////////////////////////////////////////////////////////////
extern CString rgFuncs12[][9] =
{
// Worksheet Functions
{_T("Excel12CopyError"),_T("UO%A$"),_T("Excel12CopyError"),_T("Source
Array, Use Hack(true/false)"),_T("1"),_T("Error Demo"),_T(""),_T(""),_T("Copy
Numbers to demonstrate error")},
{_T("Excel12CopyFine"),_T("UO$"),_T("Excel12CopyFine"),_T("Source
Array"),_T("1"),_T("Error Demo"),_T(""),_T(""),_T("Copy Numbers to
demonstrate error")},
};
extern int rgFuncs12Rows = sizeof(rgFuncs12) / (sizeof(CString) * 9);
LPXLOPER12 __stdcall Excel12CopyError(signed int *iNumXLRowsA,signed int
*iNumXLColumnsA, double A[], short int UseHack)
{
#pragma comment(linker, "/EXPORT:"__FUNCTION__"="__FUNCDNAME__)
AFX_MANAGE_STATE(AfxGetStaticModuleState( )); // Very first thing I should
do on entering an MFC DLL.
XLOPER12 * rval = new XLOPER12;
rval->xltype = xltypeErr | xlbitDLLFree;// Do this now, in case we don't
produce any results.
rval->val.err = xlerrRef;
if ((*iNumXLRowsA > 0) && (*iNumXLColumnsA > 0))
{
// HACK: Total HACK!
// This is where I'm offsetting the pointer by four bytes.
if (UseHack == 1)
{
char * foo = (char *) A;
foo -= 4;
A = (double *) foo;
}
// HACK: End of total hack.
int NumRows = *iNumXLRowsA;
int NumCols = *iNumXLColumnsA;
int OutSize = NumRows * NumCols;
XLOPER12 * lpxData = new XLOPER12[OutSize];
if (lpxData != 0)
{
rval->xltype = xltypeMulti | xlbitDLLFree;
rval->val.array.rows = NumRows;
rval->val.array.columns = NumCols;
rval->val.array.lparray = lpxData;
for (int index = 0; index < OutSize; index++)
{
lpxData[index].xltype = xltypeNum;
lpxData[index].val.num = A[index];
}
}
}
return(rval);
}
/////////////////////////////////////////////////////////////////////////////
LPXLOPER12 __stdcall Excel12CopyFine(unsigned short int
*iNumXLRowsA,unsigned short int *iNumXLColumnsA, double A[])
{
#pragma comment(linker, "/EXPORT:"__FUNCTION__"="__FUNCDNAME__)
AFX_MANAGE_STATE(AfxGetStaticModuleState( )); // Very first thing I should
do on entering an MFC DLL.
XLOPER12 * rval = new XLOPER12;
rval->xltype = xltypeErr | xlbitDLLFree;// Do this now, in case we don't
produce any results.
rval->val.err = xlerrRef;
if ((*iNumXLRowsA > 0) && (*iNumXLColumnsA > 0))
{
int NumRows = *iNumXLRowsA;
int NumCols = *iNumXLColumnsA;
int OutSize = NumRows * NumCols;
XLOPER12 * lpxData = new XLOPER12[OutSize];
if (lpxData != 0)
{
rval->xltype = xltypeMulti | xlbitDLLFree;
rval->val.array.rows = NumRows;
rval->val.array.columns = NumCols;
rval->val.array.lparray = lpxData;
for (int index = 0; index < OutSize; index++)
{
lpxData[index].xltype = xltypeNum;
lpxData[index].val.num = A[index];
}
}
}
return(rval);
}
/////////////////////////////////////////////////////////////////////////////
LPXLOPER12 XLRegister12(const CString Procedure,const CString TypeText,const
CString FunctionName,const CString FunctionArgs,const CString MacroType,const
CString Category,const CString Shortcut,const CString help_topic = _T(""),
const CString function_help = _T(""))
{
static XLOPER12 Result;
Excel12(xlGetName, (LPXLOPER12)&Result, 0);
CStringW csModuleText(Result.val.str, Result.val.str[0] + 1);
Excel12(xlFree, 0, 1, (LPXLOPER12)&Result);
XLOPER12 xModuleText;
xModuleText.xltype = xltypeStr;
xModuleText.val.str = (XCHAR *) csModuleText.GetString();
CStringW csProcedure(Procedure);
csProcedure.Insert( 0, csProcedure.GetLength());
XLOPER12 xProcedure;
xProcedure.xltype = xltypeStr;
xProcedure.val.str = (XCHAR *) csProcedure.GetString();
CStringW csTypeText(TypeText);
csTypeText.Insert( 0, csTypeText.GetLength());
XLOPER12 xTypeText;
xTypeText.xltype = xltypeStr;
xTypeText.val.str = (XCHAR *) csTypeText.GetString();
CStringW csFunctionText(FunctionName);
csFunctionText.Insert( 0, csFunctionText.GetLength());
XLOPER12 xFunctionText;
xFunctionText.xltype = xltypeStr;
xFunctionText.val.str = (XCHAR *) csFunctionText.GetString();
XLOPER12 xArgumentText;
xArgumentText.xltype = xltypeMissing;
CStringW csArgumentText(FunctionArgs);
if (!FunctionArgs.IsEmpty())
{
csArgumentText.Insert( 0, csArgumentText.GetLength());
xArgumentText.xltype = xltypeStr;
xArgumentText.val.str = (XCHAR *) csArgumentText.GetString();
}
XLOPER12 xMacroType;
xMacroType.xltype = xltypeMissing;
CStringW csMacroType(MacroType);
if (!MacroType.IsEmpty())
{
csMacroType.Insert( 0, csArgumentText.GetLength());
xMacroType.xltype = xltypeStr;
xMacroType.val.str = (XCHAR *) csMacroType.GetString();
}
XLOPER12 xCategory;
xCategory.xltype = xltypeMissing;
CStringW csCategory(Category);
if (!Category.IsEmpty())
{
csCategory.Insert( 0, csCategory.GetLength());
xCategory.xltype = xltypeStr;
xCategory.val.str = (XCHAR *) csCategory.GetString();
}
XLOPER12 xShortcutText;
xShortcutText.xltype = xltypeMissing;
CStringW csShortcutText(Shortcut);
if (!Shortcut.IsEmpty())
{
csShortcutText.Insert( 0, csShortcutText.GetLength());
xShortcutText.xltype = xltypeStr;
xShortcutText.val.str = (XCHAR *) csShortcutText.GetString();
}
XLOPER12 x_help_topic;
x_help_topic.xltype = xltypeMissing;
CStringW cs_help_topic(help_topic);
if (!help_topic.IsEmpty())
{
cs_help_topic.Insert( 0, cs_help_topic.GetLength());
x_help_topic.xltype = xltypeStr;
x_help_topic.val.str = (XCHAR *) cs_help_topic.GetString();
}
XLOPER12 x_function_help;
x_function_help.xltype = xltypeMissing;
CStringW cs_function_help(function_help);
if (!function_help.IsEmpty())
{
cs_function_help.Insert( 0, cs_function_help.GetLength());
x_function_help.xltype = xltypeStr;
x_function_help.val.str = (XCHAR *) cs_function_help.GetString();
}
Excel12(xlfRegister, &Result,10,
&xModuleText, &xProcedure,
&xTypeText, &xFunctionText,
&xArgumentText,&xMacroType,
&xCategory, &xShortcutText,
&x_help_topic, &x_function_help);
return(&Result);
}
/////////////////////////////////////////////////////////////////////////////
int __stdcall xlAutoOpen(void)
{
#pragma comment(linker, "/EXPORT:"__FUNCTION__"="__FUNCDNAME__)
AFX_MANAGE_STATE(AfxGetStaticModuleState());
int version = XLCallVer(); // This returns 0x0c00 in Excel version 12
if (version >= 0x0c00)
{
LPXLOPER12 rval;
int count = 0;
for (int i=0; i<rgFuncs12Rows; i++)
{
rval = XLRegister12(rgFuncs12
[0], rgFuncs12[1], rgFuncs12[2],
rgFuncs12[3], rgFuncs12[4], rgFuncs12[5], rgFuncs12[6],
rgFuncs12[7], rgFuncs12[8]);
if (rval->xltype != 1) count++;
}
}
return(1);
}
/////////////////////////////////////////////////////////////////////////////
void WINAPI xlAutoFree12(LPXLOPER12 p_oper)
{
#pragma comment(linker, "/EXPORT:"__FUNCTION__"="__FUNCDNAME__)
AFX_MANAGE_STATE(AfxGetStaticModuleState());
switch(p_oper->xltype)
{
case xltypeStr:
case xltypeStr | xlbitDLLFree:
delete[] p_oper->val.str;
break;
case xltypeRef:
case xltypeRef | xlbitDLLFree:
delete[] p_oper->val.mref.lpmref;
break;
case xltypeMulti:
case xltypeMulti | xlbitDLLFree:
// TODO: This should be modified to loop and check all of the individual
values
for (int index = 0; index < (p_oper->val.array.rows *
p_oper->val.array.columns); index++)
if (p_oper->val.array.lparray[index].xltype == xltypeStr)
delete[] p_oper->val.array.lparray[index].val.str;
delete[] p_oper->val.array.lparray; // for allocated memory.
break;
case xltypeBigData:
case xltypeBigData | xlbitDLLFree:
delete[] p_oper->val.bigdata.h.lpbData;
break;
}
delete p_oper;
}
/////////////////////////////////////////////////////////////////////////////