Excel C++ Automation Add-In UDF returning VT_ERROR variant

[Mike Ives]

I am trying to return a Variant containing a VT_ERROR from a UDF in an
Automation Add-in using ExcelXP and Excel2003. Whatever SCODE I give the
variant, the UDF results show up in Excel as a #VALUE! error.

My real UDF is written in C# and actually returns a two-dimensional
safearray. I am able to successfully return a mixture of element types in
this 2d array where they show up correctly in Excel: until any one of the
elements in the array is a VT_ERROR If there's a VT_ERROR in the returned
data then Excel shows #VALUE! in all cells in the array.

I've reduced this down to the fundamental issue, which is that I cannot
return a VT_ERROR variant from a C++ Automation add-in.

Given the following interface:

interface IMyTest : IDispatch
{
[id(1), helpstring("method MyMultiply")] HRESULT MyMultiply([in] double
a, [in] double b, [out, retval] double *product);
[id(2), helpstring("method MyFn")] HRESULT MyFn([out, retval] VARIANT
*pRetVal);
};

And the following VS6 C++ code implementing that interface:

STDMETHODIMP CMyTest::MyMultiply(double a, double b, double *product)
{
*product = a * b;
return S_OK;
}

STDMETHODIMP CMyTest::MyFn(VARIANT *pRetVal)
{
CComVariant v(2024, VT_ERROR);

::VariantInit(pRetVal);
v.Detach(pRetVal);

return S_OK;
}

The MyMultiply function works fine. But MyFn() yields #VALUE! in Excel even
though SCODE 2024 is #N/A.

If I use VBA for the function then I can get #N/A into the cell - in fact I
can have a disparate array of variants and they work fine even when one
element is a VT_ERROR. E.g. This XLA function works fine...

Function MyXlaFn()

Dim a(3)

a(0) = "Hello"
a(1) = 3
a(2) = CVErr(xlErrNA)

MyXlaFn = a

End Function

Can anyone shed any light on this please?

Mike