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?

***

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?

***

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

***

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?

***

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?

***

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?

***