connect via excel - auto bet my selections

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
User avatar
jimibt
Posts: 3641
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

liero1 wrote:
Fri May 10, 2019 4:21 pm
the selection process works well, but I come across the situation that the 'Status' cells won't blank for the new market when excel switches to the next market, that seem to hinder any new bets? ..

Image
you may have to manually intervene there. typically, i'd use a module level variable to identify the current market, then compare that to the market in Bet Angel sheet B1. If this is different, then clear out the cells (i.e. in code). I in fact have a little clear up routine that i use specifically for this purpose. This routine gets called in the Bet_Angel Worksheet_Change event:

Code: Select all

Private Sub ClearBetStatuses()
Dim intRow As Integer, wsSource As Worksheet

    Const BET_NOTIFICATION_COL = 12
    Const BET_STATUS_COL = 15
    Const BET_STATUS_ROW_START = 9
    Const BET_STATUS_ROW_END = 60
    
    Set wsSource = ThisWorkbook.Sheets("Bet Angel")
    wsSource.Range("L6:O6") = ""

    For intRow = BET_STATUS_ROW_START To BET_STATUS_ROW_END Step 2
        wsSource.Cells(intRow + 1, BET_NOTIFICATION_COL) = ""
        wsSource.Cells(intRow, BET_STATUS_COL) = ""
    Next
    
End Sub
User avatar
liero1
Posts: 36
Joined: Mon Sep 24, 2018 3:28 pm

ah thanks, perfect. So i just add it as a module? Testing now.. This is really a headache, what's the point in automating when you need to manually delete the status cells... But this might work well,let's see.

Otherwise maybe just look for the 'inplay' status in G1 and empty those status cells...
User avatar
liero1
Posts: 36
Joined: Mon Sep 24, 2018 3:28 pm

anyone else with this problem, there was a thread here - viewtopic.php?f=31&t=7181
User avatar
jimibt
Posts: 3641
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

liero1 wrote:
Fri May 10, 2019 4:45 pm
ah thanks, perfect. So i just add it as a module? Testing now.. This is really a headache, what's the point in automating when you need to manually delete the status cells... But this might work well,let's see.

Otherwise maybe just look for the 'inplay' status in G1 and empty those status cells...
you can add it as a sub in the same namespace as the bet Angel worksheet_change event... just paste it below, then, call if from the worksheet_change event when the market changes to another market. Below is how i track this change in that event:

so, module level variable:

Code: Select all

Private m_LastEventName As String
then, in the worksheet_change event:

<--- cut

Code: Select all

    Const RACE_NAME_COL = 1
    Const STATUS_ROW = 1

    Set wsSource = ThisWorkbook.Sheets("Bet Angel")
    Set wsCalc = ThisWorkbook.Sheets("Calculations")
    
    Set runnerRange = wsSource.Range("B1:K50")
    Set mainCells = wsSource.Range("B1:G6")
    
    If Not Application.Intersect(runnerRange, wsSource.Range(Target.Address)) Is Nothing Then
        ' change event info on cell contents being updated
        If (m_LastEventName <> mainCells.Cells(STATUS_ROW, RACE_NAME_COL)) Then
               m_LastEventName = mainCells.Cells(STATUS_ROW, RACE_NAME_COL)
               ClearBetStatuses
       end if
    end if
<---cut
User avatar
liero1
Posts: 36
Joined: Mon Sep 24, 2018 3:28 pm

sorry, I think I need a more detailed help here - where do I add the module variable, and where do I find the change event code... Thanks!
User avatar
liero1
Posts: 36
Joined: Mon Sep 24, 2018 3:28 pm

i think i have done it now correctly - it has the reference to a /calculations/ sheet, does anything need to go in there?
User avatar
liero1
Posts: 36
Joined: Mon Sep 24, 2018 3:28 pm

jimibt wrote:
Fri May 10, 2019 4:51 pm
liero1 wrote:
Fri May 10, 2019 4:45 pm
ah thanks, perfect. So i just add it as a module? Testing now.. This is really a headache, what's the point in automating when you need to manually delete the status cells... But this might work well,let's see.

Otherwise maybe just look for the 'inplay' status in G1 and empty those status cells...
you can add it as a sub in the same namespace as the bet Angel worksheet_change event... just paste it below, then, call if from the worksheet_change event when the market changes to another market. Below is how i track this change in that event:

so, module level variable:

Code: Select all

Private m_LastEventName As String
then, in the worksheet_change event:

<--- cut

Code: Select all

    Const RACE_NAME_COL = 1
    Const STATUS_ROW = 1

    Set wsSource = ThisWorkbook.Sheets("Bet Angel")
    Set wsCalc = ThisWorkbook.Sheets("Calculations")
    
    Set runnerRange = wsSource.Range("B1:K50")
    Set mainCells = wsSource.Range("B1:G6")
    
    If Not Application.Intersect(runnerRange, wsSource.Range(Target.Address)) Is Nothing Then
        ' change event info on cell contents being updated
        If (m_LastEventName <> mainCells.Cells(STATUS_ROW, RACE_NAME_COL)) Then
               m_LastEventName = mainCells.Cells(STATUS_ROW, RACE_NAME_COL)
               ClearBetStatuses
       end if
    end if
<---cut
Got it working!! Thanks so much!!
User avatar
jimibt
Posts: 3641
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

perfect :mrgreen:
User avatar
liero1
Posts: 36
Joined: Mon Sep 24, 2018 3:28 pm

Hi

Your solution works perfectly and I have been placing bets fully automated the last two days. A beauty!

Just one thing hope you don’t mind - as the code blanks the global command as well I can’t use the take SP command at the moment. Any way we can amend the code so it doesn’t clear the global command cell?!

Thanks!
User avatar
jimibt
Posts: 3641
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

liero1 wrote:
Sun May 12, 2019 8:33 am
Hi

Your solution works perfectly and I have been placing bets fully automated the last two days. A beauty!

Just one thing hope you don’t mind - as the code blanks the global command as well I can’t use the take SP command at the moment. Any way we can amend the code so it doesn’t clear the global command cell?!

Thanks!
morning... ok, you are obviously using the setup in a slightly different way to me. i my scenario, that function (ClearBetStatuses) only gets called when a NEW event is focussed on -i.e. when Bet Angel changes markets. This is determined by your *Automatically Switch Bet Angel Market* flag and time. However, as i don't know how you are using it, then maybe the best option is to not reset the glogal status cells in side the function. In that case, simply comment out this line:

Code: Select all

'wsSource.Range("L6:O6") = ""
That should do the trick - the global command will be static ;)
User avatar
liero1
Posts: 36
Joined: Mon Sep 24, 2018 3:28 pm

perfect, exactly what I meant. Thanks again..
User avatar
liero1
Posts: 36
Joined: Mon Sep 24, 2018 3:28 pm

hey again.. sorry I am such a vba noob!! How would I amend your code so it fires for every sheet for the market changes and not just the first betangel sheet?...
User avatar
liero1
Posts: 36
Joined: Mon Sep 24, 2018 3:28 pm

me again, found out how to do it - seems to work
User avatar
jimibt
Posts: 3641
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

liero1 wrote:
Sun May 19, 2019 2:02 pm
me again, found out how to do it - seems to work
nice one ;)
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”