Calculation Modes in Excel

[rdilipk]

I am having a terrible time understanding Excel's calculation modes
and when exactly the message *CALCULATE* appears on the bottom
left of the status bar. Let me boil down this down to one use-case.

Say you have your spreadsheet set to Manual mode. Further assume
you have a subroutine that merely assigns something to Range.Formula.
Something like:

Dim r as Range
Set r = Range("a2")
r.Formula = "=MYFUNC()"

(Assume MYFUNC just returns some deadbeat string.)

When I run the subroutine (F5) I find that everytime the control
passes through r.Formula, the CALCULATE message appears.
That is, despite the fact that assignment to r.Formula actually
caused MYFUNC to execute. Now I am forced to click on that
CALCULATE button to make it go away and this has the side-
effect of calling MYFUNC *one more time*. In essence MYFUNC
is executed *twice*.

Is this expected behavior?

 

[AFPathfinder]

I believe so. It looks like Excel will force a calculation if a cell is registered as uncalculated, such as entering a new formula into a cell.

http://www.decisionmodels.com/calcsecretsg.htm

In my own experiences, I often dislike using the Application.Calculation = xlCalculationManual because it is hard to know if it will work in a specific instance. I'll typically convert formulas to values and convert back to formulas at the end, perform as much in arrays as needed before making changes in a sheet, or use a surrogate workbook for the heavy lifting...before changing that property.

 

[rdilipk]

AFPathFinder -- appreciate for the insight. You may be on to something here.
Can you elaborate what you mean by:

>> I'll typically convert formulas to values and convert back to
>> formulas at the end

How does that particular approach avoid the formula property from
being designated as uncalculated?

 

[AFPathfinder]

Depending on where you have the formulas, it may not be very handy, but if the bulk can be referenced in a few contiguous ranges, you can delete the formulas and use relative referencing to reinput them at the end of the macro.

Dim finalRow As Integer

finalRow = Cells(Rows.Count, 1).End(xlUp).Row

Range("T2:T" & finalRow).ClearContents
'Bulk of the code inbetween
Range("T2:T" & finalRow).FormulaR1C1 = "=IF(RC[-4]<1,0,R[-1]C+RC[-2]-RC[-1])"

You don't have to delete the formulas, but just changing them to something other than a formula for the duration of the macro. I have macros in some of my workbooks that reset formulas throughout the workbook in case users mess them up and they are written similarly. May not be the best method, but can be a quick fix if you are changing values often in a workbook with lots of formulas.