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

Hi Fred,

You have enough RAM I think.

You dont say how large the data range is but assuming its 1650 rows *
?columns columns that would make

742500 * 3* 1650 multiplications =3675 million

The answer is that if you want to use SUMPRODUCT it will take a very long
time to calculate (you could leave it running over the weekend and it would
probably finish).

I suggest you look for an alternative approach:
- pivot tables would be good
- alternatively you need to do something like sorting the data and using
that to minimise the size of the range that you are using SUMPRODUCT on.

more suggestions on my website

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

"Fred" wrote in message
...
Charles,

Thank you very much for your response. My operating/hardware system looks
like this.......

OS Name Microsoft Windows XP Professional
Version 5.1.2600 Build 2600
OS Manufacturer Microsoft Corporation
System Manufacturer IBM
System Model 635025U
System Type X86-based PC
Processor x86 Family 15 Model 1 Stepping 2 GenuineIntel ~1594 Mhz
BIOS Version/Date IBM 20KT34AUS, 4/24/2002
SMBIOS Version 2.31
Locale United States
Total Physical Memory 256.00 MB
Available Physical Memory 73.44 MB
Total Virtual Memory 881.18 MB
Available Virtual Memory 539.66 MB
Page File Space 626.45 MB
Page File C:\pagefile.sys

My workbook is about 13,000KB
I'm using Microsoft Office Professional 2003 with & Excel 2003
(11.5612.5703)

I'm trying to calculate the following dimensions:
1650 rows x 75 Columns x 6 worksheets = 742,500 cells!

In each cell I'm using the SUMPRODUCT function to extract data based on 3
criteria from an imported table of data residing in the 7th worksheet. So
that's the deal in a nutshell.

I tried "Shift F9" first, but got the same noncalculating result. If I
don't first set the worksheet to "manual", it locks up (i.e. 100% of CPU
being used and "application not responding", hard disk NOT churning) upon
opening.

I really appreciate your help with this and am willing to try anything to
make it work.

Thank you,
Fred

I have a similar issue using Excel 2003 with array formulas.

My data file is ~30Mb (with approx 20,000 rows of data and 4 pivot
tables)

My analysis file is ~11Mb with 15 worksheets each with ~75 cells with
array formulas to read the data file above.

The re-calculation takes 5 minutes ordinarily with an IBM T40 laptop
with 1GB RAM. Specs are similar to the original posters

OS Name Microsoft Windows XP Professional
Version 5.1.2600 Service Pack 1 Build 2600
OS Manufacturer Microsoft Corporation
System Manufacturer IBM
System Model 237392U
System Type X86-based PC
Processor x86 Family 6 Model 9 Stepping 5 GenuineIntel ~1594 Mhz
BIOS Version/Date IBM 1RETB7WW (3.00c), 25/02/2004
SMBIOS Version 2.33
Locale Canada
Hardware Abstraction Layer Version = "5.1.2600.1106
(xpsp1.020828-1920)"
Total Physical Memory 1,024.00 MB
Available Physical Memory 619.55 MB
Total Virtual Memory 3.40 GB
Available Virtual Memory 2.72 GB
Page File Space 2.40 GB
Page File C:\pagefile.sys


"Charles Williams" wrote in message ...
Hi Fred,

You have enough RAM I think.

You dont say how large the data range is but assuming its 1650 rows *
?columns columns that would make

742500 * 3* 1650 multiplications =3675 million

The answer is that if you want to use SUMPRODUCT it will take a very long
time to calculate (you could leave it running over the weekend and it would
probably finish).

I suggest you look for an alternative approach:
- pivot tables would be good
- alternatively you need to do something like sorting the data and using
that to minimise the size of the range that you are using SUMPRODUCT on.

more suggestions on my website

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