How to set in XLA calculation on xlCalculationManual

[Billy]

Hello!

I have application in XLA file. When I run that XLA over AddIn or from
directory I have set in that XLA on ThisWorkbook event Workbook_Open()

Inside I have two lines:

Application.Calculation = xlCalculationManual
Application.OnEntry = "myProcedure"

Problem is that this lines don't run and I get error msg 1004: Method
'Calculation' of object '_Application' failed.

If I run that later then overything is fine. I also try to put that
two lines in Workbook_Activate() event but then that lines never get
processed.

This commands has to be set before user open any other workbook.

Does anybody already had the same problem and how to solve that?

Best regards,
Billy

 

[Tom Ogilvy]

I believe the problem is that a workbook hasn't been opened, so you can't
change the setting. Even though it is an application wide setting, it is
associated with the workbook where it is set. You might want to instantiate
Application level events and use the application level workbook_open to set
the calculation mode (untested suggestion)

 

[Rob Bovey]

Hi Billy,

Modify the code in your Workbook_Open event as follows:

ThisWorkbook.Activate
Application.Calculation = xlCalculationManual
Application.OnEntry = "myProcedure"

You need to have a workbook active before you can set the Calculation
property and although it seems counterintuitive, you can "activate" your
add-in workbook to serve this purpose even though it's not visible.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Peter T]

Hi Billy,

Not directly related, but a problem that more than puzzled
me once:

I had the situation where my Excel always opened with Calc
set to Manual. Set it to Auto, close/reopen Excel, back to
Manual! Tried everything, removed all addins, went through
all the registry settings. Tried to change it with my own
addin in the open event - which failed same way as yours.

To cut a long story short I must have saved my
Personal.xls whilst Calc was Manual. This being the first
workbook (excl addins) to open was setting calc to Manual.
Resaving Personal whilst Auto resolved.

Maybe for your solution, in your addin's open event, first
add a dummy hidden workbook before changing Calc. A wb
will also need to be open before resetting Calc to Auto.

Regards,
Peter

 

[Charles Williams]

Hi Rob,

Neat trick, works OK for setting the calculation property,
but it still gets reset on my system by the first non-template, non-xla,
non-empty workbook subsequently opened.


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

 

[Rob Bovey]

Hi Rob,

Neat trick, works OK for setting the calculation property,
but it still gets reset on my system by the first non-template, non-xla,
non-empty workbook subsequently opened.

Hi Charles,

Well as long as it doesn't crash I figure it must be OK. <vbg> Thanks
for pointing that out.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Billy]

Thanks for all your answers guys!

Rob I use your suggestion and I have to say that until now work as it should.

Regards,
Billy