How to refresh values on UDF

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

How to refresh values on UDF
 
Glen Lewis




Posts: 29
 Joined: 2021-03-12
I have an Excel Add-in project, that has both the AddInModule for the ribbon bar and a XLLFunctions.

The XLL Functions I have 4 UDF's defined within in there.

The UDF Example:
ACTMTD(CompanyId, Year, Month, GLACcountRange,..,..,..) as the UDF

The Udf then will query the Cached data for the GL Amounts and sum them up by the given parameters (Filters).

This data is only cached in memory or a local db for the project if a connection to the API server cannot be established.

The solution I am looking for is a way to tell excel to call the UDF again for all instances within a Workbook/Worksheet. A user will open a workbook and then need to refresh the values because the old values could be out of date. (Imagine like if you had linked cells to another workbook and you can choose to update or not)

I did a fairly bad hack to make it work, (Don't need to tell me it is a bad hack I know it). I do a find and replace on all used range cells that contain the UDF and replace the formulas from ACTMTD to ACTMTDTemp and then back. Because this forces Excel to call the UDF again.

I would like to find a graceful way of doing this.
Posted 03 Mar, 2023 14:23:17 Top
Andrei Smolin


Add-in Express team


Posts: 18817
 Joined: 2006-05-11
Hello Glen,

I'm sorry for the delayed response; I've missed your question somehow.

Check the technique discussed in https://www.add-in-express.com/forum/read.php?FID=5&TID=14983. That is, you can construct an Excel.Range object holding all cells that refer to your UDF and then call Range.Dirty() followed by Range.Calculate().

Regards from Poland (GMT+1),

Andrei Smolin
Add-in Express Team Leader
Posted 07 Mar, 2023 09:01:30 Top