[SOLVED] Migrated Excel add-in invoke of XLL UDF receives HRESULT on error instead of Error

Migrated Excel add-in invoke of XLL UDF receives HRESULT on error instead of Error

I am migrating a VB6 Excel COM add-in to VB.NET. I have encountered a problem where the add-in calls Excel to invoke a UDF we have implemented in an XLL (written in C++ with the Excel 2013 SDK), where Excel returns a 32-bit integer containing 0x800A07DF (FACILITY_CONTROL, error 2015) instead of an Error.

Our VB6 Excel COM add-in has the following function:

' VB6 version of function.
Public Function GetValueFromSheet(ByVal szName As String, _
                            ByRef szValue As String) As Boolean
    Dim Val As Variant
    On Error GoTo GetValueFromSheetHandler

    Val = xlApp.run("XLLUDF", szName)

    ' If the UDF encountered an error, Val will contain an Error.
    If VarType(Val) = vbError Then
        GoTo GetValueFromSheetHandler
    Else
        szValue = Val
    End If

    GetValueFromSheet = True
    Exit Function

GetValueFromSheetHandler:
        GetValueFromSheet = False
        szValue = vbNullString
        Err.Clear
End Function

' VB.NET version of function.
Public Function GetValueFromSheet(ByVal sName As String, ByRef sValue As String) As Boolean
        Dim val As Object
        On Error GoTo GetValueFromSheetHandler

        val = xlApp.Run("XLLUDF", sName)

        ' If the UDF encountered an error, Val will contain an integer 
        ' representing an HRESULT (FACILITY_CONTROL, error 2015), and 
        ' the line below never evaluates to True, breaking the logic.
        If VarType(val) = VariantType.Error Then
            GoTo GetValueFromSheetHandler
        Else                
            sValue = val
        End If

        ' Even though "OURXLLUDF" returned an error, we're returning success. 
        GetValueFromSheet = True
        Exit Function

GetValueFromSheetHandler:
        GetValueFromSheet = False
        sValue = String.Empty
        Err.Clear()
    End Function

The logic is now broken: the function is returning True, even when the XLL call returned an Error. For some reason, the Error is getting discarded in favor of an HRESULT.

What can we do to mitigate this, and determine when the XLL call encountered an error?

Solution

This behavior is by design, as stated in the official documentation : Marshaling Variant to Object

The following table identifies each variant type and the corresponding object type that the marshaler creates when a variant is passed from COM to the .NET Framework.

...

VT_ERROR => System.UInt32

...

So you can either check the result is of type integer (I have not tested but from your comment it seems the type is in fact System.Int32) with this special error code (-2146826273), or you change the logic of your code and return another type of VARIANT that you'll be able to test.