Excel fails to update call to user-written function.

[Jim Luedke]

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?

***

 

[Stefi]

Try to insert
Application.Volatile
as the 1st line of your UDFs!

Regards,
Stefi


„Jim Luedke†ezt írta:

 

[Niek Otten]

<Application.VolatileThat 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.

 

[Stefi]

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:

 

[Niek Otten]

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).

 

[Stefi]

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:

 

[Stefi]

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

 

[Niek Otten]

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,.......)

 

[Jim Luedke]

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]

How about just changing = to =

Excel will see that as a change to the formula and reevaluate (calc mode is
automatic, right?).