Excel VBA - worksheet.calculate doesn't calcul

Question

Private Sub Worksheet_Activate()

    If Worksheets("Input").Range("S5").Value = "Yes" Then
       MsgBox "Please make sure you've completed the historical deductible amounts for EL"
    End If

    Worksheets("Input").Calculate
    Worksheets("EL").Calculate
    Worksheets("PPL").Calculate
    Worksheets("Auto").Calculate
    Worksheets("AL").Calculate
    Worksheets("APD").Calculate
    Worksheets("Loss Layering").Calculate
    Worksheets("Sheet2").Calculate
    Worksheets("Premium").Calculate

End Sub

In an effort to speed up a very large workbook I have switched off auto calculate and have created a hierarchy in which I calculate sheets as I move through my workbook. My issue is that with any heavy duty formulas such as sumif or sumproduct, the values are not calculated within my active sheet, they stay as zeroes. I have tried application.calculate and CalculateFull, these both work but I find they take up way too much time. I'm trying to find a way to do this while keeping my template as quick, simple and user friendly as possible. Any suggestions?

Answer 1

I know it's been a while, but it has been bugging me for a while and I just came out with a workaround. I created a sub which I call when I want to force calculation on a range.

Sub forceRngCalc(rng As Range)

For Each c In rng

    formulaToCopy = c.Formula
    c.ClearContents
    c.Value = formulaToCopy

Next

End Sub

It's the equivalent of retyping the formula and thus forces to recalculate. It's not very fancy, but it works.

Answer 2

''simple solution, example:

Call Recalculate(Sheet3.[A10:G10])

End Sub Sub Recalculate(rng As Range) rng.value = rng.Formula End Sub

Answer 3

It is not clear which worksheet is the active sheet and contains this code, but I can think of 2 possible reasons for your problem.

1) You have not called worksheet.calculate on the active sheet.
2) Since worksheet.calculate ignores dependencies on other worksheets the calculation sequence you are using will only work as you want if the formulas on the sheets always refer to other sheets that have already been calculated. In other words the sheet calculation sequence must exactly match the inter-sheet references sequence and there must be NO forward inter-sheet references whatsoever (including defined names etc).

as a general point I would not usually expect using worksheet.calculate to calculate an entire workbook would be faster than using Application.Calculate (although I am sure sometimes It will be faster)