Recalc versus enter

[Bura Tino]

Hi,

Consider a cell with some formula in it. From excel's point of view, what is
the difference between my pressing Ctrl-Alt-F9 for a recalc and actually
clicking into the cell and pressing Enter? What's the VBA for the latter?

Thanks,

Bura

 

[Charles Williams]

Hi Bura,

Ctrl-alt-F9 recalculates every formula in all open workbooks

re-entering a formula causes just that formula to be evaluated (and if
calculation is automatic then all volatile and dependent formulae will also
be recalculated)

vba for recalculating the activecell is
activecell.calculate

or activecell.formula=activecell.formula

(or you could replace the = in the formula with =)


see also my "calculating from VBA" page
http://www.decisionmodels.com/calcsecretsh.htm

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Guest]

Ctrl + ALT + F9 is : Calculate.Full

 

[Bura Tino]

Ctrl + ALT + F9 is : Calculate.Full

My experience is different from both answers. If a cell has a VBA function,
for example, and I have just changed the content of the function then
Calculate.Full does not necessarily refresh that formula and I need to go in
by hand and press Enter. The same thing happens if for some reasons the cell
was #NAME? and I had fixed that name. I need to re-Enter the formula. So
clicking in and pressing Enter guaranties a complete recalc of the cell
while Calculate.Full doesn't. So I'm looking for the VBA for the latter.

I apologize - my previous post was incomplete.

Bura

 

[Bill Renaud]

Public Sub UpdateFormula(rng As Range)
With rng
If .HasFormula _
Then
.Formula = .Formula
End If
End With
End Sub

This checks to see if the cell has a formula, then simply re-enters it into
the cell (this marks it as having been edited, as far as Excel is concerned;
Excel 2000 SP-3). When the macro finishes running, the recalculation engine
will automatically update the worksheet. Modify the above routine to loop
through an entire worksheet or an entire workbook. Run this routine after
editing all of your other UDFs. Put the cursor on any line in the code and
press F5, or call it from the Tools|Macro>Macros... command. You could also
simply set a reference to all cells on a worksheet that contain a formula
(Edit|Goto>Special... Formulas command), then loop through them with a For
Each ... Next loop.

 

[Bill Renaud]

OK, here is a better version:
'----------------------------------------------------------------------
'Update all formulas in all worksheets in the currently active workbook.
Public Sub UpdateWorkbookFormulas()
Dim ws As Worksheet
Dim rngCellsWithFormulas As Range
Dim rngCell As Range

On Error GoTo NoCellsWithFormulas

'Iterate through only worksheets; skip chart and macro sheets.
For Each ws In ActiveWorkbook.Worksheets
'Find only cells with formulas (Numbers, Text, Logicals, or Errors).
Set rngCellsWithFormulas = ws.Cells.SpecialCells(xlCellTypeFormulas)

'Skip this code if none found on this worksheet.
For Each rngCell In rngCellsWithFormulas
With rngCell
'Re-enter the formula to trigger recalculation when done.
.Formula = .Formula
End With
Next rngCell
NoCellsWithFormulas:
Next ws
End Sub

 

[Bill Renaud]

Hopefully, the 3rd time is a charm!!! (Discovered a bug in the error handler
in case no worksheets had any formulas!)

'----------------------------------------------------------------------
'Update all formulas in all worksheets in the currently active workbook.
Public Sub UpdateWorkbookFormulas()
Dim ws As Worksheet
Dim rngCellsWithFormulas As Range
Dim rngCell As Range

On Error GoTo NoCellsWithFormulas

'Iterate through only worksheets; skip chart and macro sheets.
For Each ws In ActiveWorkbook.Worksheets
'Clear object variable from the previous worksheet
'for proper error handler operation.
Set rngCellsWithFormulas = Nothing

'Find only cells with formulas (Numbers, Text, Logicals, or Errors).
Set rngCellsWithFormulas = ws.Cells.SpecialCells(xlCellTypeFormulas)

