Variable time between Macros

[kshines]

Dear Wise members of the forum.
Below is a piece of VBA that is working great. I would, however, like to modify it so that it would, after "x" seconds run the Macro one more time. I have put what I would like to happen in bold below.
Any help would be very much appreciated!!!
KH

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Message "Calculating"
If Not Intersect(Target, Range("D4:D15")) Is Nothing Then cal_model1
Timer 1 sec
Run Macro1 again
"Calculation complete"
Application.ScreenUpdating = True

End Sub

 

[joeu2004]

I presume that you want to "run macro1 again" only if you run it once in the first place. Something like the following (untested)....

Private Sub Worksheet_Change(ByVal Target As Range)
Const delay As String = "0:0:2"    ' 2 sec delay; 1 sec delay is unreliable
If Not Intersect(Target, Range("D14:D15")) Is Nothing Then
    Application.EnableEvents = False    ' avoids repeated Worksheet_Change calls, if cal_model1 changes range
    Application.ScreenUpdating = False
    Application.StatusBar = Now & "    Calculating...."
    DoEvents
    cal_model1
    Application.Wait Now + TimeValue(delay)    ' see caveat below
    cal_model1
    Application.StatusBar = Now & "    Calculation complete"
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End If

Caveat: While executing Application.Wait, Excel cannot execute. Is that what you want?

Alternatively, use Application.OnTime. But that is not a one-statement replacement. Do you need help with that?