If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel fails to update call to user-written function.
I run Excel 2002 under cringe Win 2000.
Although recalc is set to automatic, Excel often fails to calc cells that call functions I wrote in VBA--i.e. whose formula is =MyFunc(). I have a workbook with hundreds of cells with formula "=MyFunc (param)", where param is the address of another cell. MyFunc() returns some text plus the target cell's address. So if, say, I insert a row in the sheet, all cells calling MyFunc() below the row I inserted should update. But few or often even none do. (Except if on the cell I press F2-Edit and return, which of course works.) My only recourse at present is to go thru the entire workbook and F2- Edit/return to force every cell to update. I've tried increasing recalc iterations in Tools / Options, but it has no effect. If Excel's behavior is indeed not to recalc all cells which need to change, then I fail to understand why it does not have a menu option to force recalc of every cell. I mean, huh? What am I missing here? *** |
#2
|
|||
|
|||
Excel fails to update call to user-written function.
Try to insert
Application.Volatile as the 1st line of your UDFs! Regards, Stefi „Jim Luedke” ezt *rta: I run Excel 2002 under cringe Win 2000. Although recalc is set to automatic, Excel often fails to calc cells that call functions I wrote in VBA--i.e. whose formula is =MyFunc(). I have a workbook with hundreds of cells with formula "=MyFunc (param)", where param is the address of another cell. MyFunc() returns some text plus the target cell's address. So if, say, I insert a row in the sheet, all cells calling MyFunc() below the row I inserted should update. But few or often even none do. (Except if on the cell I press F2-Edit and return, which of course works.) My only recourse at present is to go thru the entire workbook and F2- Edit/return to force every cell to update. I've tried increasing recalc iterations in Tools / Options, but it has no effect. If Excel's behavior is indeed not to recalc all cells which need to change, then I fail to understand why it does not have a menu option to force recalc of every cell. I mean, huh? What am I missing here? *** |
#3
|
|||
|
|||
Excel fails to update call to user-written function.
Application.Volatile
That may help, but is certainly not the best solution. There is still no guarantee that the cells will be calculated in the correct sequence. And the cell may be calculated too often which, depending on your workbook complexity, may cause slow execution. You should include all the cells that are used in the function, in the argument list of the call and the UDF definition. So, not MyFunc(), but MyFunc(Arg1, Arg2, ....) etc. That is the only way Excel knows that there are dependencies between the cells. Once it knows the dependencies it will include them in the chain of cells to be recalculated. -- Kind regards, Niek Otten Microsoft MVP - Excel "Stefi" wrote in message ... Try to insert Application.Volatile as the 1st line of your UDFs! Regards, Stefi „Jim Luedke” ezt *rta: I run Excel 2002 under cringe Win 2000. Although recalc is set to automatic, Excel often fails to calc cells that call functions I wrote in VBA--i.e. whose formula is =MyFunc(). I have a workbook with hundreds of cells with formula "=MyFunc (param)", where param is the address of another cell. MyFunc() returns some text plus the target cell's address. So if, say, I insert a row in the sheet, all cells calling MyFunc() below the row I inserted should update. But few or often even none do. (Except if on the cell I press F2-Edit and return, which of course works.) My only recourse at present is to go thru the entire workbook and F2- Edit/return to force every cell to update. I've tried increasing recalc iterations in Tools / Options, but it has no effect. If Excel's behavior is indeed not to recalc all cells which need to change, then I fail to understand why it does not have a menu option to force recalc of every cell. I mean, huh? What am I missing here? *** |
#4
|
|||
|
|||
Excel fails to update call to user-written function.
Thanks Niek, one can always learn new tricks! Is Application.Volatile still
necessary when using this method? Should Arg1, Arg2, .... be declared as Ranges? Regards, Stefi „Niek Otten” ezt *rta: Application.Volatile That may help, but is certainly not the best solution. There is still no guarantee that the cells will be calculated in the correct sequence. And the cell may be calculated too often which, depending on your workbook complexity, may cause slow execution. You should include all the cells that are used in the function, in the argument list of the call and the UDF definition. So, not MyFunc(), but MyFunc(Arg1, Arg2, ....) etc. That is the only way Excel knows that there are dependencies between the cells. Once it knows the dependencies it will include them in the chain of cells to be recalculated. -- Kind regards, Niek Otten Microsoft MVP - Excel "Stefi" wrote in message ... Try to insert Application.Volatile as the 1st line of your UDFs! Regards, Stefi „Jim Luedke” ezt *rta: I run Excel 2002 under cringe Win 2000. Although recalc is set to automatic, Excel often fails to calc cells that call functions I wrote in VBA--i.e. whose formula is =MyFunc(). I have a workbook with hundreds of cells with formula "=MyFunc (param)", where param is the address of another cell. MyFunc() returns some text plus the target cell's address. So if, say, I insert a row in the sheet, all cells calling MyFunc() below the row I inserted should update. But few or often even none do. (Except if on the cell I press F2-Edit and return, which of course works.) My only recourse at present is to go thru the entire workbook and F2- Edit/return to force every cell to update. I've tried increasing recalc iterations in Tools / Options, but it has no effect. If Excel's behavior is indeed not to recalc all cells which need to change, then I fail to understand why it does not have a menu option to force recalc of every cell. I mean, huh? What am I missing here? *** |
#5
|
|||
|
|||
Excel fails to update call to user-written function.
Hi Stefi,
Application.Volatile is not needed then. The type of the arguments depend on their nature; if they are numbers to calculate with, you should use Double. But you can also supply Ranges (of more than one cell), Text, Longs, etc. The "advantage" of using Variants (or not declare a type) is that you can check for exceptions, like empty cells, and do validations yourself (like Text in a field supposed to be a number) and return your own error values, instead of relying on Excel's type matching. I never do that because of the type conversions needed. Excel will do those automatically, of course, but it reduces speed. It does have advantages to declare the function itself as Variant, so you can return standard Excel error values (#NUM, #VALUE, etc). -- Kind regards, Niek Otten Microsoft MVP - Excel "Stefi" wrote in message ... Thanks Niek, one can always learn new tricks! Is Application.Volatile still necessary when using this method? Should Arg1, Arg2, .... be declared as Ranges? Regards, Stefi „Niek Otten” ezt *rta: Application.Volatile That may help, but is certainly not the best solution. There is still no guarantee that the cells will be calculated in the correct sequence. And the cell may be calculated too often which, depending on your workbook complexity, may cause slow execution. You should include all the cells that are used in the function, in the argument list of the call and the UDF definition. So, not MyFunc(), but MyFunc(Arg1, Arg2, ....) etc. That is the only way Excel knows that there are dependencies between the cells. Once it knows the dependencies it will include them in the chain of cells to be recalculated. -- Kind regards, Niek Otten Microsoft MVP - Excel "Stefi" wrote in message ... Try to insert Application.Volatile as the 1st line of your UDFs! Regards, Stefi „Jim Luedke” ezt *rta: I run Excel 2002 under cringe Win 2000. Although recalc is set to automatic, Excel often fails to calc cells that call functions I wrote in VBA--i.e. whose formula is =MyFunc(). I have a workbook with hundreds of cells with formula "=MyFunc (param)", where param is the address of another cell. MyFunc() returns some text plus the target cell's address. So if, say, I insert a row in the sheet, all cells calling MyFunc() below the row I inserted should update. But few or often even none do. (Except if on the cell I press F2-Edit and return, which of course works.) My only recourse at present is to go thru the entire workbook and F2- Edit/return to force every cell to update. I've tried increasing recalc iterations in Tools / Options, but it has no effect. If Excel's behavior is indeed not to recalc all cells which need to change, then I fail to understand why it does not have a menu option to force recalc of every cell. I mean, huh? What am I missing here? *** |
#6
|
|||
|
|||
Excel fails to update call to user-written function.
Hi Niek, I try to explain myself the method:
I have to include all the cell REFERENCES that are used in the function, in the argument list of the call, but it is not necessary to declare arguments of the UDF as Range. e.g. calling: =Myfunc(A1, B1, ....) A1, B1, .... are cell references UDF: Function MyFunc(Arg1, Arg2, ....) Arg1, Arg2, .... any type Am I right? Stefi don't understand. I thought that "... include all the cells that are used in the function, in the argument list of the call and the UDF definition." means that I should include in the argument list cell references in order to inform Excel there is a dependency. If there is no cell reference (that is a Range) in the argument list of the call „Niek Otten” ezt *rta: Hi Stefi, Application.Volatile is not needed then. The type of the arguments depend on their nature; if they are numbers to calculate with, you should use Double. But you can also supply Ranges (of more than one cell), Text, Longs, etc. The "advantage" of using Variants (or not declare a type) is that you can check for exceptions, like empty cells, and do validations yourself (like Text in a field supposed to be a number) and return your own error values, instead of relying on Excel's type matching. I never do that because of the type conversions needed. Excel will do those automatically, of course, but it reduces speed. It does have advantages to declare the function itself as Variant, so you can return standard Excel error values (#NUM, #VALUE, etc). -- Kind regards, Niek Otten Microsoft MVP - Excel "Stefi" wrote in message ... Thanks Niek, one can always learn new tricks! Is Application.Volatile still necessary when using this method? Should Arg1, Arg2, .... be declared as Ranges? Regards, Stefi „Niek Otten” ezt *rta: Application.Volatile That may help, but is certainly not the best solution. There is still no guarantee that the cells will be calculated in the correct sequence. And the cell may be calculated too often which, depending on your workbook complexity, may cause slow execution. You should include all the cells that are used in the function, in the argument list of the call and the UDF definition. So, not MyFunc(), but MyFunc(Arg1, Arg2, ....) etc. That is the only way Excel knows that there are dependencies between the cells. Once it knows the dependencies it will include them in the chain of cells to be recalculated. -- Kind regards, Niek Otten Microsoft MVP - Excel "Stefi" wrote in message ... Try to insert Application.Volatile as the 1st line of your UDFs! Regards, Stefi „Jim Luedke” ezt *rta: I run Excel 2002 under cringe Win 2000. Although recalc is set to automatic, Excel often fails to calc cells that call functions I wrote in VBA--i.e. whose formula is =MyFunc(). I have a workbook with hundreds of cells with formula "=MyFunc (param)", where param is the address of another cell. MyFunc() returns some text plus the target cell's address. So if, say, I insert a row in the sheet, all cells calling MyFunc() below the row I inserted should update. But few or often even none do. (Except if on the cell I press F2-Edit and return, which of course works.) My only recourse at present is to go thru the entire workbook and F2- Edit/return to force every cell to update. I've tried increasing recalc iterations in Tools / Options, but it has no effect. If Excel's behavior is indeed not to recalc all cells which need to change, then I fail to understand why it does not have a menu option to force recalc of every cell. I mean, huh? What am I missing here? *** |
#7
|
|||
|
|||
Excel fails to update call to user-written function.
Sorry, I forgot to erase the out of date part. the real message is:
Hi Niek, I try to explain myself the method: I have to include all the cell REFERENCES that are used in the function, in the argument list of the call, but it is not necessary to declare arguments of the UDF as Range. e.g. calling: =Myfunc(A1, B1, ....) A1, B1, .... are cell references UDF: Function MyFunc(Arg1, Arg2, ....) Arg1, Arg2, .... any type Am I right? Stefi |
#8
|
|||
|
|||
Excel fails to update call to user-written function.
Yes. The call to the UDF is always of the form
=MyFunc(A1,B1,C20:H40,15,"SomeText",TRUE,....) So, arguments can be cell references, Ranges, text and numeric literals, Booleans (and maybe I forget some). In the definition of the function it is your choice to define the argument types. The preciser you define them, the more Excel can validate (and reject) them. But no type definition, which means a Variant, gives more flexibility. For example you could accept both text and numbers in a function which determines the maximum. Function MyFunc(Arg1, Arg2 as Double, Arg3 as Long, Arg4 as String, Arg5 as Range,.......) -- Kind regards, Niek Otten Microsoft MVP - Excel "Stefi" wrote in message ... Sorry, I forgot to erase the out of date part. the real message is: Hi Niek, I try to explain myself the method: I have to include all the cell REFERENCES that are used in the function, in the argument list of the call, but it is not necessary to declare arguments of the UDF as Range. e.g. calling: =Myfunc(A1, B1, ....) A1, B1, .... are cell references UDF: Function MyFunc(Arg1, Arg2, ....) Arg1, Arg2, .... any type Am I right? Stefi |
#9
|
|||
|
|||
Excel fails to update call to user-written function.
Niek and Stefi:
(Very!) belated thanks for your reply to my Q about how to force recalc of UDFs. Application.Volatile does not seem to work. Nor does Application.CalculateFull. Nor does Worksheet.Calculate. Nor does Cell.Calculate on the desired cell(s). Nor does a great suggestion by a Michael Rickards in a 1995 entry in comp.apps.spreadsheets: SaveFormula = Cell.Formula Cell.Clear Cell.Formula = SaveFormula Somewhat unbelievably, the above merely re-enters the obsolete, un- recalc'ed value into the cell. I just cannot seem to, simply, force update of (all?) cells containing (all?) UDF(s) at VBA runtime. Anyone else? Thanks much again. *** |
#10
|
|||
|
|||
Excel fails to update call to user-written function.
How about just changing = to =
Excel will see that as a change to the formula and reevaluate (calc mode is automatic, right?). Jim Luedke wrote: Niek and Stefi: (Very!) belated thanks for your reply to my Q about how to force recalc of UDFs. Application.Volatile does not seem to work. Nor does Application.CalculateFull. Nor does Worksheet.Calculate. Nor does Cell.Calculate on the desired cell(s). Nor does a great suggestion by a Michael Rickards in a 1995 entry in comp.apps.spreadsheets: SaveFormula = Cell.Formula Cell.Clear Cell.Formula = SaveFormula Somewhat unbelievably, the above merely re-enters the obsolete, un- recalc'ed value into the cell. I just cannot seem to, simply, force update of (all?) cells containing (all?) UDF(s) at VBA runtime. Anyone else? Thanks much again. *** -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|