Hi all,
I created a worksheet and it works pretty good except a few problems.
I need it to do more, its a bit of a long one.
15 mins before a horse race i run a macro that copies all the current back prices to a space. This is to reference against to find drifters. With IF statements ive managed to automate the reference and bet. Brilliant so far.
Anyway my problem is that while in running the prices all change and other bets are paced that i dont want. Also when the sheet updates to the next race if the prices on the new race still match up then new bets are placed (Nightmare).
To get around this i created 40 tabs or workbook pages so that when a race is over is doesnt need to change to a next race, solving some of the problem. This is also seems to many tabs to update in a efficient manner sometimes minutes pass without data refreshing.
Here is what i would love to be able to do.
1.Automate the macro 15 mins before every race meaning i cant forget.
2. Fix the problem with either worksheets not updating in time or stopping the bets when in play starts.
Im basically trying to achieve full automation, but it is trying.
Thanks if anyone can help with a solution or workaround
Excel slow to update worksheets
This would all be very easy to do without excel.leerudd wrote: ↑Wed Apr 21, 2021 10:10 pmHi all,
I created a worksheet and it works pretty good except a few problems.
I need it to do more, its a bit of a long one.
15 mins before a horse race i run a macro that copies all the current back prices to a space. This is to reference against to find drifters. With IF statements ive managed to automate the reference and bet. Brilliant so far.
Anyway my problem is that while in running the prices all change and other bets are paced that i dont want. Also when the sheet updates to the next race if the prices on the new race still match up then new bets are placed (Nightmare).
To get around this i created 40 tabs or workbook pages so that when a race is over is doesnt need to change to a next race, solving some of the problem. This is also seems to many tabs to update in a efficient manner sometimes minutes pass without data refreshing.
Here is what i would love to be able to do.
1.Automate the macro 15 mins before every race meaning i cant forget.
2. Fix the problem with either worksheets not updating in time or stopping the bets when in play starts.
Im basically trying to achieve full automation, but it is trying.
Thanks if anyone can help with a solution or workaround
- Silverthorn
- Posts: 26
- Joined: Sat Mar 13, 2021 2:47 pm
- Location: Australia
Something like this should work.
First you need to set a trigger. It's probably easiest is to use a cell. That way you don't have to mess with numeric time.
Just copy the count down timer from F4 from your sheet and paste it to somewhere convenient like G4 so the formatting is the same. Then change G4 to 0:15:00.
VBA Code run your Macro..
Right click the sheet tab that you want the macro to run on and select view code. Put the following in the code for the sheet.
Private Sub Time_Trigger()
If .Range("F4") = .Range("G4") Then Run "MyMacro"
End If
End Sub
You can clear the sheet using similar code but using the suspend notice in cell H1to trigger a macro to clear the required cells.
If .Range("H1") ="Suspended" Then Run "MyMacro"
That should allow you to simplify the workbook and allow it to run faster. The more functionality you put in VBA rather than formulas the faster the workbook will update. However be aware that the workbook does not update when a Macro is running so it's a trade off for performance.
First you need to set a trigger. It's probably easiest is to use a cell. That way you don't have to mess with numeric time.
Just copy the count down timer from F4 from your sheet and paste it to somewhere convenient like G4 so the formatting is the same. Then change G4 to 0:15:00.
VBA Code run your Macro..
Right click the sheet tab that you want the macro to run on and select view code. Put the following in the code for the sheet.
Private Sub Time_Trigger()
If .Range("F4") = .Range("G4") Then Run "MyMacro"
End If
End Sub
You can clear the sheet using similar code but using the suspend notice in cell H1to trigger a macro to clear the required cells.
If .Range("H1") ="Suspended" Then Run "MyMacro"
That should allow you to simplify the workbook and allow it to run faster. The more functionality you put in VBA rather than formulas the faster the workbook will update. However be aware that the workbook does not update when a Macro is running so it's a trade off for performance.
There isn't a Time_Trigger event in Excel hence why it wouldn't work.
You don't need to put Run before a macro name.
Also the statement VBA is faster than formulas is categorically wrong. Formulas within Excel are derived from compiled C++ code whereas VBA is interpreted and is massively slower than formulas.
Try this within the worksheet code
You don't need to put Run before a macro name.
Also the statement VBA is faster than formulas is categorically wrong. Formulas within Excel are derived from compiled C++ code whereas VBA is interpreted and is massively slower than formulas.
Try this within the worksheet code
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Const TIMER_CELL As String = "$F$4"
' Negative times aren't numeric
If IsNumeric(Me.Range(TIMER_CELL).Value) Then
' Ensure not in-play
If Me.Range("G1").Value <> "In-Play" Then
If TimeValue(CDate(Me.Range(TIMER_CELL).Value)) <= TimeSerial(0, 15, 0) Then
Application.EnableEvents = False
' ...
' TRIGGER YOUR CODE IN HERE
' ...
Application.EnableEvents = True
End If
End If
End Sub