Using DoEvents (VBA) with triggered betting

[Tyrobot]

This should be easy enough to test but I'd be grateful for any replies nonetheless. Having read a couple of old related posts I'm assuming that DoEvents is fit for this purpose. Take the following sub:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count = 16 Then
        '[if bet conditions are met]
        Application.EnableEvents = False
        [Q5] = "BACK" 'trigger
        [R5] = 1000 'odds
        [S5] = 2 'stake
        Sleep 1000 'pause for 1 second *
        DoEvents 'yield to allow BA to interact with Excel
        '[do other stuff]
        Application.EnableEvents = True
    End If
End Sub

With a refresh rate in BA of 1 second, will DoEvents as used in the above example allow BA to reliably read the trigger and submit the bet BEFORE the next line of code is executed?

Or should DoEvents be called repeatedly in a loop instead in this case (e.g. Do While bet ref is empty)? I gather that such loops are CPU-heavy and so could potentially be an issue when logging multiple markets, which is the point of all this.

Thanks
------------------
* To use the Sleep function the following statement must be inserted at the top of the module (or if declared as Public instead, any module can use the function):

#If VBA7 Then
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Other pausing methods are commonly used but it appears that they have certain drawbacks:
https://stackoverflow.com/questions/1544526/how-to-pause-for-specific-amount-of-time-excel-vba

[alrodopial]

what are you trying to achieve?

[Captain Sensible]

With a refresh rate in BA of 1 second, will DoEvents as used in the above example allow BA to reliably read the trigger and submit the bet BEFORE the next line of code is executed?

As far as I'm aware BA won't actually submit the bet until any coding on the sheet has been processed and BA will then submit any trigger data from the worksheet.

Might be easier ways to achieve what you're trying to do.

[Captain Sensible]

Also worth noting BA sends data to excel in two chunks, the first is the market data A:P then the next data is the betting data T: X (possibly more if you have additional columns ticked) I run my coding from the second set of data as the sheet will be fully populated from that BA refresh then, otherwise you may be betting from betting old data, matched bets etc

[Tyrobot]

Thank you both.

what are you trying to achieve?

The above sub is only illustrative. I'm trying to work out how to best place two separate bets on the same selection in-play in quick succession. It's hard to explain but this actually seems to be a reasonable way of implementing my system rather than consolidating the bets. Of course, I may be overlooking something - any further questions may help.

Might be easier ways to achieve what you're trying to do.

The possibility had occurred to me. Let me sleep on it and I'll see what I might add here to elucidate.

Cheers

[GaryRussell]

This might not be what you need, but if they are both back bets you can place two bets at the same time on the same selection by entering two pipe delimited sets of odds and stakes.

eg. 2|5 in odds column and 10|20 in stake column.

[alrodopial]

if you don't want to monitor how your bets change you can do it by placing the bets using com:

Code: Select all

Dim WithEvents ba As BettingAssistantCom.ComClass

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target(1, 1).Address = "$A$1" Then
        If Target(1, 1) = "" Then Exit Sub
       
        Call SetBA(shtNmb)
        ref_1st = ba.placeBet(selectionID_1st, trigger_1st, odds_1st, stake_1st, False)
        ref_2nd = ba.placeBet(selectionID_2nd, trigger_2nd, odds_2nd, stake_2nd, False)
    End If
End Sub

Private Sub SetBA(sheetNumber)
    If ba Is Nothing Then
        Set ba = New BettingAssistantCom.ComClass
        ba.tabIndex = sheetNumber - 1
    End If
End Sub

selectionID id is: the row in Excel minus 5
Of course there is a way to monitor your bets with com but it requires more macros

The DoEvents will only bring you problems

When using excel use the: Range("Q5:T5") = Array(trigger, odds, stake)
minimize reading/writing to excel

[Tyrobot]

Thanks guys for steering me clear. FYI these are the old posts I dug up on DoEvents, I may have read too much into them or the woes just weren't reported:
viewtopic.php?p=12086#p12086
viewtopic.php?p=3035#p3035

This might not be what you need, but if they are both back bets you can place two bets at the same time on the same selection by entering two pipe delimited sets of odds and stakes.

