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

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

"Bura Tino" wrote in message
...
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

Ctrl + ALT + F9 is : Calculate.Full

"AA2e72E" wrote in message
...
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

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|MacroMacros... command. You could also
simply set a reference to all cells on a worksheet that contain a formula
(Edit|GotoSpecial... Formulas command), then loop through them with a For
Each ... Next loop.
--
Regards,
Bill


"Bura Tino" wrote in message
...

"AA2e72E" wrote in message
...
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

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
--
Regards,
Bill


"Bura Tino" wrote in message
...

"AA2e72E" wrote in message
...
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

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


"Bura Tino" wrote in message
...

"AA2e72E" wrote in message
...
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

This worked beautifully, thanks!

"Bill Renaud" wrote in message
...
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


"Bura Tino" wrote in message
...

"AA2e72E" wrote in message
...
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

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

"Bura Tino" wrote in message
...

"AA2e72E" wrote in message
...
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

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).
--
Regards,
Bill

"Charles Williams" wrote in message
...
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.


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

"Bura Tino" wrote in message
...

"AA2e72E" wrote in message
...
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