Updating a Function's Result

  • Thread starter Thread starter Syed Zeeshan Haider
  • Start date Start date
S

Syed Zeeshan Haider

Hello Experts,
I have Excel 97 Pro on Win98SE.

I have written a custom function which depends on date, therefore, its
result must be refreshed every time the workbook is opened; like NOW
function does.
How can I give my function the ability to update its results automatically?

Thank you,
 
somewhere in your spreadsheet, have a cell with the current date, i.e, a
cell "$A$1" with the formula '=Today()' or Now(). Then include a new
parameter into your function, i.e., CurrentDate as Date, and when you use
your function, use the cell with the date as a source for CurrentDate. Your
function use will look like this:

=myfunction(.....whatever other parameters you have already..., $A$1)

Every time you open your workbook, Today (or Now) will recalculate cell
$A$1, and this will force Excel to recalculate your function.

Cheers,

RADO
 
volatile will work, but it slows down spreadsheet a lot, if you use the
function many times. Taking an updatable parameter in is a better solution,
in my opinion.

RADO
 
Hi Rado,

NOW and TODAY are volatile functions anyway.

I do not see the advantage in making a function volatile by including an
additional argument that refers to a volatile function rather than just
making the function volatile. It certainly will not run any faster or less
often, and seems more complicated.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com
 
Thank you for the information!

Your idea is very wise but I have to make my functions, fully automatic and
independent like NOW() is. Such functions could be distributed to others who
need them without any deep knowledge of VBA or such expertise of Excel like
you have.

Thank again!
 
Hi Charles,

I agree with your opinion regarding NOW function, because it changes with
the timer. But I assume TODAY, even if it's volatile, does not change the
value within a day, and therefore, does not trigger a call of the user
function (my understanding is that the call is made only when the argument
changes value, am I correct?). If true, it can be a big advantage if the
user function is time-consuming or used extensively. It's also easier to
debug code without volatile functions.

Respectfully -
RADO
 
Hi Rado,

Unfortunately Excel recalculates dependents of calculated cells even if the
calculated value does not change.

So TODAY is volatile, and even if the value returned by TODAY does not
change, any formula referring to the cell containing TODAY will be
recalculated at every recalculation.


Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com
 
Hey! You! Charles! Yes I am talking to you. You are an intelligent guy. Your
response worked. I just want to say MANY THANKS. ;-)

Thanks again!
--
Syed Zeeshan Haider.
http://szh.20m.com/


-----------------------------------
Allah says to Mankind:
"Then which of the favours of your Lord will ye deny?"


in message
 
Back
Top