Avoid UDF Recalculation after deleting Rows or Columns

120 views
Skip to first unread message

naina...@gmail.com

unread,
Jul 31, 2015, 3:24:24 AM7/31/15
to Excel-DNA
Hi 

I have been using Excel DNA from past couple of months and so far so good.

Yesterday some of the users complained about  entire excel recalculation after insert or deleting cells in worksheet
i have searched many sites and found that there is no workaround.
But is there any way to avoid this. The users are kind of frustrated with this behavior. 

They usually copy data from different workbook and this refreshes entire worksheet. They usually have 100 s of rows entered and all the 100 rows are getting recalculated which the users are not liking.


Any help/suggestions are highly appreciated.


Thanks,
Nain

Govert van Drimmelen

unread,
Jul 31, 2015, 3:32:33 AM7/31/15
to Excel-DNA, naina...@gmail.com
Hi Nain,

Are your functions volatile?
Your functions will be volatile in one of these cases:
* You've marked it as [ExcelFunction(IsVolatile=true)]
* You've marked it as [ExcelFunction(IsMacroType=true)] and you have at least one argument of type object that is marked [ExcelArgument(AllowReference=true)].

Also, I think RTD formulae (so anything async in Excel-DNA too) also recalculate when inserting or deleting rows or columns.

Volatile functions can be made less volatile, but you have to consider the function and its context.
For RTD-based stuff there's not much we can do.

If you're not in these cases, you'll need to explain your situation in a bit more detail.

-Govert


naina...@gmail.com

unread,
Aug 2, 2015, 4:08:47 PM8/2/15
to Excel-DNA, naina...@gmail.com
Hi Govert - Thanks for the reply. 

I use ExcelAsyncUtil.Run method in the UDF. If we can't control the insert/delete rows/columns, can we catch the row delete column delete actions?

If i can trigger a function for row/column delete operations in excel i can avoid calling the UDF when they fire. Is this possible?

Thanks,
Nain
Reply all
Reply to author
Forward
0 new messages