Calculating Time Dependent User Defined Functions

[cphenley]

I work with workbook that contains userdefined functions that pull dat
from another program.

The data is time dependent and I created a simple button macro t
calculate when new data is availible (every hour).

However, the macro often fails to update the spreadsheet when clicked
and pressing f9 does not execute the cells. Excel goes through it'
completion counter in the status bar but it fails to compute th
formulas.

When I click on a cell containing a formulae, click on the formula i
the formula bar and hit enter, then the value is computed (the sam
calculating cells status appears, but this time it actually does).

Does anyone have any ideas for how to force excel to execute th
functions?

Thank

 

[Frank Kabel]

Hi
you may post at least how you call your UDF. Maybe together with the
relvant part of your code. Some ideas:
- add application.volatile at the beginning of your UDF
- use the referenced cells as parameter in your UDF

 

[cphenley]

A typical function contained in a cell: =ATGetAgg('SIC K1'!F$1, "", ""
'SIC K1'!$A23, 'SIC K1'!$A24, "1h", 10, 0, "5m", 1, 1040, 0, 0)

F$1: refers to the tag

A23 and A24: refer to the times for the function to be evaluated

When I open the program it calculates all the cells that it can base
off the current time. However, refresh(f9) still fails to execut
cells once enough time has passed that some cells should be able to b
calculated. Yet, clicking on each cell individual still allows them t
generate.

I am not sure how to add application.volatile

I assumed that what I have was a user defined function, but perhaps w
have different definitions.

Thanks for you attempt to help Frank. I have no idea how to approac
this

 

[cphenley]

Creating a button with a save as macro did the trick