Calculate really slow (slower than F9)

**Stoobyte** · 09-16-2016, 11:13 AM

Hi guys,

First of all thank you for all your thread who helped me a lot. But now I'd like to do some improvement in my workbook because I loose a big amount of time calculating my workbook.

Let me explain:

I have a Workbook with multiple sheets (some of them are only for database, but 4 of them are for a simulation). I have a Dashboard to see the result of my simulation (I have 3 dashboards actually, in the same sheet, to do A/B/C tests). I have a button for each dashboard to run the simulation and then get the data.
The problem is, when I press F9 the simulation take no more than a minute in the worst case, and around 10 minute by using VBA. Here is my code of one of my button:

Please Login or Register  to view this content.

I basically calculate my 4 sheets row by row (I actually didn't find a way to calculate de 4 sheets at the same time, so to not loose references I had to calculate it this way. If you have a better idea I take it) and then calculate my dashboard, here "Player1".

I tried different things to speed up the calculation (remove Indirect() function as much as I can, mostly by making 4 If statement in a row, set ScreenUpdating to False, remove every conditional formatting...) but my calculation is still slow.
Some time (seems random but now I don't see it anymore) the calculation take less than 2 minutes.

Each sheet has 1100 rows and a bit less than 400 columns, which is not this big.

Hope you can help me. Unfortunately I can really share you the document, but I will answer any questions.

Thank you.

Edit : And I have some array formula mostly for IndexMatch function. I don't know if it's heavy or not.

**jomili** · 09-16-2016, 12:05 PM

Why do you need to calculate each sheet individually? Why not just do the F9, or have a macro to calculate the whole workbook at once (basically same as F9), especially since you say F9 is faster than your macro?

For the macro itself, your Timer/Status bar code slows down the process, and calculating row by row (unless you have a reason to) is really slow.

Finally, without seeing the workbook it's really hard to figure out what formulas you have in that could be slowing things down. You might want to look for any formulas that use a whole worksheet range (for instance, A:A instead of A1:A50).

Please Login or Register  to view this content.

**TMS** · 09-16-2016, 12:08 PM

I have some array formula

If you have full column references in any array formulae, that will slow calculation.

Other than that, formulae copied all the way down the sheet, heavy use of formatting and, especially, Conditional Formatting, can have an impact on performance.

Difficult to say without seeing the workbook.

Not sure I understand why you need to calculate row by row. I would think that means you are calculating the whole workbook 1100 times.

**Stoobyte** · 09-16-2016, 12:18 PM

I actually tried to calculate my 4 sheets at once but I couldn't figured out why. And the reason why I can't use F9 it's because I have my 3 dashboards in my sheet "dashboard" and I don't want them to be calculated at the same time. I want to decide which one is refreshed.

If you know how to disable an entire sheet for being calculated by F9 it could do the trick, but once more I couldn't find how to do it.

Calculating row by row is the only way to do it I found. Because let's say my cell A2 in sheet 1 refers to cells A1 in sheets 2 and 3, cell A1 in sheet 2 refers to cell A1 in sheet 1, and cell A1 in sheet 3 refers to cells A1 in sheet 1 and 2. So if I calculate my entire sheet 1 first I won't get data from other tables.

Does A:A could be quicker than A1:A50 for exemple?

Edit : and as I said I deleted all conditional formatting as well. But I have some random I can't delete and few Indirect left.

**TMS** · 09-16-2016, 12:35 PM

Why can't you use:

Please Login or Register  to view this content.

**TMS** · 09-16-2016, 12:36 PM

A:A is over a million cells whereas A1:A50 is, well, 50 cells. Which do you think would be quickest?

**AlphaFrog** · 09-16-2016, 12:40 PM

Or calculate 1100 rows on each sheet.

Please Login or Register  to view this content.

**AlphaFrog** · 09-16-2016, 12:47 PM

Originally Posted by Stoobyte

Calculating row by row is the only way to do it I found. Because let's say my cell A2 in sheet 1 refers to cells A1 in sheets 2 and 3, cell A1 in sheet 2 refers to cell A1 in sheet 1, and cell A1 in sheet 3 refers to cells A1 in sheet 1 and 2. So if I calculate my entire sheet 1 first I won't get data from other tables.

Your description doesn't paint the whole picture. This is not how Excel works. If a formula references another cell (on another sheet or the same sheet) and the reference cell changes, Excel will automatically recalculate the original formula.

**Stoobyte** · 09-16-2016, 01:23 PM

@TMS, If I do that, Excel calculate the entire Simulation sheet, THEN the inventory sheet etc... But within my Simulation sheet I have reference to my inventory sheet. So it didn't get them. If you know a solution to calculate multiple sheet at the same time using VBA i take it.

@AlphaFrog, Calculating rows like that is like the TMS' solution. And my workbook is on manual calculation options because it's to big. I made the test several times, my Simulation sheet gets data from Inventory of the previous run.
So with F9 I agree with you excel recalculate cells, but not in VBA apparently. Or I make something wrong.

**AlphaFrog** · 09-16-2016, 02:02 PM

Then I would then suggest, instead of using formulas, to have a macro calculate the values in a given range for each sheet and put the results on the sheets. It would be fast if coded properly.

**jomili** · 09-19-2016, 09:11 AM

Or, going off your reply to TMS, calc the Inventory sheet, first, the Simulation. Or whichever sheet you need calculated first. Looking at your first example code TMS was correct in suggesting Simulation first, but if that doesn't work you can change it to any order you want. Bottom line is it's faster to calculate a Range (Worksheets("Simulation").Rows("1:1100").Calculate) or a Worksheet(Worksheets("Simulation").Calculate) than each individual row (For i = 1 To 1100
Worksheets("Simulation").Rows(i).Calculate), and as Alpha Frog suggests you can bypass the formula route and do it all via VBA.

**jomili** · 09-19-2016, 09:12 AM

Dupe, deleted.

**Stoobyte** · 09-20-2016, 08:58 AM

Little update.

I basically don't use my button for now. When my database will be done I'll try to delete all link by replacing them by their value. It could lighten a bit the calculation. Will see.

Anyway, thanks for your answers.

**prjt** · 09-20-2016, 10:10 AM

errr..... Application.Calculation = xlCalculationManual --> SOME CODE --> Application.Calculation = xlCalculationAutomatic ??? Just run all the calcs at once at the end? Or is there a reason the calculations need to be ongoing...

**xlnitwit** · 09-20-2016, 10:30 AM

Originally Posted by Stoobyte

If you know how to disable an entire sheet for being calculated by F9 it could do the trick, but once more I couldn't find how to do it.

Hi,

You may set the EnableCalculation property of a worksheet to False in order to prevent it from calculating until the property is reset.

Calculate really slow (slower than F9)

LinkBack

Thread Tools

Rate This Thread

Display

Calculate really slow (slower than F9)

Re: Calculate really slow (slower than F9)

Re: Calculate really slow (slower than F9)

Re: Calculate really slow (slower than F9)

Re: Calculate really slow (slower than F9)

Re: Calculate really slow (slower than F9)

Re: Calculate really slow (slower than F9)

Re: Calculate really slow (slower than F9)

Re: Calculate really slow (slower than F9)

Re: Calculate really slow (slower than F9)

Re: Calculate really slow (slower than F9)

Re: Calculate really slow (slower than F9)

Re: Calculate really slow (slower than F9)

Re: Calculate really slow (slower than F9)

Re: Calculate really slow (slower than F9)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro running slower and slower

Workbook calculate very slow - any suggestion?

Excel macro getting slower and slower.

slow response on worksheet calculate

VBA routine gets slower and slower on each iteration of the main loop

Large File does calculate and is slow

[SOLVED] Excel gets slower and slower....

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions