Excel Manual Calculation is stubborn

Hi,

I set my Excel options -- calculation to manual because I have one very
heavy sheet.Last 3 weeks or so I didnt change the option.

Now, I want to change the option back to automatic but even after I change
it and quitting excel, if I start a new excel session options tab show the
calculations as Manual.

I have tried quitting excel but it doesnt work.

Regards,
Hari
India

Excel picks this up from the first workbook that's opened in that session.

So maybe you're opening a workbook that's set to manual as the first
workbook????


Hari wrote:

Hi,

I set my Excel options -- calculation to manual because I have one very
heavy sheet.Last 3 weeks or so I didnt change the option.

Now, I want to change the option back to automatic but even after I change
it and quitting excel, if I start a new excel session options tab show the
calculations as Manual.

I have tried quitting excel but it doesnt work.

Regards,
Hari
India

--

Dave Peterson

Hi Dave,

Im running some macros from my personal macros folder. But, I never set
manual as the option for personal macro folder.

Also, is the option of calculation being automatic / manual worksheet
specific or is it generally applicable for excel.

Regards,
Hari
India


"Dave Peterson" wrote in message
...
Excel picks this up from the first workbook that's opened in that session.

So maybe you're opening a workbook that's set to manual as the first
workbook????


Hari wrote:

Hi,

I set my Excel options -- calculation to manual because I have one very
heavy sheet.Last 3 weeks or so I didnt change the option.

Now, I want to change the option back to automatic but even after I
change
it and quitting excel, if I start a new excel session options tab show
the
calculations as Manual.

I have tried quitting excel but it doesnt work.

Regards,
Hari
India

--

Dave Peterson

It's an application setting (affects all workbooks), but it's either set by
Tools|options|calculation tab or from the first workbook opened in that session.

Try opening excel (and personal.xls).
Then change that setting
then save personal.xls

And close excel and try it out.


Hari wrote:

Hi Dave,

Im running some macros from my personal macros folder. But, I never set
manual as the option for personal macro folder.

Also, is the option of calculation being automatic / manual worksheet
specific or is it generally applicable for excel.

Regards,
Hari
India

"Dave Peterson" wrote in message
...
Excel picks this up from the first workbook that's opened in that session.

So maybe you're opening a workbook that's set to manual as the first
workbook????


Hari wrote:

Hi,

I set my Excel options -- calculation to manual because I have one very
heavy sheet.Last 3 weeks or so I didnt change the option.

Now, I want to change the option back to automatic but even after I
change
it and quitting excel, if I start a new excel session options tab show
the
calculations as Manual.

I have tried quitting excel but it doesnt work.

Regards,
Hari
India

--

Dave Peterson


--

Dave Peterson

Hi Dave,

If Its an application setting why should the same be affected by "from the
first workbook opened in that session".

Also ( Im sorry for jumping threads/posts) as I posted in the
excel.programming group why should my calculation setting change while the
macro is running.

Regards,
Hari
India
"Dave Peterson" wrote in message
...
It's an application setting (affects all workbooks), but it's either set
by
Tools|options|calculation tab or from the first workbook opened in that
session.

Try opening excel (and personal.xls).
Then change that setting
then save personal.xls

And close excel and try it out.


Hari wrote:

Hi Dave,

Im running some macros from my personal macros folder. But, I never set
manual as the option for personal macro folder.

Also, is the option of calculation being automatic / manual worksheet
specific or is it generally applicable for excel.

Regards,
Hari
India

"Dave Peterson" wrote in message
...
Excel picks this up from the first workbook that's opened in that
session.

So maybe you're opening a workbook that's set to manual as the first
workbook????


Hari wrote:

Hi,

I set my Excel options -- calculation to manual because I have one
very
heavy sheet.Last 3 weeks or so I didnt change the option.

Now, I want to change the option back to automatic but even after I
change
it and quitting excel, if I start a new excel session options tab
show
the
calculations as Manual.

I have tried quitting excel but it doesnt work.

Regards,
Hari
India

--

Dave Peterson


--

Dave Peterson

I don't recall seeing any macro that would change calculation (without having
coded it that way). I'd be very hesitant to believe that this is possible.

