Inability to calculate a range in Excel versions above 2000
 

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

Inability to calculate a range in Excel versions above 2000
 

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

Inability to calculate a range in Excel versions above 2000
 

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.


"G Franc" wrote:

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