ExcelDnaUtil loses Application Object in Excel 2013

May 20, 2014 at 1:42 PM
I have a VSTO application level addin that uses an Excel-DNA addin for UDF's.

In Excel 2013, if I open multiple workbooks, then close the original workbook, I would like to "re-point" my addin to the next available workbook. However, subsequent calls to the Excel-DNA add in will throw an exception trying to access ExcelDnaUtil.Application. The Application object is Nothing.

I would have thought the application object would not be tied to the workbook, but it seems like that is what is happening. Any thoughts appreciated.

Rick M.
Coordinator
May 20, 2014 at 11:18 PM
Hi Rick,

There were such problems in earlier versions of Excel-DNA, but I don't expect that anymore in Excel-DNA 0.32.
Could you perhaps confirm which version you are using, and whether the problem occurs with the current Excel-DNA version?
If so, I'm happy to look further - it did get a lot harder under Excel 2013 to keep the Application object straight, but you are correct that ExcelDnaUtil.Application should never fail or return 'Nothing'.

Regards,
Govert
May 22, 2014 at 2:25 PM
Hi Govert,

Thanks for the response. I was on 0.30. I upgraded to Excel-DNA 0.32 and still having the same problem. However, we have decided to dump VSTO and use Excel-DNA exclusively so let's see what happens with the re-architecture.

Rick M
Coordinator
May 22, 2014 at 2:36 PM
Hi Rick,

Are you perhaps calling Marshal.ReleaseComObject or Marshal.FinalReleaseComObject on the Application object? That could cause trouble.

If not that, I'd like to try to investigate the problem a bit further.
Would it be possible for you to send me something that reproduces this?

Thanks,
Govert
May 22, 2014 at 5:19 PM
Hi Govert,

I am not calling Marshall.ReleaseComObject on the application object.

Below is a pared-down version of the code. For the first workbook, it does fine. If you open another workbook, then close the first one, you get the error trying to create a range from the application object.

I have not tried this particular piece of code in its current condition so I don't know if it will work for you but it's the code I'm using minus a lot of repetitive stuff.

The UDF takes parameters that can be either cell values or cell references (addresses) but I don't know which they are until runtime. If it is an address, I need to create a range from that address and get the value(s) into a comma-delimited string. Please let me know if this is inadequate.

Thanks,

Rick

<ExcelFunction(Description:="Search Function", ISMACROTYPE:=True)>
Shared Function MyUDF(<ExcelArgument(AllowReference:=True)> Optional ByVal Arg1 As Object = Nothing, _
                                 <ExcelArgument(AllowReference:=True)> Optional ByVal Arg2 As Object = Nothing, _
                                 <ExcelArgument(AllowReference:=True)> Optional ByVal Arg3 As Object = Nothing) 
                                 As Object

        'Keeps from recalculating constantly
        XlCall.Excel(XlCall.xlfVolatile, False)

        Dim myResult As Object = Nothing
        Dim RangeIsTable As Boolean = False

        Try
            CheckArgument(Arg1)
            CheckArgument(Arg2)
            CheckArgument(Arg3)            
        Catch ex As Exception
           Return ex.Message           
        End Try

'Create parameter values to pass to the function
Try
            Dim range
            If TypeName(Arg) = "ExcelReference" Then
                range = ReferenceToRange(Arg)
            ElseIf TypeName(Arg) = "String" Then
                 range = StringToRange(Arg)
            End If

            Dim resultString As String = ""

            For r = 1 To range.Count
                resultString += range(r).value & ","
            Next

            resultString = resultString.Substring(0, resultString.Length - 1)
            Arg = resultString
            Return Arg
        Catch ex As Exception
            MsgBox("Error: " & ex.Message)
        End Try

        MyResult = VSTOFuncs.SearchFunc(Arg1, Arg2, Arg3)
        Return myResult

End Function
    
Private Shared Function CheckArgument(ByRef Arg As Object) As Object

        If TypeOf Arg Is ExcelError Then Throw New Exception("#PARAMETER")
        If TypeOf Arg Is ExcelEmpty Or TypeOf Arg Is ExcelMissing Then Return Nothing

        Try
            Dim range
            
       If TypeName(Arg) = "ExcelReference" Then
                range = ReferenceToRange(Arg)
            ElseIf TypeName(Arg) = "String" Then
                'Might be a range address;try to create a range from it
                range = StringToRange(Arg)
            End If

            Dim resultString As String = ""

            For r = 1 To range.Count
                resultString += range(r).value & ","
            Next

            resultString = resultString.Substring(0, resultString.Length - 1)
            Arg = resultString
            Return Arg
        Catch ex As Exception
         MsgBox("Error: " & ex.Message)
        End Try
End Function

Private Shared Function ReferenceToRange(ByVal xlRef As ExcelReference) As Object
    Dim strAddress = XlCall.Excel(XlCall.xlfReftext, xlRef, True)
        
__  '*** Here is where the error occurs: "Object reference not set to an instance of an object"
    '*** But only after the initial workbook is closed (Excel 2013).__
        ReferenceToRange = ExcelDnaUtil.Application.Range(strAddress)
End Function    

Private Shared Function StringToRange(ByVal xlRef As String) As Object
        StringToRange = ExcelDnaUtil.Application.Range(xlRef)
End Function
Coordinator
May 23, 2014 at 10:05 AM
HI Rick,

OK - I'll have a look and get back to you.

-Govert
Coordinator
May 23, 2014 at 9:43 PM
Edited May 23, 2014 at 9:44 PM
Hi Rick,

I've tried your code, with minor fix-ups, and am not able to reproduce the problem.
Some possibilities:
  • You are still running the older version of Excel-DNA (you might have updated the ExcelDna.Integration.dll reference, but might still be using the old ExcelDna.xll file.) Could you check that you have the right .xll file, and that there is no ExcelDna.Integration.dll file in your output directory (the reference should be marked as Copy Local=false)?
  • The call to VSTOFunc.SearchFunc causes problems. I had to take this call out for my test - I presume it is some internal library.
I suggest you start a new "Class Library" project, then use the "Excel-DNA" NuGet package to turn it into an Excel add-in. Then start with the simplest function that uses ExcelDnaUtil.Application, maybe just:
    Shared Function GetExcelVersion() As String
        Return ExcelDnaUtil.Application.Version
    End Function
And then see if you can recreate the problem with this simple add-in.


Let me know if you are able to make any progress.

Regards,
Govert
May 27, 2014 at 7:44 PM
Thanks for the code. That worked as expected.

Also, I just finished disconnecting my code from VSTO (which was non-trivial) and it seems to work in the new code as well. During that time, I re-copied the files from the ExcleDna-0.32 folder just to be sure. My guess is that, earlier, I must have moved the ExcelDna.Integration.dll and failed to move over the new xll's when I went to 0.32. I'll let you know if anything else comes up, but I think this was my mistake.

Rick
Coordinator
May 27, 2014 at 7:56 PM
Hi Rick,

I'm glad it looks better now.
It was quite a story getting it to work right in Excel 2013, and I'd be disappointed if there were still an issue....

Good luck with your add-in.

-Govert