Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Calculate Macro (2000)

    Is it possible to have a macro do an auto calculate every 'n' seconds? I know there are potential risks when using this on entry forms, however I am creating a view only form. I would like the now() function to update every 'n' seconds from a macro instead of having to press F9.

    Thanks!!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Auto Calculate Macro (2000)

    You can add this to a module, and run "StartRecalculation" and will recalculate the active sheet every 10 seconds. Change the time, give an explicit sheet if desired. Run the stoprecalculation to end the macro from running.

    <pre>Option Explicit
    Dim NextTick
    Sub StartRecalculation()
    NextTick = Now + TimeValue("00:00:10")
    ActiveSheet.Calculate
    Application.OnTime NextTick, "StartRecalculation"
    End Sub

    Sub StopRecalcalculation()
    ' Cancels the OnTime event (stops the clock)
    On Error Resume Next
    Application.OnTime NextTick, "StartRecalculation", , False
    End Sub</pre>



    I would not really recommend having a macro continual run in the background, it seems like a waste of resources. It may be better to update based on an event or even do an automatic recalculation when things change.
    Steve

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Calculate Macro (2000)

    Here's a variation on Steve's that doesn't require the user to run a macro. though it may take 2 periods to close. The user activates the "Close" sheet to close the workbook. <pre>Option Explicit
    Const Period = 10 ' The time in seconds between calculations
    Dim mySht As String

    Sub Auto_Open()
    '
    Sheets("Sheet1").Activate
    Calculate
    Application.OnTime Now + TimeValue("00:00:10"), "PeriodicCalculate1"
    mySht = ActiveSheet.Name

    End Sub
    Sub PeriodicCalculate1(Optional Abort As Boolean)
    '
    Calculate
    If ActiveSheet.Name = "Close" Then
    Worksheets(mySht).Activate
    ThisWorkbook.Close
    Else
    Application.OnTime Now + TimeValue("00:00:10"), "Auto_Open"
    mySht = ActiveSheet.Name
    End If
    End Sub
    </pre>

    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •