How to speed up the calculation process within macro?

oem7110 wrote:

ActiveSheet.EnableCalculation = False

Could you please tell me more on what above code do?

It disables calculation of any formulas on the worksheet, at least until sheet.EnableCalculation=True is set later, where "sheet" is ActiveSheet, a sheet object (e.g. Sheet1), or Sheets(name or number).

I don't remember if we can calculate formulas on that worksheet selectively, e.g. Range("a1").Calculate, or by executing sheet.Calculate.  "The exercise is left to the student". ;-)

The problem is:  the EnableCalculation=False state is not saved with the file (sometimes?).  So we need put something like the following into the Workbook_Open event procedure:

Sheet1.EnableCalculation = False

The Workbook_Open event procedure is executed before recalculation at open time.

PS:  In a previous posting in this thread, I bleated that the Workbook_Open code did not work reliability.  I was wrong!  I had made a rookie mistake during my experiments.

oem7110 wrote:

I have been working around on simplifying those calculations, and make all formula calculated manually finished around 1 hour without running CalAuto

If you make significant improvements to the formulas in R3:V3, it would nice if you had shared those changes with us so we would not waste our time trying to improve obsolete design.

Nevertheless, this will probably be my last major posting on the subject, since I have already invested more time and effort than I can afford.  I hope some of the pinciples below are useful, if not the actual suggestions.

All my comments below relate to the design that you originally uploaded to http://1drv.ms/1e07c7e.

In a nutshell, I suggest that you make the following design changes in Excel and VBA.  On my computer, these changes cut the run time nearly 49% when auto-calc was not re-enable.

Note:  Although it reduced the run time by 49%, it still takes about 10 sec to process 23 formulas in R3:V25.  That seems to scale linearly, to wit:  20 sec for 46 formulas, and 30 sec for 69 formulas.  So unfortunately, I estimate it would take about 3.71 hours to process 30,715 formulas.

But those times are for my ancient laptop (read:  slow and small memory).  If you can process 30,715 formulas in 1 hour with your improvements, perhaps the following suggestions will reduce that time to 30 min or less (without enabling auto-calc) -- unless you implemented the same improvements independently.

1. Enter the following formula into O2; this is used in the changes in #2 below.

=COUNT(I3:I60000)

2. Replace the formulas in R3:V3.  Array-enter the following formula into R3 (press ctrl+shift+Enter instead of just Enter), and copy it into S3:V3.

=SUM(IF($I$3:INDEX($I$3:$I$60000,$O$2)=$Q3,

  IF($M$3:INDEX($M$3:$M$60000,$O$2)="C1",

  IF($H$3:INDEX($H$3:$H$60000,$O$2)=R$1,$J$3:INDEX($J$3:$J$60000,$O$2))

  - IF($H$3:INDEX($H$3:$H$60000,$O$2)=S$1,$J$3:INDEX($J$3:$J$60000,$O$2)))))

3. Replace Cal_RV and Clean with the following.

Sub Cal_RV()

    Dim n As Long

    Range("N3") = Time

    Range("N2") = "R:V"

    Range("O1:O2").Calculate

    n = Range("O1")

    Range("R3:V3").AutoFill Destination:=Range("R3:V" & 2 + n), Type:=xlFillDefault

    Range("R3:V" & n + 2).Calculate

    Range("N1") = n

End Sub

Sub Clean()

    If Not IsEmpty(Range("V4")) Then

        Range("R4", Cells(Rows.Count, "V").End(xlUp)).ClearContents

    End If

End Sub

4. If you want to re-enable auto-calc, but you are content to set ActiveSheet.EnableCalculation=False, replace CalAuto and CalManual with the following.

Sub CalAuto()

    ActiveSheet.EnableCalculation = False

    With Application

        .Calculation = xlAutomatic

        .MaxChange = 0.001

        .ScreenUpdating = True

    End With

End Sub

Sub CalManual()

    With Application

        .Calculation = xlManual

        .MaxChange = 0.001

        .CalculateBeforeSave = False

        .ScreenUpdating = False

    End With

    ActiveSheet.EnableCalculation = True

End Sub

Also add the following into the ThisWorkbook module:

Private Sub Workbook_Open()

    Sheet1.EnableCalculation = False

End Sub

Explanation....

1. Excel changes

A major part of the problem with your original design is the formula in R3 (copied into S3:V3):

