User defined function excel not updating
In short, ensure that there’s a data query setup with auto-refresh.
If there isn’t a data connection, add a small one to the worksheet.
Other volatile functions are Today(), Randbetween(), Offset() and Indirect().
In some situations Info(), Cell() and Sum If() can also be volatile.
Some versions of this workaround add a link to a tiny csv file on the same computer.
We’ve got the same result using a data connection from a table in the worksheet. The cell can have anything but we create a cell with NOW() in it, for reasons we’ll explain later.
Because we used NOW() in the source table cell, it’ll be easy to see when/if the worksheet has refreshed.
There are several ways to make Excel automatically refresh data connections and recalculate a worksheet.
These tricks are more important with the upcoming Stock data type in Excel 2016 for Office 365 customers.
On Time Now Time Value(“”), “Auto Refresh” an in-built Excel function that runs automatically when the worksheet is opened. The disadvantage of the VBA approach is that a worksheet is necessary (macro enabled Excel worksheet).
There can be problems sharing macro enabled files because of security concerns.