Force Recalc

[KT_1010]

On a given column I have a VBA function (which calls other functions
and accumulates the values, yielding a calculated weight for the data
on that row.) When I change the function, is there a way to allow/
force a recalc (F9, Shift-F9). Non of the data on the form has
changed, but the function to be applied to each row has been update,
so the resulting column for each row should be updated.

If I use the recalc sheet button, the value on every row in that
column is a copy of the output for the first row. [If I place the
cursor after the formula in the column I can force the function to
accurately weigh the data in that row. I can spot check some of the
rows, but I'd really like the worksheet to recalculate the value for
all 32,000 rows]

my perception is that when value changes in a dependent cell, the
formula will fire and update the target cell. I had understood, the
"Recalculate Worksheet" would force a recalculation for every cell
that is derived from a function. Apparently that doesn't work for me
either. [[ it calculates the first value, and then copies the value
into that column for all the rows, or alternatively shows a value
error.]

oh. yes. I'm very new at this. I am still trying to find the RTFM
that relates, but it's very hard to find.

 

[Master Blaster]

What happens if you end your code with:

"
Application.Calculation = xlAutomatic
Calculate

"

this should slove your problem. Let me know if it doesn't.

regards

 

[Hubisan]

This might be what you looking for:

VBA-Help > Keyword: Volatile:

"Marks a user-defined function as volatile. A volatile function must
be recalculated whenever calculation occurs in any cells on the
worksheet. A nonvolatile function is recalculated only when the input
variables change. This method has no effect if it's not inside a user-
defined function used to calculate a worksheet cell.

Function My_Func()
Application.Volatile
...
end function"

Best wishes
Daniel

 

[KT_1010]

I added the Application.Volatile to the beginning of the parent
function

and the Application.Calculation=xlCalculateAutomatic
Calculate to the end of the function (prior to 'end function')

on exiting the edit for the module the spreadsheet shows the ongoing
recalculation progress until
all rows have been processed.

However, all rows ($T) show the same value as $1$T. Each row can be
recalculated to show the correct
value (trigger function evaluation row by row.)

this has been a big improvment, but I'm still not there..

(knowing how to phrase the question, is 75% of resolving the
problem.)

 

[Mike]

On a given column I have a VBA function (which calls other functions
and accumulates the values, yielding a calculated weight for the data
on that row.) When I change the function, is there a way to allow/
force a recalc (F9, Shift-F9). Non of the data on the form has
changed, but the function to be applied to each row has been update,
so the resulting column for each row should be updated.

If I use the recalc sheet button, the value on every row in that
column is a copy of the output for the first row. [If I place the
cursor after the formula in the column I can force the function to
accurately weigh the data in that row. I can spot check some of the
rows, but I'd really like the worksheet to recalculate the value for
all 32,000 rows]

my perception is that when value changes in a dependent cell, the
formula will fire and update the target cell. I had understood, the
"Recalculate Worksheet" would force a recalculation for every cell
that is derived from a function. Apparently that doesn't work for me
either. [[ it calculates the first value, and then copies the value
into that column for all the rows, or alternatively shows a value
error.]

oh. yes. I'm very new at this. I am still trying to find the RTFM
that relates, but it's very hard to find.

Have you tried Application.Send ("^%{F9}")

 

[Mike]

On a given column I have a VBA function (which calls other functions
and accumulates the values, yielding a calculated weight for the data
on that row.) When I change the function, is there a way to allow/
force a recalc (F9, Shift-F9). Non of the data on the form has
changed, but the function to be applied to each row has been update,
so the resulting column for each row should be updated.

If I use the recalc sheet button, the value on every row in that
column is a copy of the output for the first row. [If I place the
cursor after the formula in the column I can force the function to
accurately weigh the data in that row. I can spot check some of the
rows, but I'd really like the worksheet to recalculate the value for
all 32,000 rows]

my perception is that when value changes in a dependent cell, the
formula will fire and update the target cell. I had understood, the
"Recalculate Worksheet" would force a recalculation for every cell
that is derived from a function. Apparently that doesn't work for me
either. [[ it calculates the first value, and then copies the value
into that column for all the rows, or alternatively shows a value
error.]

oh. yes. I'm very new at this. I am still trying to find the RTFM
that relates, but it's very hard to find.

Have you tried Application.Send ("^%{F9}")

Whoops that should read Application.SendKeys ("^%{F9}")