Well, maybe if your macro were part of an addin that ran with no workbook active
and it opened the first workbook and that workbook had manual calcultion
set???? Does that describe your situation?

==
As to why it's set by the first workbook. I don't have any explanation. That
setting had to be kept somewhere. If the developer team put it in the registry,
then I'd bet that some people wouldn't like that when they shared the workbook
with others.

Sometimes developers make decisions with which users have to live.

(Unencumbered by the thought process, I think it would be nicer as a workbook
level setting that traveled with the workbook.)

Hari wrote:

Hi Dave,

If Its an application setting why should the same be affected by "from the
first workbook opened in that session".

Also ( Im sorry for jumping threads/posts) as I posted in the
excel.programming group why should my calculation setting change while the
macro is running.

Regards,
Hari
India
"Dave Peterson" wrote in message
...
It's an application setting (affects all workbooks), but it's either set
by
Tools|options|calculation tab or from the first workbook opened in that
session.

Try opening excel (and personal.xls).
Then change that setting
then save personal.xls

And close excel and try it out.


Hari wrote:

Hi Dave,

Im running some macros from my personal macros folder. But, I never set
manual as the option for personal macro folder.

Also, is the option of calculation being automatic / manual worksheet
specific or is it generally applicable for excel.

Regards,
Hari
India

"Dave Peterson" wrote in message
...
Excel picks this up from the first workbook that's opened in that
session.

So maybe you're opening a workbook that's set to manual as the first
workbook????


Hari wrote:

Hi,

I set my Excel options -- calculation to manual because I have one
very
heavy sheet.Last 3 weeks or so I didnt change the option.

Now, I want to change the option back to automatic but even after I
change
it and quitting excel, if I start a new excel session options tab
show
the
calculations as Manual.

I have tried quitting excel but it doesnt work.

Regards,
Hari
India

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi Dave,

Some of the questions that u have asked ( sadly) fit me. ( I dont have any
addins installed)

I was running a macro from my personal macro folder and with no other
workbook open and this macro opens other workbooks and the first WB opened
has its calculation set to manual. ( I discovered this in a painful manner
yesterday )

Now, I have one more question. If the calculation is set to manual will the
If statement or any decision statements executed by macro whose decision
point values itself may depend on the calculations, would they be inaccurate
as macro does not get the latest values on the basis of which it may execute
statements.

In my case I was opening all excel files in a folder automatically using
Lookin and Newsearch but the macro will not report the correct number of
files within the folder. If in the first run I have 20 files in the folder
it will run fine. If i delete some files or add some files the macro will
not report the correct no of files and it will also not perform operation on
the newly added files and also gives messages while running that it cannot
find files ( for those which were deleted by me before running the code).

2 things in this:-

1. If I run the code in my colleagues computers with the same code and the
same set of files it will run flawlessly. If I update the no. of files in
the folder the .foundfiles() will also report correctly.

2. I have changed my excel setting to automatic and reran the code but it
doesnt wok.

Regards,
Hari
India

"Dave Peterson" wrote in message
...
I don't recall seeing any macro that would change calculation (without
having
coded it that way). I'd be very hesitant to believe that this is
possible.

Well, maybe if your macro were part of an addin that ran with no workbook
active
and it opened the first workbook and that workbook had manual calcultion
set???? Does that describe your situation?

==
As to why it's set by the first workbook. I don't have any explanation.
That
setting had to be kept somewhere. If the developer team put it in the
registry,
then I'd bet that some people wouldn't like that when they shared the
workbook
with others.

Sometimes developers make decisions with which users have to live.

(Unencumbered by the thought process, I think it would be nicer as a
workbook
level setting that traveled with the workbook.)

Hari wrote:

Hi Dave,

If Its an application setting why should the same be affected by "from
the
first workbook opened in that session".

Also ( Im sorry for jumping threads/posts) as I posted in the
excel.programming group why should my calculation setting change while
the
macro is running.

Regards,
Hari
India
"Dave Peterson" wrote in message
...
It's an application setting (affects all workbooks), but it's either
set
by
Tools|options|calculation tab or from the first workbook opened in
that
session.

Try opening excel (and personal.xls).
Then change that setting
then save personal.xls

And close excel and try it out.


Hari wrote:

Hi Dave,

