Updating a Function's Result
 

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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

Updating a Function's Result
 

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


"Syed Zeeshan Haider" wrote in
message ...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

Updating a Function's Result
 

Hi syed,

You need to add this line to your function.

Application.Volatile

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com

"Syed Zeeshan Haider" wrote in
message ...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

Updating a Function's Result
 

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


"Charles Williams" wrote in message
...
Hi syed,

You need to add this line to your function.

Application.Volatile

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com

"Syed Zeeshan Haider" wrote in
message ...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

Updating a Function's Result
 

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

"RADO" wrote in message
...
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


"Charles Williams" wrote in message
...
Hi syed,

You need to add this line to your function.

Application.Volatile

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com

"Syed Zeeshan Haider" wrote in
message ...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

Updating a Function's Result
 

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!
--
Syed Zeeshan Haider.
http://szh.20m.com/


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


"RADO" wrote in message ...
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


"Syed Zeeshan Haider" wrote in
message ...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

Updating a Function's Result
 

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



"Charles Williams" wrote in message
...
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

"RADO" wrote in message
...
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


"Charles Williams" wrote in message
...
Hi syed,

You need to add this line to your function.

Application.Volatile

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com

"Syed Zeeshan Haider" wrote in
message ...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

Updating a Function's Result
 

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

"RADO" wrote in message
...
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



"Charles Williams" wrote in message
...
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

"RADO" wrote in message
...
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


"Charles Williams" wrote in message
...
Hi syed,

You need to add this line to your function.

Application.Volatile

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com

"Syed Zeeshan Haider" wrote
in
message ...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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

Updating a Function's Result
 

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?"


"Charles Williams" wrote in message
...
Hi syed,

You need to add this line to your function.

Application.Volatile

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com

"Syed Zeeshan Haider" wrote in
message ...
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,
--
Syed Zeeshan Haider.
http://szh.20m.com/


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