Empty Variant and #NUM! Error

[tarfed]

Hi everyone

implementing a function that query a db, I discovered that returning an Object set to Nothing results in a #NUM! error. If I returns an Object() with any element set to Nothing, I get Empty Variants in VBA and zeros on a worksheet. In the example code below, the function EmptyTestX returns the error, while EmptyTestZ returns an array of one Empty Variant.

Looking around in the ExcelDNA source tree, I noticed some comments in the file XlCustomMarshal.cs (ExcelDna.Loader), but I'm not able to fully understand the code

  // DOCUMENT: A null pointer is immediately returned to Excel, resulting in #NUM!

Is there any way to return a scalar Empty Variant ?

many thanks

<DnaLibrary Language="VB">

<![CDATA[

Public Module EmptyTest

  Public Function EmptyTestX(ByVal trigger As Object) As Object

    Dim out As Object = Nothing

    Return out

  End Function

  Public Function EmptyTestZ(ByVal trigger As Object) As Object

    Dim out(0) As Object

    out(0) = Nothing

    Return out

  End Function

End Module

]]>

</DnaLibrary>

[Govert van Drimmelen]

Hi Federico,

You can try to return ExcelEmpty.Value from the scalar function, but that might not give you the expected result on the worksheet.

For a UDF called from a worksheet formula, Excel will never store the resulting value as "Empty". (This is independent of the technology you use to implement the UDF.)

So you need to decide what to return from your UDF:

* An empty string

* An error value (e.g. ExcelError.ExcelErrorValue)

* ExcelEmpty.Value - which Excel will then convert to some other value on the worksheet.

If you return "Nothing" (null) from a UDF, the Excel-DNA marshaling is not called for the resulting value, but Excel always interprets the "Nothing" (null) value as ExcelError.ExcelErrorNA. We have not control over that.

If you return "Nothing" (null) for an element in a result array, then Excel-DNA does convert the value to an Excel "Empty" type (equivalent to setting it to ExcelEmpty.Value), but Excel will convert the value to something else (since Excel can't store "Empty" as the result of a function). So to have consistent results for scalar and array functions where you control the result, you could return ExcelEmpty.Value in all cases.

Now the conversion from a UDF result into VBA might behave a bit differently, but is still not under control of Excel-DNA - if you call Application.Run("MyUDF", param1) then the result is first interpreted by Excel, then converted to a variant in VBA. You're saying you get the Empty variant in VBA in this case, but it is outside the control of Excel-DNA how the values are interpreted. You can try to return ExcelEmpty.Value from the scalar UDF to see whether Excel makes the same conversions.

Regards,

Govert

[tarfed]

ExcelEmpty.Value is exactly what I was searching for. My fault not to have read through the documentation.

Thank you very much Govert for your prompt answer.