changing calculation mode (xlCalculationAutomatic) w/o showing workbook

  • Thread starter Thread starter shawshank247
  • Start date Start date
S

shawshank247

Hello,
Is there a way to switch the calculation mode without having t
actually show the workbook? I am writing a program in VB and want t
turn the automatic calculation mode off while I input a bunch of data
then turn it back on after I finish inputting.

I created this hack so that it would work, but I would rather not hav
to make the workbook visible in order to do this.


wkbk.Windows("ExcelRef.xls").Visible = True
wkbk.Application.Calculation = xlCalculationManual

<inputting function>

wkbk.Application.Calculation = xlCalculationAutomatic
wkbk.Windows("ExcelRef.xls").Visible = False



Thanks
 
I used word to test this.

Option Explicit
Sub testme()

Dim myXL As Object
Dim myWkbk As Object

Set myXL = CreateObject("excel.application")
Set myWkbk = myXL.workbooks.Open("C:\my documents\excel\book1.xls")

myXL.calculation = -4135

myWkbk.worksheets(1).Range("a1").Value = myXL.calculation

MsgBox myXL.calculation

myWkbk.Close savechanges:=True

'Print xlautomatic
'-4105
'Print xlManual
'-4135

Set myWkbk = Nothing
Set myXL = Nothing
End Sub

Any chance you used late binding, so you didn't get excel's values for
xlmanual/xlcalculationmanual (both -4135)?
 
In a private response to an email:

It sure looks like you need to have a visible workbook to change this setting.

Can you add a dummy workbook (if you need to) or hide the whole application?

I'm gonna post back to the newsgroup--since my original response wasn't correct.

--
Dave Peterson
(e-mail address removed)


----- Original Message -----
From: "Bob Barker" <[email protected]>
To: <[email protected]>
Sent: Monday, February 09, 2004 20:37
Subject: Re: changing calculation mode (xlCalculationAutomatic) w/o showing



Hi Dave,

Thanks for trying to help me out. Actually, I have the workbook hidden,
although I create an object pointing to it. But to change the calculation
value, I need to have that active workbook visible.

When you first open the workbook, it is hidden:

wkbk.Windows("ExcelRef.xls").Visible = False

But to change the calculation mode, you need to make it visible first:

wkbk.Windows("ExcelRef.xls").Visible = True
wkbk.Application.Calculation = xlCalculationManual

Is there a way around this? Your example doesn't show that it works when the
workbook is hidden or not visible.

Warren
 
Back
Top