CODEWITHSUNDEEP
c#vb.netexcelexcel-dnavba
Christopher Dermody-Lawrence
Christopher Dermody-Lawrence

Reputation: 112

Excel-DNA calling xll function from another async xll function (Exception from HRESULT: 0x800AC472)

I'm using excel-DNA to make the addins, one addin handles communication to an API and another handles the inputs and routes. The second addin needs to be separate as other addins use it to post to the same api, and it is used to hold information about the address and user.

In VBA i used Application.Run() to run other files functions so I assumed the ExcelDnaUtil.Application.run() worked the same way, which it did. The issue is that the async functions, when they are trying to use application.run when the user is entering in a cell or selecting multiple cell and they get an error 0x800AC472.

I tried to solve the problem by looping until the function was successful (example below) using the ideas from this thread HRESULT 800ac472 from set operations in Excel but the errors still come when someone opens a formatting or function wizard window.

Public Shared Function AsyncTest(a As Integer, b As Integer) As Object
    Return ExcelAsyncUtil.Run("AsyncTest", New Object() {a, b}, Function() SyncTest(a, b))
End Function

<MethodImpl(MethodImplOptions.Synchronized)>
Public Shared Function SyncTest(a As Integer, b As Integer) As Object
    Try
        Dim Package = String.Concat({"<Model><a>", a, "</a><b>", b, "</b></Model>"})

        Dim xlApp As Object
        xlApp = ExcelDnaUtil.Application
        Dim failed as boolean = false
        Do
            Try
                Return xlApp.Run("PostToApiXmlToJson", Package, "api/test/add")  

            Catch e As System.Runtime.InteropServices.COMException
                If e.ErrorCode = -2146777998 Then
                    failed = True
                    System.Threading.Thread.Sleep(10)
                End If
                Debug.Write(String.Concat({"ErrorCode: #### ", e.ErrorCode, " #### PostToAPI"}))
            End Try

            count = count + 1

            Loop Until failed = False Or count > 100

        Catch err As Exception
            Return err.Message
        End Try
    End Function

So is there a different way of calling a function from another xll?

Or is there a way of telling the function not to calculate until the function is free?

Or is there a way of telling Excel that this function hasn't finished calculating try again?

Does anyone need any more information to help?

Any help will be appreciated

Upvotes: 0

Views: 1151

Answers (1)

Govert
Govert

Reputation: 16907

When you run AsyncTest, the SyncTest will run on a ThreadPool thread (due to Task.Run). From there you are making a COM call from Excel.

As a rule, an Excel add-in should never call into Excel from any thread except the main calculation thread. The Excel COM object model is (basically) single-threaded, so all COM calls eventually have to execute on the main thread. Any call from another thread might fail with a COM error like the one you see.

Excel-DNA has a helper method that allows you to schedule a delegate to be run on the main Excel thread - you call ExcelAsyncUtil.QueueAsMacro(...). This will have the delegate run once Excel is ready, and both COM and C API calls in that context will work, since the delegate will run on the main thread in a macro context. ExcelAsyncUtil.QueueAsMacro can be called from any thread at any time.

Upvotes: 1

Related Questions