Slow Calculation

[Macphesto]

Hello,

I have a spreadsheet and when i run it on excel 2007 it runs perfectly but when i run it in 2003/2007 after each entry in a cell or a check box is clicked the sheet calculates and it takes a while to complete unless I click any random cell which then calculates instantly.

I can't figure this out, any suggestions?

[Richard Buttrey]

Hi,

Can you clarify. Do you mean its OK in 2007 but not 2003?

Is it exactly the same workbook? i.e was it created as a .xls file and then opened with 2007.
Was it created as a .xlsx file perhaps with features unique to 2007 and then and saved as a 1997/2003 file?

Can you upload the workbook so that we can check?

[Macphesto]

Hi Richard,

Thanks for your quick reply! It was created on 2003 and it is .xls file. I open it in 2010/07 and works perfect and when i amended it i always made sure to save it as .xls without and 2007/2010 add ons

[Richard Buttrey]

In that case upload the workbook so that we can see the problem in context.

[Macphesto]

hi Richard,

i can't upload it as it got sensitive data on it . the spreadsheet is about 9mb and it has about 12 sheets, macro etc.. i done all the usual bit to streamline it reset last cell .. clear variables at the end of the macro etc.. etc..

what puzzles me it behaves weird on 1 pc, i tried it on 3 different pc with different set-ups. i am blaming the pc or excel itself on the machine.

what it is doing on this particular pc is as soon i enter a value ina cell or tick a check box, in the status bar i can see the calculation percentage going up slowly then if i select another cell with out entering any values the calculation goes to 100% instantly

[rollis13]

What hardware has that PC, I mean, how much ram does it have; you aren't using a small file. Does the harddisk need cleaning and defrag ?

[Macphesto]

its a dual core with 2 gig ram. hard disk is kept very clean using ccleaner & defragler twice a week .. the odd thing is when i input a value/text in a blank cell and press enter it calculates slowly and if I press enter twice it calculates instantly ..

[Richard Buttrey]

hi Richard,

i can't upload it as it got sensitive data on it . the spreadsheet is about 9mb and it has about 12 sheets, macro etc.. i done all the usual bit to streamline it reset last cell .. clear variables at the end of the macro etc.. etc..

This is the first time you've mentioned the word 'macro'. That may be significant. Are there any sheet change or sheet selectionchange macros in operation?

Can't you anonymise the sensitive data and then upload?

The approach I've generally adopted in the past when similar problems have arisen is what I call the 'binary division and delete' technique.
Delete half the worksheets and see if the problem still exists. If not you know the problem is associated with the sheet you deleted. So open the workbook again and delete half the number the original sheets you deleted and check again.
Repeats of this process will narrow you down to one sheet that causes the problem.

Then adopt the same technique but deleting half the rows, and then when you've identified the row, do the same with half the columns.

Eventually you will hit on the rogue cell or range.

[Macphesto]

Hi Richard,

thanks for your suggestion! I will try that and see how it goes. i have change macro on 2 other sheets, using the marlett font instead of check boxes, i will disable that and see how it goes.

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

One thing you could try is put a breakpoint (F9) on the first line of code in the before double click macro so that if it kicks in it will halt.
Then step through with the F8 key and see if any line of code takes more than a split second to action. If it does examine what that line is doing.

[Macphesto]

Hello,

FYI - I though i post this as it might help other, i found what the problem is with excel 2003, it is explained on the below link.
http://support.microsoft.com/default...;en-us;Q243495
and sadly there is no practical solution. the only thing I can think of is to delete all formulas and do a macro on the change event to insert the formula in the relevant cells upon user input. i.e. if the user starts from row "1" cell "a" then formulas will be filled in the next cell and if the the first cell of the row is blank then there be no formula generated..

[Richard Buttrey]

Hi,

Or avoid more than 65536 dependencies when using XL 2003!

Just as a matter of interest. How many formulae do you have and how many precedents does each formula have?

[Macphesto]

hi
A lot by the looks of it, joking a side. I have a lot of different formulas as the spreadsheet does a lot of different things. I done this for work though if i had a say i would have used access and not excel but we do not have access at work. The spreadsheet is more of a data base than anything else, each record(row) has 5 dates which they are validated (if the date is before today the cell changes colour) and 3 of the date have 2 check boxes next to them, depending on which one is ticked it is copied on another sheet, there are 5 different types of variations in all, 5 different sheets. They all use vlookup and if(and/or formulas though the formulas are not long the biggest one has 3 precedents. From each Variation sheet i have a userform with different options like exporting the relevant data to csv and emailed to a specific email, mail merge which produce one print job and then saves each letter with the person name & date in a specific folder, on one of the variation sheets and on the main i have formulas that look on another sheet for addresses which are downloaded from the net as a web query and i have another option to generate a check sheet (importing data printing etc..) also looking into the other sheets for data. Then i have another sheet with 4 different calculators using workday to calculate the 3 dates depending on time they work and if they had an interruption in service etc .. it also looks into another sheet where i have dates stored which are holidays etc..

each sheet has 500 rows and when i finished it was 22mb though after i streamlined it i reduced it to 9mb ..

[Richard Buttrey]

Hi

OK that explains a lot.

Maybe not a perfect solution, but have you considered holding some of the formulae/validations in a holding cell and using macros to copy them down a range only when necessary and then immediately convert them back to values.