Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force Recalc
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force Recalc
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force Recalc
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force Recalc
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.) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force Recalc
"KT_1010" wrote in message ... 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}") |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force Recalc
"Mike" wrote in message m... "KT_1010" wrote in message ... 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}") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't stop recalc | Excel Worksheet Functions | |||
cell does not recalc | Excel Worksheet Functions | |||
Conditional Recalc | Excel Programming | |||
Automatic Recalc | Excel Worksheet Functions | |||
Force recalc of formulas saved as text | Excel Programming |