Im running some macros from my personal macros folder. But, I never
set
manual as the option for personal macro folder.

Also, is the option of calculation being automatic / manual
worksheet
specific or is it generally applicable for excel.

Regards,
Hari
India

"Dave Peterson" wrote in message
...
Excel picks this up from the first workbook that's opened in that
session.

So maybe you're opening a workbook that's set to manual as the
first
workbook????


Hari wrote:

Hi,

I set my Excel options -- calculation to manual because I have
one
very
heavy sheet.Last 3 weeks or so I didnt change the option.

Now, I want to change the option back to automatic but even
after I
change
it and quitting excel, if I start a new excel session options
tab
show
the
calculations as Manual.

I have tried quitting excel but it doesnt work.

Regards,
Hari
India

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Since you're opening the workbook in code, can't you just set the calculation
mode to automatic right after you open that workbook.

Dim newWkbk As Workbook

Set newwkb = Workbooks.Open("C:\book1.xls")
With Application
.Calculation = xlCalculationAutomatic

'not required, changing the calculation mode will recalc
'but just in case!
.Calculate
End With

And if a formula needs calculation, you could get very unexpected results! So
be careful.

And I know that xl2002 is a bit flakey with application.filesearch.

You may want to use the DIR() function.

Here's an example that I just posted in response to a question about retrieving
the formula in C3 of each worksheet of each workbook in a specific workbook.
Maybe you can steal portions.

Option Explicit
Sub testme01()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim tempWkbk As Workbook
Dim wks As Worksheet
Dim oRow As Long
Dim logWks As Worksheet
Dim myAddr As String

myAddr = "C3"

'change to point at the folder to check
myPath = "c:\my documents\excel\test"
If Right(myPath, 1) "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

Application.ScreenUpdating = False

'get the list of files
fCtr = 0
Do While myFile ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

Set logWks = Workbooks.Add(1).Worksheets(1)
logWks.Range("a1").Resize(1, 4).Value _
= Array("Workbook", "Worksheet", "Formula", "Value")

oRow = 1
With logWks
If fCtr 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
Application.StatusBar = "Processing: " & myFiles(fCtr)
Set tempWkbk = Nothing
On Error Resume Next
Set tempWkbk = Workbooks.Open(Filename:=myPath & myFiles(fCtr))
On Error GoTo 0
If tempWkbk Is Nothing Then
MsgBox "Error Opening: " & myFiles(fCtr)
Else
For Each wks In tempWkbk.Worksheets
oRow = oRow + 1
.Cells(oRow, "A").Value = "'" & tempWkbk.Name
.Cells(oRow, "B").Value = "'" & wks.Name
.Cells(oRow, "C").Value _
= "'" & wks.Range(myAddr).Formula
With .Cells(oRow, "D")
.Value = wks.Range(myAddr).Value
.NumberFormat = wks.Range(myAddr).NumberFormat
End With
Next wks
tempWkbk.Close savechanges:=False
End If
Next fCtr
End If
.UsedRange.Columns.AutoFit
End With

With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub



Hari wrote:

Hi Dave,

Some of the questions that u have asked ( sadly) fit me. ( I dont have any
addins installed)

I was running a macro from my personal macro folder and with no other
workbook open and this macro opens other workbooks and the first WB opened
has its calculation set to manual. ( I discovered this in a painful manner
yesterday )

Now, I have one more question. If the calculation is set to manual will the
If statement or any decision statements executed by macro whose decision
point values itself may depend on the calculations, would they be inaccurate
as macro does not get the latest values on the basis of which it may execute
statements.

In my case I was opening all excel files in a folder automatically using
Lookin and Newsearch but the macro will not report the correct number of
files within the folder. If in the first run I have 20 files in the folder
it will run fine. If i delete some files or add some files the macro will
not report the correct no of files and it will also not perform operation on
the newly added files and also gives messages while running that it cannot
find files ( for those which were deleted by me before running the code).

2 things in this:-

1. If I run the code in my colleagues computers with the same code and the
same set of files it will run flawlessly. If I update the no. of files in
the folder the .foundfiles() will also report correctly.

2. I have changed my excel setting to automatic and reran the code but it
doesnt wok.

Regards,
Hari
India


--

Dave Peterson