Formula TIMEVALUE

Discuss anything related to using the program (eg. triggered betting tactics)

Moderator: 2020vision

Re: Formula TIMEVALUE

Postby brumbie » Sun Jan 09, 2022 1:42 am

Mate, I'm guessing here but I think you have stuck your original Worksheet_Change into a module instead of placing it here:
Right click the name of the sheet that you are using for Gruss, then click view code. That is where your Worksheet_Change code should go.
brumbie
 
Posts: 197
Joined: Tue Dec 28, 2010 2:00 am
Location: Brisbane,Australia

Re: Formula TIMEVALUE

Postby Sussexguy44 » Sun Jan 09, 2022 12:32 pm

Hi Guys,

I may have misinterpreted the advice below.
Like brumbie said you can't have two worksheet_change events so you'd have to combine the code rather than posting one complete worksheet_change snippet on top of another.

It's generally better practice to break things up into sub routines, stick them in a module then call them when needed. Makes your code a lot easier to read and tweak.

My interpretation (from Google) was that I needed to combine modules. As I see it I now need to include the new Worksheet_Change code (as noted and somehow incorporate this under the one Worksheet_Change heading. So essentially we are all under the code as seen. In other words if I go to my worksheet and press View code it will show my current Worksheet_Change code that works well. As part of this flow I now need to add in the new code as part of the original Worksheet_Change flow.

With the code below would I need to remove the Private Sub Worksheet_Change(ByVal Target As Range) as this would be a second change code and not acceptable as noted?

Dim currentMarket As String, marketSelected As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim timeFromStart As Date, beforeStart As Boolean, secondsFromStart As Integer
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
If Left([D2], 1) = "-" Then
timeFromStart = Mid([D2], 2)
beforeStart = False
Else
timeFromStart = [D2]
beforeStart = True
End If
secondsFromStart = (Hour(timeFromStart) * 3600) + (Minute(timeFromStart) * 60) + Second(timeFromStart)
If Not beforeStart Then secondsFromStart = -secondsFromStart
If [A1] <> currentMarket Then marketSelected = False
currentMarket = [A1]
If secondsFromStart <= -600 And Not marketSelected Then
marketSelected = True
[Q2] = -1
End If
Application.EnableEvents = True
End If
End Sub
Sussexguy44
 
Posts: 55
Joined: Thu Oct 14, 2021 9:43 am

Re: Formula TIMEVALUE

Postby Captain Sensible » Tue Jan 11, 2022 4:17 pm

Sussexguy44 wrote:With the code below would I need to remove the Private Sub Worksheet_Change(ByVal Target As Range) as this would be a second change code and not acceptable as noted?


Unfortunately it's rarely that simple as the code you posted has declared variables like Dim currentMarket As String, marketSelected As Boolean etc and all need to be included. Plus mixing and matching code snippets is rarely a good idea as the coding syntax in each is different i.e. one uses .Range("A1").Value to specify cells whereas the other uses [A1] syntax. The example sheets are mainly there as examples rather than fully fledged working modules to stick together, they're mainly there to show how to code rather tahn working examples to tweak a number here and there.

Might be worth taking a bit of time to look thru the code to see if you can understand the logic behind each step, as soon as you grasp those basics the coding side becomes a lot easier to tweak your own sheets.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Formula TIMEVALUE

Postby Captain Sensible » Tue Jan 11, 2022 4:19 pm

If lines of the code seem a bit complicated most people on here will be happy to explain the reasoning behind them.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Formula TIMEVALUE

Postby brumbie » Tue Jan 11, 2022 8:34 pm

The codes seem to conflict with each other anyway as the first code wants to "do stuff" at 8 minutes before the off, but the second code wants to move on to the next market at 10 minutes before the off of the current market, thus preventing the original code to initiate?
Like the Captain says, combining codes can sometimes be complicated.

If you were just looking to initiate the NEXT market at 10 minutes before the off of THAT market then go here:

Excel > Log Multiple Sheets Quick Link > Auto select option = At specific time before off, then adjust the next column across to suit your needs.
Hope this helps.
brumbie
 
Posts: 197
Joined: Tue Dec 28, 2010 2:00 am
Location: Brisbane,Australia

Re: Formula TIMEVALUE

Postby Captain Sensible » Tue Jan 11, 2022 9:18 pm

I read the code as moving to the next market 600 seconds after the scheduled off time.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Formula TIMEVALUE

Postby brumbie » Tue Jan 11, 2022 11:20 pm

Yes, on testing, that is the case. I suppose there must be a reason for doing things this way and not using the conventional method.
brumbie
 
Posts: 197
Joined: Tue Dec 28, 2010 2:00 am
Location: Brisbane,Australia

Re: Formula TIMEVALUE

Postby Captain Sensible » Thu Jan 13, 2022 3:47 pm

brumbie wrote:Yes, on testing, that is the case. I suppose there must be a reason for doing things this way and not using the conventional method.


Yep always found the auto select markets works fine and is reliable. I only write custom routines for when I want to skip closed markets or move once a race has finished. Using a set time it'd probably be easier to use the built in functions.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Formula TIMEVALUE

Postby Sussexguy44 » Thu Jan 13, 2022 5:39 pm

Thanks for your replies. I realise that combining these two functions of code is a step too far. The 600 seconds after the start of the race is there as I need to go in play and this ensures the race does. The main reason I need the next race VBA to do this is that the Auto select markets (with a time that can be set in Minutes) does not work. It may be some VBA I have! My only option is to select "Wait until the scheduled off of the previous race" however this does not allow the race to go in play.

I wont try to combine the VBA below with other VBA so I will attempt to use the VBA below on its own and see how it works. At present the line
Private Sub Worksheet_Change(ByVal Target As Range) is highlighted in yellow so I will try to see if I can sort it.

Dim currentMarket As String, marketSelected As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim timeFromStart As Date, beforeStart As Boolean, secondsFromStart As Integer
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
If Left([D2], 1) = "-" Then
timeFromStart = Mid([D2], 2)
beforeStart = False
Else
timeFromStart = [D2]
beforeStart = True
End If
secondsFromStart = (Hour(timeFromStart) * 3600) + (Minute(timeFromStart) * 60) + Second(timeFromStart)
If Not beforeStart Then secondsFromStart = -secondsFromStart
If [A1] <> currentMarket Then marketSelected = False
currentMarket = [A1]
If secondsFromStart <= -600 And Not marketSelected Then
marketSelected = True
[Q2] = -1
End If
Application.EnableEvents = True
End If
End Sub
Sussexguy44
 
Posts: 55
Joined: Thu Oct 14, 2021 9:43 am

Re: Formula TIMEVALUE

Postby Captain Sensible » Thu Jan 13, 2022 7:16 pm

Sussexguy44 wrote:The main reason I need the next race VBA to do this is that the Auto select markets (with a time that can be set in Minutes) does not work. It may be some VBA I have! My only option is to select "Wait until the scheduled off of the previous race" however this does not allow the race to go in play.


I've never seen anyone having a problem with Auto Select market not working, it simply forces BA to the next market with whatever setting you've set. If you check when it's running you'll see the market change and the only reason that the new market is not being sent to excel will be because of an error between the link and that'll usually be due to any VBA you have coded within the sheet effectively freezing the link between BA and excel.

If you want to code up your own navigation between markets you need to start to think how you're going to handle abandoned or delayed markets etc as one race may mean you miss a day's racing. All the data you need is being sent from BA to excel, time to race, off time of next race etc so you can code things up but it's worth thinking thru exactely what you want to do before starting. Betting in play but switching markets 600 seconds after the off is no good if the race is delayed or there's only a few minutes gap between races.

Usually if you want to move markets after a race is finished you'd generally log the offtime when the market goes inplay (E2), start a timer to ensure there isn't a false start or suspension then once the market has been suspended (F2?) and the expected amount of time has passed by you amend Q2 to move onto the next market rather than setting a set time after the expected off.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Formula TIMEVALUE

Postby Captain Sensible » Thu Jan 13, 2022 7:19 pm

Here's some code showing what I mean

viewtopic.php?f=5&t=8897#p46099
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Previous

Return to Discussion

Who is online

Users browsing this forum: Bing [Bot] and 21 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.