| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?" |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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?" |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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?" |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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?" |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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?" |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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?" |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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?" |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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?" |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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?" |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup with result TRUE not updating cell | Excel Worksheet Functions | |||
| use function to change a string to function's parameter | Excel Worksheet Functions | |||
| Cells display function's text rather than its results | Excel Discussion (Misc queries) | |||
| A correct IF function doesn't show the function's result in cell | Excel Discussion (Misc queries) | |||
| want sumif function's range to evaluate 2 columns | Excel Worksheet Functions | |||
![Syed Zeeshan Haider[_5_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