'Skip this code if none found on this worksheet.
If Not (rngCellsWithFormulas Is Nothing) _
Then
For Each rngCell In rngCellsWithFormulas
With rngCell
'Re-enter the formula to trigger recalculation when done.
.Formula = .Formula
End With
Next rngCell
End If
Next ws

Exit Sub

NoCellsWithFormulas:
Err.Clear
Resume Next
End Sub

 

[Charles Williams]

Hi Bura,

I cannot duplicate your problem with Calculatefull with Names or UDFs.

Can you be more specific about the circumstances that cause Calculatefull to
fail?

The only difference I know of between re-entering a formula and
recalculating it is that the dependency tree gets updated.
(as in in Ctrl-Alt-shift-F9 in XL 2002).

As mentioned in my previous post you can use .formula=.formula (assuming you
have no array formulae).


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Bill Renaud]

I built a simple workbook and used the following UDF (user-defined
function):

Public Function UDF(Var1 As Double, Var2 As Double)
UDF = Var1 + Var2
End Function

I was able to duplicate the problem every time (I have Excel 2000 SP-3).
Edit (change) the UDF in a code module, then compile, save the file, and
exit the VBA editor. Do not edit any cells on the worksheet. In the above
example, I changed the formula to divide Var1 by Var2, instead of adding
them together. Notice that any worksheet that uses that UDF does not update
when you close the VBA editor. This is because a recalculation has not been/
triggered (because no predecessor cells have been changed).

 

[Bura Tino]

Hi Bura,

I cannot duplicate your problem with Calculatefull with Names or UDFs.

Can you be more specific about the circumstances that cause Calculatefull to
fail?

Since I don't think I can attach documents I will describe it with words.

Design an add-in which has a function and a worksheet which uses that
function. Also, have a procedure which copies that worksheet out to the
active workbook. (Think of it as the add-in having templates which it serves
out.) Then the cells that depends on that function will have #NAME? until
you employ that .Formula=.Formual trick.

 

[Bura Tino]

This worked beautifully, thanks!

Hopefully, the 3rd time is a charm!!! (Discovered a bug in the error handler
in case no worksheets had any formulas!)

'----------------------------------------------------------------------
'Update all formulas in all worksheets in the currently active workbook.
Public Sub UpdateWorkbookFormulas()
Dim ws As Worksheet
Dim rngCellsWithFormulas As Range
Dim rngCell As Range

On Error GoTo NoCellsWithFormulas

'Iterate through only worksheets; skip chart and macro sheets.
For Each ws In ActiveWorkbook.Worksheets
'Clear object variable from the previous worksheet
'for proper error handler operation.
Set rngCellsWithFormulas = Nothing

'Find only cells with formulas (Numbers, Text, Logicals, or Errors).
Set rngCellsWithFormulas = ws.Cells.SpecialCells(xlCellTypeFormulas)

'Skip this code if none found on this worksheet.
If Not (rngCellsWithFormulas Is Nothing) _
Then
For Each rngCell In rngCellsWithFormulas
With rngCell
'Re-enter the formula to trigger recalculation when done.
.Formula = .Formula
End With
Next rngCell
End If
Next ws

Exit Sub

NoCellsWithFormulas:
Err.Clear
Resume Next
End Sub
--
Regards,
Bill


go

 

[Charles Williams]

I agree that changing a UDF does not trigger a recalc, but Tina's problem is
that after changing the UDF Ctrl_alt_F9/executing calculatefull does not
trigger a recalc of the UDF. This I cannot duplicate.


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Bill Renaud]

Tina wrote:
<<Design an add-in which has a function and a worksheet which uses that
function. Also, have a procedure which copies that worksheet out to the
active workbook. (Think of it as the add-in having templates which it serves
out.) Then the cells that depends on that function will have #NAME? until
you employ that .Formula=.Formual trick.>>

What happens if the template worksheet is in a separate workbook, instead of
in the add-in?

 

[Bill Renaud]

