Application.OnTime doesn't wait-CodePudding

I have some 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. However, the program does not seem to wait one second to call the procedure and rather runs everything directly. I have tried other methods such as Application.wait which works in regards to waiting but it does not update the graphs. From information I have found on the internet, it seems like Application.OnTime is the best option. Could someone help me figure out why Application.OnTime does not wait one second? Here is my code:

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

CodePudding user response：

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