Excel Functions are called multiple times.

Add-in Express™ Support Service
That's what is more important than anything else

Excel Functions are called multiple times.
Excel Functions are called multiple times. 
Michael Benowitz




Posts: 7
Joined: 2017-02-03
I noticed if my spreadsheet only has one cell containing my custom function. The function is called multiple times when I refresh. Is this normal behavior?

Thanks,

Mike
Posted 16 May, 2017 14:12:40 Top
Andrei Smolin


Add-in Express team


Posts: 14104
Joined: 2006-05-11
Hello Mike,

I can't tell for sure whether this is normal or not. I am sure however that your function must be prepared to this scenario.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 17 May, 2017 04:15:54 Top
Michael Benowitz




Posts: 7
Joined: 2017-02-03
Andrei,

Thanks for responding.

My functions handle the multiple calls, but the function executes some sql functions and the multiple calls slow down the refresh of spreadsheet data.

Mike
Posted 17 May, 2017 07:44:42 Top
Andrei Smolin


Add-in Express team


Posts: 14104
Joined: 2006-05-11
Mike,

Your function may check incoming data: if these don't change, the function may return the previous value instead of running the complete mechanism retrieving new data. If the function result depends on how much time has passed since the last call, you can use a timer.

To store last results, you can use a dictionary: key is the cell address, value is the last value. To get the cell, see section Determining the cell, worksheet or workbook your UDF is called from at https://www.add-in-express.com/docs/net-excel-udf-tips.php#determining-cell.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 17 May, 2017 10:47:01 Top
Andrei Smolin


Add-in Express team


Posts: 14104
Joined: 2006-05-11
Hello Mike,

Please check https://social.msdn.microsoft.com/Forums/en-US/0cc5e02b-0449-472e-923c-08d3593e01e5/udfs-are-evaluated-multiple-times?forum=exceldev.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 18 May, 2017 10:19:39 Top