Running a macro at certain intervals

[MiniBlueDragon]

Hi folks,

I've been running a macro via the "Worksheet_Change" code set as follows:

Code: Select all

Public CurrentMarketID As Long
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
    'Check current Bank and if it's higher than existing Max Bank, update Max Bank
    If Worksheets("Gruss").Range("I2").Value > Worksheets("Gruss").Range("U1").Value Then
        Worksheets("Gruss").Range("U1").Value = Worksheets("Gruss").Range("I2").Value
    End If
    'Check if Market ID changes
    If CurrentMarketID <> Me.Cells(3, "N") Then
        CurrentMarketID = Me.Cells(3, "N")
        ' Run GetCard method
        GetCard
    End If
    Application.EnableEvents = True
End Sub

I'd like to look into running "GetCard" on a more frequent basis in the buildup to a race start, e.g. every 5 or 10 seconds instead of only once when a new market is selected.

Any ideas on how I can do it? Not requesting full code or an Excel developer but a pointer in the right direction would be great.

[osknows]

In BA for Betfair cell B2 holds the current datetime (BA for Betdaq only holds date so use function Now). You can record when GetCard was last run in a variable and then use VBA function DateDiff to test the time difference between when it was last run and the current time.

[MiniBlueDragon]

Thanks osknows. Nice elegant solution; the only problem is that I don't know if there's something similar to "Worksheet_Change" that I can use. Currently I have a button click to bring in all racecard URL's for the day and the code above pulls in individual data once the worksheet's refreshed but it's the in between worksheet changes I'm not sure of?

[osknows]

You can add it into the same worksheet_change function, something like

Code: Select all

Public CurrentMarketID As Long
Dim lastGetCardUpdate As Date

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
    'Check current Bank and if it's higher than existing Max Bank, update Max Bank
    If Worksheets("Gruss").Range("I2").Value > Worksheets("Gruss").Range("U1").Value Then
        Worksheets("Gruss").Range("U1").Value = Worksheets("Gruss").Range("I2").Value
    End If
    'Run GetCard method on next refresh after 30 seconds
    'Replace Cells(2,2) with Now for Betdaq
    If DateDiff("s", lastGetCardUpdate, Cells(2, 2)) > 30 Then
            lastGetCardUpdate = Cells(2, 2)
            ' Run GetCard method
            GetCard
    End If
    Application.EnableEvents = True
End Sub

[MiniBlueDragon]

Ah right; I misunderstood the "Worksheet_Change" then. I assume it only ran when a new market was selected, e.g. the worksheet changes. :/

I'm using BetDaq Assistant as you correctly assumed. I have B2 (cells 2,2) set as "=Now()" however as soon as it refreshes it's set automatically to today's date. The lastGetCardUpdate variable seems to be setting itself to "09/02/2013" but with no seconds even when formatted as "dd/mm/yyyy hh:mm:ss" so when it's checking it's seeing the last ran as the same as current and refreshing every time it checks. :/

[osknows]

I meant replace it in the code, so

Code: Select all

    If DateDiff("s", lastGetCardUpdate, Cells(2, 2)) > 30 Then
            lastGetCardUpdate = Cells(2, 2)
            ' Run GetCard method
            GetCard
    End If

becomes

Code: Select all

    If DateDiff("s", lastGetCardUpdate, Now) > 30 Then
            lastGetCardUpdate = Now
            ' Run GetCard method
            GetCard
    End If

or even better

Code: Select all

    timeNow = Now
    If DateDiff("s", lastGetCardUpdate, timeNow) > 30 Then
            lastGetCardUpdate = timeNow
            ' Run GetCard method
            GetCard
    End If

[MiniBlueDragon]

That's what I did. :/

The code I'm using is:

Code: Select all

Public CurrentMarketID As Long
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
    'Check current Bank and if it's higher than existing Max Bank, update Max Bank
    If Worksheets("Gruss").Range("I2").Value > Worksheets("Gruss").Range("U1").Value Then
        Worksheets("Gruss").Range("U1").Value = Worksheets("Gruss").Range("I2").Value
    End If
    'Run GetCard method on next refresh after 10 seconds
    timeNow = Now
    If DateDiff("s", lastGetCardUpdate, timeNow) > 10 Then
            lastGetCardUpdate = timeNow
            ' Run GetCard method
            GetCard
    End If
    Application.EnableEvents = True
End Sub

Seems to be running getCard every second (same as "refresh") so not quite sure what's up with it. The only thing I can think is maybe the "lastGetCardUpdate" variable is being forgotten in between runs of "Worksheet_Change" so technically the IF statement's always true?

[osknows]

Make sure you have

Code: Select all

Dim lastGetCardUpdate As Date

above the Worksheet_Change event.

[MiniBlueDragon]

What a plum.

Thanks osknows; much appreciated!

[mithcd]

I guess anybody could have easily missed that. Including me. Thanks.