Excel 2003 VBA - setting "iteration" upon loading

O

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 ?
 
T

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.
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top