How to not hang Excel?

Question

I have this problem where my volatile, macro type UDF that sets the value of a couple of cells next to the calling cell works perfectly unless used twice (or more) in the sheet: the Excel status bar starts flashing "Calculating...".

I't not a real hang but an heavy load of recalculations: the counter in the example function are drawn without the choppiness of actual hangs.

Also I am not sure about using a volatile UDF, as I need to have automatic recalculation, but only on input changes, rather than constant polling.

I did produce an example module with three functions:

• VolatileNyanCat - Similar to my UDF: a single SetValue call. Excel hangs if used two times.
• NonVolatileOneNyanCat - As above, but not volatile. Doesn't hang, but I need auto-recalculation.
• HangNyanCat - It calls SetValue two times on two different ranges. Excel hangs.

I think that this has to do with how ExcelAsyncUtil.QueueAsMacro works and with thread safety, but I'm baffled here.

Imports ExcelDna.Integration
Imports ExcelDna.Integration.XlCall

Public Module Example

    <ExcelFunction(IsMacroType:=True, IsVolatile:=True)>
    Public Function VolatileNyanCat() As String

        Dim caller = CType(XlCall.Excel(XlCall.xlfCaller), ExcelReference)
        Dim NyanCat(,) As String = {{"Nyan", "Cat"}}

        Dim nc As New ExcelReference(caller.RowFirst + 1, caller.RowLast + 1,
                                     caller.ColumnFirst, caller.ColumnLast + 1,
                                     caller.SheetId)

        Static nyy As Integer = 1

        ExcelAsyncUtil.QueueAsMacro(Sub()
                                        nyy += 1
                                        nc.SetValue(NyanCat)
                                    End Sub)
        Return "NYA! x " + CStr(nyy)
    End Function

    <ExcelFunction(IsMacroType:=True)>
    Public Function NonVolatileOneNyanCat() As String

        Dim caller = CType(XlCall.Excel(XlCall.xlfCaller), ExcelReference)
        Dim NyanCat(,) As String = {{"Nyan", "Cat"}}

        Dim nc As New ExcelReference(caller.RowFirst + 1, caller.RowLast + 1,
                                     caller.ColumnFirst, caller.ColumnLast + 1,
                                     caller.SheetId)

        Static nyy As Integer = 1

        ExcelAsyncUtil.QueueAsMacro(Sub()
                                        nyy += 1
                                        nc.SetValue(NyanCat)
                                    End Sub)
        Return "NYA! x " + CStr(nyy)
    End Function

    <ExcelFunction(IsMacroType:=True, IsVolatile:=True)>
    Public Function HangNyanCat() As String

        Dim caller = CType(XlCall.Excel(XlCall.xlfCaller), ExcelReference)
        Dim nyan(,) As String = {{"Nyan"}} : Dim cat(,) As String = {{"Cat"}}

        Dim n As New ExcelReference(caller.RowFirst + 1, caller.RowLast + 1,
                                    caller.ColumnFirst, caller.ColumnLast,
                                    caller.SheetId)

        Dim c As New ExcelReference(caller.RowFirst + 1, caller.RowLast + 1,
                                    caller.ColumnFirst + 1, caller.ColumnLast + 1,
                                    caller.SheetId)

        Static nyy As Integer = 1

        ExcelAsyncUtil.QueueAsMacro(Sub()
                                        n.SetValue(nyan)
                                        c.SetValue(cat)
                                        nyy += 1
                                    End Sub)
        Return "NYA! x " + CStr(nyy)
    End Function
End Module

Answer 1

ExcelAsyncUtil.QueueAsMacro runs the code as soon as possible - in this case when the calculation completes. But the macro you run sets some values on the sheet, so the volatile functions will calculate again. Which queues up a new macro to set some cells... Etc. So everything seems to work as expected.

Setting other cells from inside a UDF is not recommended and blocked by Excel - which is why you have to go through the weird macro route. If you can restructure your functions to be without such side effects, Excel will become much happier.