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?