A
andyr88
Guest
In the process of migrating our C++ Excel add-in from a 32-bit to 64-bit build I've noticed some odd behaviour with functions that return a VARIANT data type.
For example, we might typically have a declaration like this in the addin VBA wrapper declaring the entry point into the XLL:
Public Declare PtrSafe Function Foo Lib "addin.xll" Alias "xlFoo" () As Variant
and then in the C++ source for the XLL we would have something like this:
VARIANT __declspec(dllexport) __stdcall xlFoo()
{
int result = calc_foo_int();
VARIANT ret;
VariantInit(&ret);
V_VT(&ret) = VT_I4;
V_I4(&ret) = result;
return ret;
}
Now, in the 32-bit world, everything works just fine. We can call this function in a VBA script or in an Excel sheet cell and we will get back the result we were expecting every time.
In the 64-bit world, however, things aren't so great. We can actually call this function just fine inside a VBA script and it will behave normally, but if we try calling it directly from an Excel cell entry, i.e. by typing:
=Foo()
Excel crashes and asks to restart!
I have searched high and low, but I can't find anything anywhere stating that there is any special other way of handling VARIANT return types in 64-bit Excel or that this is somehow problematic and should be avoided (despite being fine in the 32-bit world).
The closest thing I've seen described is in this article, which seems to imply (oddly) that VARIANT return types are not supported in 64-bit Office callback functions:
You must declare the return type explicitly in a VBA macro that you run in a 64-bit environment
So my best hunch is that maybe for the same reason VARIANT return types are not supported by 64-bit Excel addins either, at least as far as being able to be called from cells within a spreadsheet?
Is anyone able to confirm or deny that this is the case or perhaps shed some further light on the issue?
(oh, I should probably point out that I realise using a VARIANT return type for a simple integer value in the case above is overkill - it's just meant to be a simple demonstration case - some of our return types are complex arrays of mixed data types and do warrant a VARIANT return type)
Continue reading...
For example, we might typically have a declaration like this in the addin VBA wrapper declaring the entry point into the XLL:
Public Declare PtrSafe Function Foo Lib "addin.xll" Alias "xlFoo" () As Variant
and then in the C++ source for the XLL we would have something like this:
VARIANT __declspec(dllexport) __stdcall xlFoo()
{
int result = calc_foo_int();
VARIANT ret;
VariantInit(&ret);
V_VT(&ret) = VT_I4;
V_I4(&ret) = result;
return ret;
}
Now, in the 32-bit world, everything works just fine. We can call this function in a VBA script or in an Excel sheet cell and we will get back the result we were expecting every time.
In the 64-bit world, however, things aren't so great. We can actually call this function just fine inside a VBA script and it will behave normally, but if we try calling it directly from an Excel cell entry, i.e. by typing:
=Foo()
Excel crashes and asks to restart!
I have searched high and low, but I can't find anything anywhere stating that there is any special other way of handling VARIANT return types in 64-bit Excel or that this is somehow problematic and should be avoided (despite being fine in the 32-bit world).
The closest thing I've seen described is in this article, which seems to imply (oddly) that VARIANT return types are not supported in 64-bit Office callback functions:
You must declare the return type explicitly in a VBA macro that you run in a 64-bit environment
So my best hunch is that maybe for the same reason VARIANT return types are not supported by 64-bit Excel addins either, at least as far as being able to be called from cells within a spreadsheet?
Is anyone able to confirm or deny that this is the case or perhaps shed some further light on the issue?
(oh, I should probably point out that I realise using a VARIANT return type for a simple integer value in the case above is overkill - it's just meant to be a simple demonstration case - some of our return types are complex arrays of mixed data types and do warrant a VARIANT return type)
Continue reading...