I can duplicate this also (in Excel 2000 SP-3). Try this. Build a simple
workbook with a UDF. Edit the UDF, compile the code module, exit the VBE
editor, then save the workbook. Without changing any cells on the worksheet,
bring up the Tools|Options dialog box, goto the Calculation tab, and press
the "Calc Sheet" button. Notice that the worksheet does NOT update, even
though the UDF was changed. This is because the recalculation engine does
not see any cells that have changed, either cells that are arguments to the
UDF or the cell with the UDF itself. Even if a recalculation is triggered by
other cells that do not call the UDF, any cells that contain the UDF will
not update, since none of their precedent cells were changed.

Even if you declare the UDF Volatile inside the function when you edit it,
the worksheet will not update the results of any cells that use the UDF if
no precedent cells are changed. The UDF has to be called at least once
before the Volatile flag actually gets set for that UDF. Using the "Calc
Sheet" button will not update the cells that contain the UDF. Once the UDF
has been called once (with Application.Volatile declared inside the UDF),
then the UDF can be edited and it will update all cells on the worksheet in
the normal fashion when either the "Calc Sheet" button is clicked or any
other change that triggers a recalculation occurs.

 

[Charles Williams]

Hi Bura, Bill,

Many thanks for the description of the problem, which allowed me to
duplicate it.

The problem is that it does not retain the reference to the workbook when
the sheet is copied (or , to be more precise it changes the workbook
reference from the source workbook to the destination workbook when the copy
is being done from the source workbook).

You can see this if you make the function reference contain an explicit
workbook ref: it gets changed when the copy is done.

So since it cannot find the function in the destination workbook you get
#Name.

Bill correctly guesses that the problem does not exist if the template
worksheet is in a separate workbook.

Reentering the formula causes a search in all open workbook for the
function, so it gets resolved.

Nice one!

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Charles Williams]

Hi Bill,

Try ctrl-alt-F9 or calculatefull, I think you will find that it
recalculates.

See also below what I think is the explanation of Bura Tino's problem.


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Bill Renaud]

Charles,

You are absolutely correct about using CalculateFull (Ctrl+Alt+F9) to force
a recalculation of the active workbook! Thanks for the tip.

(The CalculateFull button is in the Tools category of the Customize dialog
box, and has to be added to a toolbar, since it is not available by
default.)

 

[Bura Tino]

Hi Bura, Bill,

Many thanks for the description of the problem, which allowed me to
duplicate it.

The problem is that it does not retain the reference to the workbook when
the sheet is copied (or , to be more precise it changes the workbook
reference from the source workbook to the destination workbook when the copy
is being done from the source workbook).

You can see this if you make the function reference contain an explicit
workbook ref: it gets changed when the copy is done.

So since it cannot find the function in the destination workbook you get
#Name.

Bill correctly guesses that the problem does not exist if the template
worksheet is in a separate workbook.

Reentering the formula causes a search in all open workbook for the
function, so it gets resolved.

Nice one!

So from studying the thread I'm concluding that .Fomula = .Formula is a
legtimate trick.

Will this work if the cell has an array formula?

 

[Dave Peterson]

It didn't when I tried it.

But you could look for a formula and look for an array:

With ActiveCell
If .HasFormula Then
If .HasArray Then
.FormulaArray = .FormulaArray
Else
.Formula = .Formula
End If
End If
End With



Bura Tino wrote:

So from studying the thread I'm concluding that .Fomula = .Formula is a
legtimate trick.

Will this work if the cell has an array formula?

<<snipped>>

 

[Bill Renaud]

Tina,
I would consider the idea of building a separate template workbook (*.XLT)
that is saved in the Templates directory. All normal calculations and UDF's
would be contained in this template (along with any event handlers, if
needed). In this way, all of the problems of re-entering formulas with a VBA
routine of some sort simply go away. The routines that we suggested are no
longer needed. You could include and use array formulas, embedded charts,
etc. all you want.

Put all common code that supports a toolbar or custom menu into an add-in
(*.XLA). It can initially be developed as a separate workbook (*.XLS), then
saved as an add-in once all the code works the way you want it. In making
the add-in, you only have to add a few short lines of code in the
Workbook_AddinUninstall event to delete any toolbar that was attached when
the add-in was installed.