Causing all calculated cells to refresh...

[faustino Dina]

Hi,

I'm new to Excel programming. I need to cause all the Workbook to refresh
the calculated cells when de document is opened. I tryed the following.

Private Sub Workbook_Open()

MsgBox "STARTING"
Me.RefreshAll

End Sub

The message box appears as expected, but the Me.RefreshAll doesn't works.
What should I do?
In which order the cells are refreshed?

Thanks in advance
Faustino

 

[Charles Williams]

Hi Faustino,

Assuming refresh=recalculate then
If the workbook is in automatic calculation mode then it will recalculate
when it is opened anyway.
(Tools-->Options-->calculation)
If it is in Manual node then instead of refreshall use
Application.Calculate


hth
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com

 

[faustino Dina]

Thanks for your replay.

Assuming refresh=recalculate then
If the workbook is in automatic calculation mode then it will recalculate
when it is opened anyway.
(Tools-->Options-->calculation)
If it is in Manual node then instead of refreshall use
Application.Calculate

I tryed Application.Calculate but it didn't work as I expect.
The workbook is in Automatic mode. I have some functions in a dll that
display the values extracted from a database into the cells taking as
arguments values from other cells. It works as expected. Whenever I change
an argument cell, the calculated field calls my dll function and refresh the
value in the cell. The problem is that when the document is closed, it saves
the last results from the calculated fields. Then when it is opened again it
shows the saved values. It doesn't call the function until I change manually
the cells that act as argument. But I need all the calculated cell to be
recalculated to rerieve the current values from the database.

 

[Charles Williams]

Application.calculatefull would be more efficient. There is no need to
rebuild the dependency tree.

or you could consider making your functions volatile (Application.Volatile).
Volatile functions are recalculated at each calculation even if their input
arguments do not change.

Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com