Inability to calculate a range in Excel versions above 2000

[G Franc]

The macro below fails whenever the calculation tab in excel version 2002 and
newer are set to manual with interations checked. The MS EXCEL work around is
to set calculations to automatic or to uncheck iterations. Neither option is
suitable for my application.

Are others having a similar problem? My only solution to date is to continue
running EXCEL 2000.



Sub calc()
Dim mess
On Error GoTo er
Selection.Calculate
GoTo done
er:
mess = Error(Err)
MsgBox (mess)
done:
End Sub

 

[Charles Williams]

Range Calculate does not support Iteration in any version: it just does not
complain about it in earlier versions.
So the correct solution is

Sub calc()
Dim mess
dim blIter as boolean
On Error GoTo er
blIter=application.iteration
if blIter then application.iteration=false
Selection.Calculate
if bliter<>application.iteration then application.iteration=bliter
GoTo done
er:
mess = Error(Err)
MsgBox (mess)
done:
End Sub

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[G Franc]

Thank you Charles for your reply. Please excuse my delay in reponse ... I've
just returned from vacation. Hope your holiday was enjoyable.

I have tried your proposed work around. I am still having problems because
my spreadsheet applications require circular referencing. When the macro
temporarly turns off iterations, the subsequent range calculation stops the
macro due to circular reference errors.

Are you aware of any application setting to ignore circular reference
concerns within the calculation so that the process will continue.