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?
***
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Stefi" <St...@discussions.microsoft.com> wrote in message
news:14A66FDB-EDA7-499B...@microsoft.com...
> Try to insert
> Application.Volatile
> as the 1st line of your UDFs!
>
> Regards,
> Stefi
>
>
> „Jim Luedke” ezt írta:
Regards,
Stefi
„Niek Otten” ezt írta:
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" <St...@discussions.microsoft.com> wrote in message
news:EBF7F55A-BFF5-4139...@microsoft.com...
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:
=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" <St...@discussions.microsoft.com> wrote in message
news:019C5F3E-481F-43E3...@microsoft.com...
(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.
***
Excel will see that as a change to the formula and reevaluate (calc mode is
automatic, right?).
--
Dave Peterson