M
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
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