Excel VBA formulas don't calculate fully

Question

I have a toggle cell which feeds calculations throughout multiple sheets in my workbook. The goal is, whenever this toggle cell is changed, workbook recalculates and provides an update number.

I have manual calculation turned on.

Below are methods I have tried (note the code is in a sheet)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Cells(3, 4)) Is Nothing Then
        Application.Calculate
    End If

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Cells(3, 4)) Is Nothing Then
        Application.CalculateFull
    End If

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Cells(3, 4)) Is Nothing Then
        Application.CalculateFullRebuild
    End If

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Cells(3, 4)) Is Nothing Then
        range("DI:DI").calculate 
        'to calculate the specific range which drives the updated numbers
    End If

None of these methods work. The only method is to save the workbook and everything calculates.

Any thoughts?

-----------------------------EDIT-----------------------------

Only solution that works so far, force calculate all cells that are drive the final results. Hoping for a more elegant solution. Total run time ~3seconds\

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Cells(3, 4)) Is Nothing Then
        Worksheets("Dashboard").Range("af3").Calculate
        Worksheets("Base").Range("R1:R50000").Calculate
        Worksheets("Control").Range("R1:R50000").Calculate
        Worksheets("Base").Range("DI1:DI50000").Calculate
        Worksheets("Control").Range("DI1:DI50000").Calculate
        Worksheets("Dashboard").Range("W10:W5000").Calculate
        Worksheets("Dashboard").Range("L10:L5000").Calculate
        Worksheets("Dashboard").Range("Y10:Aa5000").Calculate
        Worksheets("Dashboard").Range("B2:B7").Calculate
end if

Does the event happen and do you actually get inside the If? — GSerg
Did you tried Application.Calculation = xlCalculationAutomatic ? — user8753746
Exactly what is being calculated (built-in functions or non-volatile VBA UDFs?), and what is the Application.CalculationMode? — Mathieu Guindon
If you step through the code and pause on the If statement, what are Target.Address and Cells(3,4).Address? — Dave
Start with putting a debug.print / msgbox to make sure the sub gets triggered. — Alex

Daruki Daruki · Accepted Answer · 2017-11-01T15:52:25

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Cells(3, 4)) Is Nothing Then
        Worksheets("Dashboard").Range("af3").Calculate
        Worksheets("Base").Range("R1:R50000").Calculate
        Worksheets("Control").Range("R1:R50000").Calculate
        Worksheets("Base").Range("DI1:DI50000").Calculate
        Worksheets("Control").Range("DI1:DI50000").Calculate
        Worksheets("Dashboard").Range("W10:W5000").Calculate
        Worksheets("Dashboard").Range("L10:L5000").Calculate
        Worksheets("Dashboard").Range("Y10:Aa5000").Calculate
        Worksheets("Dashboard").Range("B2:B7").Calculate
end if

Force re-calc all dependent ranges.

Other methods may not work as file is big(~30mb)

Excel VBA formulas don't calculate fully

1 Answers