I have a large workbook which, when calculating, causes my PC to lock up due to a lack of memory. I'm hoping that by controlling the calculation one sheet at a time (which I'm attempting with the VBA code below), I can accomplish my goal

When I run the code on a test range, it works exactly as planned/desired. However, when I run the code on a the whole worksheet, although it doesn't lock the workbook up, nothing on the worksheet calculates. Can anybody tell me how I need to alter the code below to achieve my goal?

Thanks
Fre

********************
Sub CalcSheetOnly(ByVal s As String
Select Case
Case "Crab": n =
Case "Cats": n =
Case "Trout": n =
Case "Head": n =
Case "Shucked": n =
Case "Shellfish": n =
End Selec
i = 0: p =
For i = 1 To
Worksheets(i).EnableCalculation = Fals
Next i: i =
' Range("TestRange").Calculat
Worksheets(n).Calculat
For p = 1 To
Worksheets(p).EnableCalculation = Tru
Next p: p =
End Sub

Why not just set your workbook calculation to MANUAL and then calculate what
you want. From vba HELP.
"When the value of this property is False, you cannot request a
recalculation"
EnableCalculation Property
See Also Applies To Example Specifics
True if Microsoft Excel automatically recalculates the worksheet when
necessary. False if Excel doesn't recalculate the sheet. Read/write Boolean.

Remarks
When the value of this property is False, you cannot request a
recalculation. When you change the value from False to True, Excel
recalculates the worksheet.

Example
This example sets Microsoft Excel to not recalculate worksheet one
automatically.

Worksheets(1).EnableCalculation = False
--
Don Guillett
SalesAid Software

"Fred" wrote in message
...
I have a large workbook which, when calculating, causes my PC to lock up
due to a lack of memory. I'm hoping that by controlling the calculation one
sheet at a time (which I'm attempting with the VBA code below), I can
accomplish my goal.

When I run the code on a test range, it works exactly as planned/desired.
However, when I run the code on a the whole worksheet, although it doesn't
lock the workbook up, nothing on the worksheet calculates. Can anybody tell
me how I need to alter the code below to achieve my goal?


Thanks,
Fred

*********************
Sub CalcSheetOnly(ByVal s As String)
Select Case s
Case "Crab": n = 1
Case "Cats": n = 2
Case "Trout": n = 3
Case "Head": n = 4
Case "Shucked": n = 5
Case "Shellfish": n = 6
End Select
i = 0: p = 0
For i = 1 To 6
Worksheets(i).EnableCalculation = False
Next i: i = 0
' Range("TestRange").Calculate
Worksheets(n).Calculate
For p = 1 To 6
Worksheets(p).EnableCalculation = True
Next p: p = 0
End Sub

Hi Fred,

Be careful using Worksheet.calculate :
it ignores interdependencies between worksheets so
you will only get the correct answer if either
- you have no interdependencies
- or you are calculating the sheets strictly in interdependency sequence.
- or all the other sheets have just been calculated with a normal
recalculation

The reason your worksheet.calculate does nothing is because you have set its
enablecalculation to false, which stops it being calculated.
You dont need to set it to false or true.

Actually you dont need the VBA at all, just select the sheet you want to
calculate and press shift-F9.

Unless you have a very small amount of RAM on your PC (128MB or 64 MB or
less) it is very unlikely that lack of memory will cause a calculation to
lock up. (check if your hard disk starts thrashing when you calculate).

How large is your workbook, how much RAM do you have and exactly which
version of Excel are you using (Help --About)?

regds
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Fred" wrote in message
...
I have a large workbook which, when calculating, causes my PC to lock up
due to a lack of memory. I'm hoping that by controlling the calculation one
sheet at a time (which I'm attempting with the VBA code below), I can
accomplish my goal.

When I run the code on a test range, it works exactly as planned/desired.
However, when I run the code on a the whole worksheet, although it doesn't
lock the workbook up, nothing on the worksheet calculates. Can anybody tell
me how I need to alter the code below to achieve my goal?


Thanks,
Fred

*********************
Sub CalcSheetOnly(ByVal s As String)
Select Case s
Case "Crab": n = 1
Case "Cats": n = 2
Case "Trout": n = 3
Case "Head": n = 4
Case "Shucked": n = 5
Case "Shellfish": n = 6
End Select
i = 0: p = 0
For i = 1 To 6
Worksheets(i).EnableCalculation = False
Next i: i = 0
' Range("TestRange").Calculate
Worksheets(n).Calculate
For p = 1 To 6
Worksheets(p).EnableCalculation = True
Next p: p = 0
End Sub