=SUMPRODUCT(($I$3:$I$60000=$Q3)*($M$3:$M$60000="C1")

  *($H$3:$H$60000=R$1),$J$3:$J$60000)

  - SUMPRODUCT(($I$3:$I$60000=$Q3)*($M$3:$M$60000="C1")

  *($H$3:$H$60000=S$1),$J$3:$J$60000))

Thus, Excel always processed nearly 60,000 rows (in multiple columns), even though you have about 30,700 rows of data.

Moreover, that formula does some calculations twice, namely ($I$3:$I$60000=$Q3)*($M$3:$M$60000="C1").

The array-entered formula I suggest remedies both problems, to wit:

=SUM(IF($I$3:INDEX($I$3:$I$60000,$O$2)=$Q3,IF($M$3:INDEX($M$3:$M$60000,$O$2)="C1",...)))

For example, $I$3:INDEX($I$3:$I$60000,$O$2) constructs the range $I$3:$I$30715 in your original uploaded file, and $I$3:$I$30715 and $M$3:$M$30715 are each processed only once.

(Also note that INDEX is not volatile, whereas INDIRECT and OFFSET are.)

In fact, the comparison $M$3:$M$30715="C1" and other comparisons to the right are not processed if an earlier comparison to the left is FALSE.

2. VBA changes

The significant rewrite of Cal_RV has many improvements.  Here is a critique of the origin implementation first.  You wrote:

    Dim i As Integer

    Range("R3:V3").Select

    Selection.AutoFill Destination:=Range("R3:V" & Range("O1").Value), Type:=xlFillDefault

    Range("N3") = Time

    Range("N2") = "R:V"

    Range("O1").Calculate

    For i = 3 To Range("O1").Value

        Range("Q" & i & ":U" & i).Calculate

        Range("N3:N5").Calculate

        Range("N1") = i

    Next i

2.1. Variable "i" should be type Long.  (I replaced "i" with "n".)  Otherwise, the code will fail if there is more than 32,767 data.

2.2. It is prudent to execute Range("O1").Calculate before using Range("O1").Value for the AutoFill method.  The formula in O1 is =COUNT(Q3:Q60000).

2.3. As noted in a previous response, we should use 2+Range("O1") in the AutoFill method.  Likewise, we should also use 2+Range("O1") in the For statement, if we kept it.  (I don't.)

2.4. It is not necessary to select Range("R3:V3").  Range("R3:V3").Autofill suffices.  Arguably, it is a minor improvement relative to the total calculation time.  But it is an important principle to understand and apply in the future.

2.5. It is not necessary to recalculate N3:N5, much less in a loop.  They are invariant:  N3=Time here, and N4:N5 contain constants.  Moreover, there are no visible dependencies on N3:N5 in the original uploaded file.

2.6. Likewise, it is not necessary to recalculate N1 in the loop because, again, there are no visible dependencies on N1.  It is sufficient to assign N1=Range("O1") outside the loop.

2.7. Most significantly, it seems unnecessary to calculate Range("Qi:Ui") [sic] in a loop.  Moreover, the range should be Range("Ri:Vi"), where the formulas are.  But there are no dependencies on R3:V3 et al, much less dependencies that are recalculated individually in the loop in manual-calc mode.

Thus, Range("R3:V" & 2+n).Calculate suffices, where "n" is the value of Range("O1").

FYI, with the original Excel design (non-array-entered SUMPRODUCT formulas), it is not necessary to use the AutoFill method and the Range("R3:V" & 2+n).Calculate.  The following would copy the formula (adjusting relative references) and calculate each copy, even in manual-calc mode:

Range("R3:V" & 2+n).Formula = Range("R3:V3").Formula

However, that paradigm does not seem to work with array-entered, even if we use FormulaArray instead.  Instead, the AutoFill method followed by the Calculate method for the entire range does work.  Unlike the Range.Formula assignment about, Range.AutoFill does not calculate each copy.

---

In summary, even with my suggestions, when you tell Excel to do up to 7*30715 operations 30715 times, that translates into 6.6 billion (10^9) operations.  And if we re-enable auto-calc without disabling EnableCalculation for the worksheet, that's 13.2 billion operations.  That's a huge number of operations.

Okay, I'm tired.  I hope I covered everything, and I hope I'm not beating a dead horse or overwhelming you.

Good luck!