eg. 2|5 in odds column and 10|20 in stake column.

Thanks Gary, I caught a previous post where you mentioned this little trick. Unfortunately for me, either of the two bets could be a back or a lay.

if you don't want to monitor how your bets change you can do it by placing the bets using com

This is really great to know, thank you! Admittedly though I'm noting this down as a last resort because my hands are full already without having to learn how to use the COM for the first time.


Like CS I suspect that there's a simpler solution here. I guess I'll rack my brain some more (and now I can't help feeling that I'm missing the bleeding obvious!) I was going to add the following background info before the latest replies came in. Maybe it can still help.

I'm trading tennis in-play and I want to place 2 bets as quickly as possible once the next point in the match is scored and updated in Excel. Reason being that the first of these bets (Bet1) is a trade exit and the second (Bet2) is a trade entry.

Bet1 is triggered by the score update and if it weren't for Bet2 then BA should read Q5 as soon as the sub ends. So my original post stems from the task of getting BA to submit Bet1 then somehow submit Bet2 as quickly as possible.

So if anybody ever has any more ideas then please shoot. I really appreciate all the help anyhow.

[alrodopial]

maybe the below will do the job:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target(1, 1).Address = "$A$1" Then
        If Target(1, 1) = "" Then Exit Sub
   
        ' 2nd bet:
        If Range("AA5") = "Y" And Range("AB5") = "" Then
            If Range("T5") = "PENDING" Then ' bet reff clm, must have data from a bet (1st bet)
                Range("Q5:T5") = Array(trigger, odds, stake, "") ' 2nd bet data
                Range("AB5") = "Y" ' used as flag that the 2nd bet been placed so no further 2nd bets will be placed
            End If
        End If
       
        ' If conditions for 1st bet are met then:
        Range("Q5:S5") = Array(trigger, odds, stake) ' 1st bet data
        Range("AA5") = "Y" ' used as flag that the 1st bet is placed
       
    End If
End Sub

2nd bet will be placed only if 1st has been placed and will be placed at the second update time, eg after 1 sec from 1st bet if your refresh rate is 1 sec (can be set much smaller)
Tennis has 7 secs delay (?) in running so you should not wait for the 1st bet's ref to be returned
Just an idea, not tested, modify it and ask anything you want

[alrodopial]

forgot it:
if you trade and place "exit trade" bets then your two bets must be opposite BACK & LAY or LAY & BACK

[Tyrobot]

Many thanks for the effort mate.

My confusion with this is, what worksheet change event might run this sub without the score changing in Excel? I guess it should work if the first bet was placed on a previous point in the match, but what about getting both bets placed on the same point?

Without BA being able to update the score or read the trigger cells while the VBA runs, I'm kinda stuck right now.

[alrodopial]

there are two ways for updating ba+excel:
1. "full stream" ticked, ba+excel will update whenever there is the slightest change in available odds/amounts, so inrunning every eg.<0.5sec (sooner in real match)
2. "full stream" unticked, ba+excel will update at the set refresh rate, eg every 1sec even if there is no change at available odds/amounts

the 16columns change will happen in these cases (or "A1" cell change as in my example, it's the same)

[alrodopial]

pics

[Tyrobot]

the 16columns change will happen in these cases (or "A1" cell change as in my example, it's the same)

I'd dismissed the idea of using the 16-column block thinking that I'd need the odds (or at least B3) to quickly change and before the next point is played, which wouldn't necessarily be timely enough. But thanks to your reply I've just realized that C2 and D2 keep ticking away after the off! Might be just what I needed... Thanks Alrodopial

[Tyrobot]

2. "full stream" unticked, ba+excel will update at the set refresh rate, eg every 1sec even if there is no change at available odds/amounts

the 16columns change will happen in these cases (or "A1" cell change as in my example, it's the same)

(^ This is what I intended to quote in my previous post - we need an edit button!)

One final quick question for now if anyone would be so kind (as to save me £6!) - my searching on the forum turned up empty.

My Excel workbook is designed for multiple markets using a Coupon, and I understand that it isn't possible to set individual refresh rates that way. But can I still use the Q2 special trigger to change the refresh rate for all the markets?