[SOLVED] Change the value in a cell every second

Change the value in a cell every second

I have graphs that are updated based on the value in one cell. I am trying to write a script that changes the value in this cell every second and updates the graphs in the process.

The program does not wait one second to call the procedure and rather runs everything directly.

I tried other methods such as Application.wait which works in regards to waiting but it does not update the graphs.

Why doesn't Application.OnTime wait one second?

Sub graphOverTime()

Do While Range("N5").Value = "Running"
    
    'changes cell N5 to Not running on condition
    Call my_procedure

    'Update value in D3 that impacts the graphs
    current_month = Range("D3").Value

    Range("D3").Value = Application.WorksheetFunction.EoMonth(current_month, 1)
    
    DoEvents
    
    'Wait one second to rerun procedure
    Application.OnTime Now + TimeValue("0:00:01"), "graphOverTime"
    
Loop

End Sub

Solution

Your main problem is Do While looping which causes Application.OnTime to set overlapping scheduled calls of graphOverTime. Following is one way of doing what you wanted to in the first place

Sub graphOverTime()
    If Range("N5").Value = "Running" Then
        'changes cell N5 to Not running on condition
        Call my_procedure
    
        'Update value in D3 that impacts the graphs
        Range("D3").Value = Application.WorksheetFunction.EoMonth(Range("D3").Value, 1)
        
        DoEvents
        
        'Wait one second to rerun procedure
        Application.OnTime Now + TimeValue("0:00:01"), "graphOverTime", False
    End If
End Sub