Triggering code to run based on countdown timer VBA

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
rich789
Posts: 6
Joined: Fri Feb 10, 2023 12:59 pm

Hi all

TL;DR Does anyone have a VBA snippet to trigger some more code *just once* that works effectively?!

Hoping you can help; I've been trying to get some automation to trigger based on the time before the event start. I'm going to preface this message with the disclaimer that I do know a reasonable amount about coding but VBA (and Excel really) is entirely new to me! Into the worm can...

I started by using the Worksheet_Change() method and then limiting that to the countdown value cell, but this didn't seem to work and from my research online it looked like a change is not registered if it's coming from an external source. I.e Bet Angel, not me as a user updating the cell manually.

So I then moved on to the Worksheet_Calculate() method. As this does trigger every time the Worksheet updates, and you can't restrict it to a cell, and I want the worksheet to update quickly, I copied the countdown to a cell on Sheet3. The formula for the cell in Sheet3 for the countdown is this:

Code: Select all

=TEXT('Bet Angel'!F4, "hh:mm:ss")
So I now have a cell on the Sheet3 updating once a second. The reason it's wrapped in a text function is because of Excel's way of storing time as a decimal number(?!) and I need to compare the time as a string when calling my VBA

The VBA looks like this:

Code: Select all

Private Sub Worksheet_Calculate()
Dim countdown As String
Dim timeToRunCode As String
countdown = Range("B3")
timeToRunCode = "00:00:45"

    If countdown = timeToRunCode Then
        Debug.Print "Running Python"
        RunPython "import my_code; my_code.run_my_code()"
        Sleep (1500)
    End If
End Sub
So the extra code I'm calling is Python, via XLWings. I thought that this may be the source of the issue, but as soon as the countdown gets to 45s, I see a flurry of Debug prints in the Immediate window in VBA, so Im _assuming_ this means that the VBA is running loads of times. This carries on until Excel crashes and restarts itself

(Sleep was a function I stuck in a module to try and see if I could get it to sleep until the If wouldn't evaluate as True, but that doesn't seem to be working either.)
This is the code in a module for Sleep, grabbed off the internet:

Code: Select all

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
Sorry for the long message, but I wanted to provide as much detail as possible. Tearing my hair out a little bit with this so hoping one of you kind folk with more VBA/Excel knowledge than me can help!

Many Thanks, Rich
User avatar
Derek27
Posts: 23468
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

The Worksheet_Change event code runs EVERY time any cell in the worksheet changes. You then need to test which part changed. This is a piece of code that I use that runs every time the balance gets updated (once a second).

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
'
    Application.EnableEvents = False

' Check for worksheet update completion (balance is last cell to update)
    If Not Intersect(Target, Me.Range("C12")) Is Nothing Then Match.SecondIntervalEvent
    
Exit_Sub:
    Application.EnableEvents = True

End Sub
In the example above, Match.SecondIntervalEvent gets executed every second. Assuming your BA sheet starts at A1, you could change C12 to F3 to point to the countdown timer, then have a nested If statement to test if the time is met and exit if it's not.

It shouldn't be necessary to put the code to sleep with event code. If you want code to run in ten seconds, for example, you can use:

variable = Now() + TimeSerial(0, 0, 10) and then test that Now() > variable Simply exit if it's not.

Or you can use the countdown timer: variable = TimeValue(Range("F3")) + TimeSerial(0, 0, 10) and perform the same test.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

I pasting your question into GPT. Seems reasonable as a first try, but Derek's is more efficient. You could followed it up by asking it do now only do that when one cell changed not any of them. GPT4 was down so it's 3.5 which might explain the simplified solution.
-----------
It sounds like the issue is with the Worksheet_Calculate event firing too frequently, causing Excel to crash. One way to solve this issue would be to introduce a flag that only allows the code to run once when the countdown reaches the target time.

Here's an example of how you could modify your code to include a flag:

Private Sub Worksheet_Calculate()
Dim countdown As String
Dim timeToRunCode As String
Dim ranCode As Boolean

countdown = Sheets("Sheet3").Range("A1").Value ' Update cell reference to match your Sheet3
timeToRunCode = "00:00:45"

If countdown = timeToRunCode And Not ranCode Then
Debug.Print "Running Python"
RunPython "import my_code; my_code.run_my_code()"
ranCode = True
End If
End Sub

In this modified code, we added a new Boolean variable called ranCode that tracks whether the code has already run. The code only runs when countdown equals timeToRunCode and ranCode is False. Once the code runs, we set ranCode to True to prevent it from running again.

Additionally, it's important to make sure that countdown and timeToRunCode have the same format. You're currently using a text format for timeToRunCode, so you may want to convert countdown to text as well:

countdown = Format(Sheets("Sheet3").Range("A1").Value, "hh:mm:ss")

Hope this helps! Let me know if you have any questions.
rich789
Posts: 6
Joined: Fri Feb 10, 2023 12:59 pm

Brilliant, I've got something working with the 'If Not Intersect...'. Thanks both!
betunfair
Posts: 27
Joined: Thu Dec 31, 2015 11:03 am

Bard came up with this if that's any good to you:

To fix this, you need to add a check to the Worksheet_Calculate() method to make sure that the code is only run once. Here is the code that you need to add:

Code snippet
Private Sub Worksheet_Calculate()
Dim isFirstCalc As Boolean

'This variable will be set to True the first time the Worksheet_Calculate() method is called.
isFirstCalc = True

'If this is the first time the Worksheet_Calculate() method is called, then run the code.
If isFirstCalc Then
Debug.Print "Running Python"
RunPython "import my_code; my_code.run_my_code()"
isFirstCalc = False
End If
End Sub
Use code with caution. Learn more
This code will set the isFirstCalc variable to True the first time the Worksheet_Calculate() method is called. If the isFirstCalc variable is True, then the code will be run. If the isFirstCalc variable is False, then the code will not be run.
User avatar
ODPaul82
Posts: 683
Joined: Sun May 08, 2011 6:32 am
Location: Digswell Herts

betunfair wrote:
Mon May 08, 2023 6:29 am
Bard came up with this if that's any good to you:

To fix this, you need to add a check to the Worksheet_Calculate() method to make sure that the code is only run once. Here is the code that you need to add:

Code snippet
Private Sub Worksheet_Calculate()
Dim isFirstCalc As Boolean

'This variable will be set to True the first time the Worksheet_Calculate() method is called.
isFirstCalc = True

'If this is the first time the Worksheet_Calculate() method is called, then run the code.
If isFirstCalc Then
Debug.Print "Running Python"
RunPython "import my_code; my_code.run_my_code()"
isFirstCalc = False
End If
End Sub
Use code with caution. Learn more
This code will set the isFirstCalc variable to True the first time the Worksheet_Calculate() method is called. If the isFirstCalc variable is True, then the code will be run. If the isFirstCalc variable is False, then the code will not be run.
That will consistently trigger as you're setting isFirstCalc every single time inside Worksheet_Calculate() as it is declared inside of the routine
Even though your turning it off in the if condition, the scope of the variable is within the routine only, it doesn't remember what you set it to next time and anyway, you're turning it back on every single time.

To do it that way you'd be better off doing like this

Code: Select all

Private blnHasCalculated As Boolean	' Shove at the top of the worksheet

Private Sub Worksheet_Calculate()

If Not blnHasCalculated Then
      Debug.Print "Running Python"
    RunPython "import my_code; my_code.run_my_code()"
    blnHasCalculated = True
End If
End Sub
[/clode]
betunfair
Posts: 27
Joined: Thu Dec 31, 2015 11:03 am

Thanks, appreciated, cheers.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”