roland kapl
roland kapl

Reputation: 73

VBA Code triggering UDF that utilizes ExcelAsyncUtil.QueueAsMacro results in Application Error

I'm using ExcelDNA for functions that utilize ExcelAsyncUtil.QueueAsMacro to modify worksheet content, for example the database fetching function below.

Public Function DBRowFetch(<ExcelArgument(Description:="query for getting data")> Query As Object,
                           <ExcelArgument(Description:="connection string defining DB, user, etc...")> ConnString As Object,
                           <ExcelArgument(Description:="Range to put the data into", AllowReference:=True)> ParamArray targetArray() As Object) As String

    ' ... code to prepare below invocation

    ExcelAsyncUtil.QueueAsMacro(
        Sub()
            DBRowFetchAction(callID, CStr(Query), caller, finalTargetArray, CStr(ConnString), HeaderInfo)
        End Sub)

End Function

Public Sub DBRowFetchAction(callID As String, Query As String, caller As Excel.Range, targetArray As Object, ConnString As String, HeaderInfo As Boolean)

    ' ... code actually modifying worksheet content

End Function

When such a function is triggered by a cell modification done by VBA code, other content set and required by the VBA code is not available (e.g. XML data imported into a list object). This content doesn't have any relation to the function, it's only a synchronization issue.

I was wondering if there is any ExcelDNA property or function tag that I can set to prevent this behaviour.

A minimal working example might be:

<ExcelFunction(Description:="TestFunc, sets TextToSet into active sheet range A1")>
Public Function TestFunc(TextToSet As String) As String
    ExcelAsyncUtil.QueueAsMacro(Sub()
                                    TestAction(TextToSet)
                                End Sub)
    TestFunc = "called TestAction with " + TextToSet
End Function

Public Sub TestAction(TextToSet As String)
    ExcelDnaUtil.Application.ActiveSheet.Range("A1").Value = TextToSet
End Sub

Upvotes: 0

Views: 36

Answers (0)

Related Questions