Excel 2003 VBA - setting "iteration" upon loading

[O_lanke]

Hi!

I have a problem regarding an add-in:

I'm trying to set "iteration" in a workbook_open event with the code:

Private Sub Workbook_Open()

With Application
.Iteration = True
.MaxIterations = 500
.MaxChange = 0.001
End With

But I get the error message

"Error 1014, Method 'iteration'of object application failed' when
Excel is started and loads the add-in

My Excel does not have iteration ticked off as default in the
tools/options/calculation. But why can not this be done with VB upon
start of Excel ?

If the add-in is loaded after Excel have been started, the
workbook_open event works ok, and the iteration is set.

Any suggestions ?

 

[Tom Ogilvy]

Close all visible workbooks.

Now go to Tools=>Options

note that Options is grayed out. So if you don't have a visible workbook
open in Excel, I suspect you will not be able to change these parameters
(until you do have a visible workbook open).

Perhaps you need to instantiate Application Level events in your addin and
do the setting in the application level workbook_open event.

 

[Stephen Bullen]

Hi O_lanke,

My Excel does not have iteration ticked off as default in the
tools/options/calculation. But why can not this be done with VB upon
start of Excel ?

As Tom suggested, for some reason, that property can only be set when
there is a workbook active. You could, therefore, use:

Dim wkbTemp As Workbook

Set wkbTemp = Workbooks.Add
Application.Iteration = True
wkbTemp.Close False

with Application.ScreenUpdating switched off to avoid the flickering.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk