locked
VBA - auto recalcuate formulas RRS feed

  • Question

  • Afternoon all,

    Does anyone know of a way of getting the formulas in my worksheet to recalcuate automatically?

    So for example if there a few lines of code that could be run whenever the workbook is opened that will recalculate the formulas automatically every 5 seconds until the workbook is closed?

    Or is there a way of recalculating the formulas everytime some clicks the mouse?

    Thanks, any help would be appreciated,

    Regards,

    Pete

    Friday, March 21, 2014 5:25 PM

All replies

  • Start by running StartTimer...................run StopTimer before closing the workbook:

    Public RunWhen As Double
    Public Const cRunIntervalSeconds = 5
    Public Const cRunWhat = "refresh"

    Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
         schedule:=True
    End Sub

    Sub StopTimer()
       On Error Resume Next
       Application.OnTime earliesttime:=RunWhen, _
           procedure:=cRunWhat, schedule:=False
    End Sub

    Sub refresh()
        Application.CalculateFullRebuild
        Call StartTimer
    End Sub


    gsnu201308

    • Proposed as answer by ryguy72 Friday, May 2, 2014 3:05 PM
    Saturday, March 22, 2014 12:57 AM