VBA Timer to delay running of Sub until automatic input has completed

[flanna]

My workbook has2 worksheets. Sheet1 and Sheet2. Data is sent to Sheet2 from an outside source. The data fills about 50 cells. New data is added, in batches, about every 10 minutes. The data is then used to compile some statistics in Sheet1.
I use this code to run the StatsWrite sub when the data has updated:

Private Sub Worksheet_Change(ByVal Target As Range)
Call StatsWrite
End Sub

Problem is that the StatsWrite sub (writes data to Sheet1) runs after each cell in Sheet2 is updated and this makes the whole thing very slow. I need to delay the running of the sub until each batch of data has completed, and then run the sub once. Then when the next batch of data is added, some minutes later, the sub runs again. There is usually a delay of 5 to 10 minutes between data updates.

 

[tonyyy]

flanna,

You might give this a try...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim StartTime As Single
Dim TriggerTime As Single
Dim PauseTime As Single

StartTime = Timer
PauseTime = 20 '<<<<< Adjust as needed
TriggerTime = StartTime + PauseTime

Application.EnableEvents = False
Do While Timer < TriggerTime
    DoEvents
Loop
Application.EnableEvents = True

Call StatsWrite
 
End Sub

The code puts in a 20 second pause starting at the first worksheet change event, then calls your StatsWrite macro.

Cheers,

tonyyy

 

[flanna]

Thanks Tonyyy, I tried your code but all it does is to make the process much slower. There is now a 20 second delay after every cell in Sheet2 is updated. Also the Statswrite sub still runs after each cell is updated.

 

[tonyyy]

Sorry that didn't work, flanna.

Sounds like the data is being sent to Sheet2 a cell at time rather than in a single transaction, yes?

Ideally, if we knew the last cell being populated we could use that as a trigger to call the StatsWrite program. How close can we get? You say the "data fills about 50 cells." Is that in a single row? Or column? When the next batch arrives, does it delete the previous data then start filling in again? Or does it overwrite the previous data? Or does it append to the next row or column?

Is the data coming from another vba program? Could it tell us how many cells will be filled?

 

[flanna]

Thanks for looking at this Tonyyy. You might be onto something.
The data is sent one cell at a time. There are multiple rows. Row count can vary. The data appends the existing data (adds more rows). It is not coming from a VBA program. However, the sending program does know how many rows there will be each time. It would be possible to send a new_data_row_count to a specific cell on a spare sheet i.e. Sheet9!B2

 

[tonyyy]

So would the sending program send the new_data_row_count when it starts to send the data to Sheet2? Or when it's completed sending the data?

If you have a choice, choose the latter. Then we could use a change event on Sheet9!B2 to kick off the StatsWrite program.

If you have to go with the former... well, that will raise a few more questions.

 

[flanna]

The new_data_row_count could only be sent at the beginning of the data stream.
Another idea I am trying to work with is this: Considering that each data update to Sheet2 takes only a few seconds (no more than 5 sec), have a delay timer written into the StatsWrite sub. So that the sub writes the stats after waiting 5 sec from the Sub Worksheet_Change(ByVal Target As Range).

 

[tonyyy]

Unless we can target the last populated cell or row in Sheet2, every change to the worksheet will trigger the macro - whether the delay is in the macro itself (as in my original attempt) or in the StatsWrite sub - and will run it 50 times (or however many cells are being populated.)

Now, however, we can use the change to Sheet9!B2 to trigger the StatsWrite sub. The code below should go into the Sheet9 module.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim StartTime As Single
Dim TriggerTime As Single
Dim PauseTime As Single

If Not Application.Intersect(Target, Range("Sheet9!B2")) Is Nothing Then
    StartTime = Timer
    PauseTime = 5    '<<<<< Adjust as needed
    TriggerTime = StartTime + PauseTime

    Application.EnableEvents = False
    Do While Timer < TriggerTime
        DoEvents
    Loop
    Application.EnableEvents = True

    Call StatsWrite
End If

End Sub