Clearing the Bet Status Cells in Excel

Help improve Bet Angel.
Post Reply
Atho55
Posts: 637
Joined: Tue Oct 06, 2015 1:37 pm
Location: Home of Triumph Motorcycles

Sa7med, that`s an excellent idea.

BA, if you already have solutions of many kinds assuming you have resolved the requests, why not bundle them ALL into a folder with a description of what each one does so the user can select the one that looks to be the best fit.
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

sa7med wrote:
Tue May 19, 2020 1:44 pm
It's just that there's 1000s of ways to implement it under many different conditions and you're asking for it to be done the way you want it. If you upload a version of your excel sheet and describe in steps exactly what you want to be done with it I can have a go.
Thanks sa7med, it seems to me that I am trying to achieve something so basic (and fundamental) that people aren't really grasping what it is! I'm not wanting code to clear the cells, I have a sub for that ... I'm just looking for how I call that sub to clear the cells. I have a standard BA/Excel multi sheet workbook reduced to just two sheets (for two markets) with some cell formula in one of the sheets making the comparisons and triggering the bets (the 'active' sheet). The only VBA is this sub to clear the standard cells …

Code: Select all

Option Explicit

Sub ClearCells()
'
' Clear all cells on Market Change
'
' Keyboard Shortcut: Ctrl+Shift+C
'
' ClearRunners
    Range("B9:K68").Select
    Selection.ClearContents
'
' ClearBetsData
    Range("O9:AE68").Select
    Selection.ClearContents
    '
' Clear bet placement confirmations
    Range("O6,L10,L12,L14,L16,L18,L20,L22,L24,L26,L28,L30,L32,L34,L36,L38,L40,L42,L44,L46,L48,L50,L52,L54,L56,L58,L60,L62,L64,L66,L68").Select
    Selection.ClearContents
End Sub
The code is within the 'active' sheet where the calcs are done and the bets triggered. It works fine using the keyboard shortcut but I can't get it to fire automatically within VBA when the Market changes. I have tried using this code within the 'active' sheet (this code is popular on the Internet as a solution) …

Code: Select all

Private Sub Worksheet_Change(byval target as range)
Sheets("Bet Angel P").Activate
If Not Intersect(target.address, Range("$B$1")) is Nothing Then ClearCells() 
End If 
End Sub
I've tried it with and without a "Call" and with and without the "()" and with and without the Sheets/Activate (as per Shaun's suggestion in another thread) but no joy.

I have tried (another popular Internet solution) ...

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Bet Angel P").Activate
    If Target.Address = "$B$1" Then
        Call ClearCells
    End If
End Sub
Again I have tried it with and without the variances as outlined with the first 'solution'.

And I have tried this code (also popular on the Internet) …

Code: Select all

Public Sub Worksheet_Change(ByVal target As Range) 
Dim intersection As Range 
Set intersection = Intersect(target, Range("$B$1")) 
If Not intersection Is Nothing Then Call ClearCells()
End Sub
That one did produce a result but it also raised a problem whereby repeated bets were fired ... I'm only looking for one bet for each selection that meets my criteria. I think I have discovered why it fires repeated bets. The BA cells such as B1 etc appear to be constant but they are not, they are refreshed as per the BA refresh rate so although the contents of B1 (the Market) appear constant until a new market is linked it isn't it's constantly refreshed and therefore constantly changes. It's a bit of an educated guess based on what I have observed.

I have tried using all Market variable cells to ID a new market without success. My latest attempt this afternoon after Aus has finished is to incorporate a system clock using VBA and comparing that with the cell F3 (Event Start) and clearing the cells when the scheduled start time has passed. I have read that this can be resource hungry but I thought it doesn't need to be milli-second accurate so maybe incorporating a 1 second loop would keep resource demands low. Another alternative I was going to look at is to have a While control again based on the Event Start.

And Rostov kindly posted this possible solution in another thread which I have yet to try ...
rostov wrote:
Tue May 19, 2020 11:06 am
Try this in the sheet tab.

Code: Select all

Private Sub Worksheet_Calculate()
Static OldVal As Variant

Application.EnableEvents = False

If Range("$B$1").Value <> OldVal Then
    OldVal = Range("$B$1").Value
     Call ClearCells
End If

Application.EnableEvents = True

End Sub
But I go back to my reason for raising this as a Suggestion. All I am suggesting is that something as basic as not carrying forward erroneous data when the Market changes should be in the software, nothing more. I am not talking of anything fancy such as automatic active trading with multiple bets and the complications that may bring … just the removal of the runner specific data when the Market changes. Runner names are cleared when the Market changes, bet references are cleared when the Market changes, bet Info is cleared when the market changes … why is the Status column not cleared when the Market changes? It's not logical, that's why I say it's a bug! :)
Last edited by firlandsfarm on Wed May 20, 2020 6:38 am, edited 2 times in total.
Bet Angel
Bet Angel
Bet Angel
Posts: 3999
Joined: Tue Apr 14, 2009 3:47 pm

Here's a macro we have used that detects market change and clears the cells.

Code: Select all

Dim OldMarketId As String

Private Sub Worksheet_Change(ByVal Target As Range)
    ' cells C2:C6 will be the last range to be updated during the update process
    If Target.Address = "$C$2:$C$6" Then
        If Range("A1") <> OldMarketId Then
            ' new market, so remember market id
            OldMarketId = Range("A1")
            ' clear the global cells
            Range("L6:O6").ClearContents
            ' clear betting command
            Range("L9,L11,L13,L15,L17,L19,L21,L23,L25,L27,L29,L31,L33,L35,L37,L39,L41,L43,L45,L47,L49,L51,L53,L55,L57,L59,L61,L63,L65,L67").ClearContents
            ' clear odds
            Range("M9,M11,M13,M15,M17,M19,M21,M23,M25,M27,M29,M31,M33,M35,M37,M39,M41,M43,M45,M47,M49,M51,M53,M55,M57,M59,M61,M63,M65,M67").ClearContents
            ' clear stakes
            Range("N9,N11,N13,N15,N17,N19,N21,N23,N25,N27,N29,N31,N33,N35,N37,N39,N41,N43,N45,N47,N49,N51,N53,N55,N57,N59,N61,N63,N65,N67").ClearContents
            ' clear status
            Range("O9,O11,O13,O15,O17,O19,O21,O23,O25,O27,O29,O31,O33,O35,O37,O39,O41,O43,O45,O47,O49,O51,O53,O55,O57,O59,O61,O63,O65,O67").ClearContents
        End If
    End If
End Sub
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Bet Angel wrote:
Tue May 19, 2020 2:35 pm
sa7med wrote:
Tue May 19, 2020 1:44 pm
It's just that there's 1000s of ways to implement it under many different conditions and you're asking for it to be done the way you want it.
Spot on. We occasionally have requests for cell clearing into support and, when clarified, every request is different.

The idea of the green coloured cells in Excel is that they can be populated with customer's own formulae - if those cells were cleared when switching markets then all the formulae would be wiped out too. So you'd create a situation where the spreadsheet required manual intervention on each market, or required VBA code to populate it (in which case why not also use VBA to clear it exactly as required).
Some ask for just the status column to be wiped when switching markets, but that would cause any betting commands left in columns L/M/N to trigger immediately when given a market. That may suit a few people, but certainly not everyone, and you'd think that Automation Rules could do the same without even bothering with Excel for simple scenarios like that. Others ask for Status and Odds to be cleared, but not Stake. And I'm sure there must be some poeple that want Stake cleared but not Odds!

We're happy to take a look at auto-clearing if we can have a consensus as to how it should work, but we're yet to see more than a few people that want the same behaviour. Normally offering a solution like this, just moves the problem somewhere else until VBA is the only solution. Excel is wonderful with VBA, but limited without it.
BA, I was only suggesting the "O" cells are cleared. I appreciate your concerns if the "L", "M" and "N" were cleared but I'm not suggesting that … I'm simply suggesting that the "O" cells, the Bet Prevention cells, are cleared just as the "B", Selection Names, cells are cleared, the "P" - "S" Bet Reference cells are cleared and the "T" - "AE" Bet Info cells are cleared. I have not suggested the "L", "M" and "N" cells be cleared, they are not the same, they do not prevent the placing of bets on the new Market. There would be NO need for manual intervention, no wiping of formula or values in those cells. I appreciate your concern about "any betting commands left in columns L/M/N to trigger immediately when given a market" why not have it as a "do you want to clear [these] cells when the Market changes … Yes/No" so those who don't want them cleared don't have to.
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

sa7med wrote:
Tue May 19, 2020 2:39 pm
Perhaps there could be an automation rule to clear them in guardian so that people can set up their own rules for clearing. Just a thought
That would be a solution, I was thinking of just a tickbox in the standard spreadsheet
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Atho55 wrote:
Tue May 19, 2020 2:58 pm
BA, if you already have solutions of many kinds assuming you have resolved the requests, why not bundle them ALL into a folder with a description of what each one does so the user can select the one that looks to be the best fit.
Good suggestion Atho, just as Guardian and Servant rules are posted.
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Bet Angel wrote:
Tue May 19, 2020 3:26 pm
Here's a macro we have used that detects market change and clears the cells.

Code: Select all

Dim OldMarketId As String

Private Sub Worksheet_Change(ByVal Target As Range)
    ' cells C2:C6 will be the last range to be updated during the update process
    If Target.Address = "$C$2:$C$6" Then
        If Range("A1") <> OldMarketId Then
            ' new market, so remember market id
            OldMarketId = Range("A1")
            ' clear the global cells
            Range("L6:O6").ClearContents
            ' clear betting command
            Range("L9,L11,L13,L15,L17,L19,L21,L23,L25,L27,L29,L31,L33,L35,L37,L39,L41,L43,L45,L47,L49,L51,L53,L55,L57,L59,L61,L63,L65,L67").ClearContents
            ' clear odds
            Range("M9,M11,M13,M15,M17,M19,M21,M23,M25,M27,M29,M31,M33,M35,M37,M39,M41,M43,M45,M47,M49,M51,M53,M55,M57,M59,M61,M63,M65,M67").ClearContents
            ' clear stakes
            Range("N9,N11,N13,N15,N17,N19,N21,N23,N25,N27,N29,N31,N33,N35,N37,N39,N41,N43,N45,N47,N49,N51,N53,N55,N57,N59,N61,N63,N65,N67").ClearContents
            ' clear status
            Range("O9,O11,O13,O15,O17,O19,O21,O23,O25,O27,O29,O31,O33,O35,O37,O39,O41,O43,O45,O47,O49,O51,O53,O55,O57,O59,O61,O63,O65,O67").ClearContents
        End If
    End If
End Sub
Thanks Guys, I'll give it a go … but with "L", "M" and "N" removed! :)
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Whilst on the subject of clearing cells is there any reason why the selections rows are not always cleared?
SelectionsRows.JPG
You do not have the required permissions to view the files attached to this post.
Bet Angel
Bet Angel
Bet Angel
Posts: 3999
Joined: Tue Apr 14, 2009 3:47 pm

firlandsfarm wrote:
Tue May 19, 2020 3:40 pm
Whilst on the subject of clearing cells is there any reason why the selections rows are not always cleared?
Probably Excel locking out cell updates when a market change occurs. If you're live editing something in Excel while it's linked to another application, then Excel can prevent external updates. Also if there's a really slow macro running in Excel then that can block updates too. Nothing we can do about that in the Bet Angel code.
Atho55
Posts: 637
Joined: Tue Oct 06, 2015 1:37 pm
Location: Home of Triumph Motorcycles

Firstly, BA, thanks for the Macro.

Secondly, you have assumed incorrectly that I have a working knowledge of VBA otherwise you would have:

1) included instructions on how to install it (least desireable)
2) attached a spreadsheet with it already contained within it.

Do you now see what the issue is. Not everbody sits looking at code everyday or has a desire to do so which unfortunately places the onus back on yourselves if you wish to satisfy the needs of some.

The end...
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Bet Angel wrote:
Tue May 19, 2020 3:50 pm
firlandsfarm wrote:
Tue May 19, 2020 3:40 pm
Whilst on the subject of clearing cells is there any reason why the selections rows are not always cleared?
Probably Excel locking out cell updates when a market change occurs. If you're live editing something in Excel while it's linked to another application, then Excel can prevent external updates. Also if there's a really slow macro running in Excel then that can block updates too. Nothing we can do about that in the Bet Angel code.
Understood but that's why I included the "B" - "K" cells in my 'clearing' sub because anything left in there from a previous market really screws up my calculations for the next market.
Bet Angel
Bet Angel
Bet Angel
Posts: 3999
Joined: Tue Apr 14, 2009 3:47 pm

Atho55 wrote:
Tue May 19, 2020 4:59 pm
attached a spreadsheet with it already contained within it.
Here's the template spreadsheet with the macro attached, but it is intended as an example - if it clears more or less than required then you'll need to use the macro editor built into Excel to adapt it. It's worth investing a bit of time learning the basics of the macro editor as it unlocks so much potential in Excel. You don't need coding experience, once you can navigate around the macro editor it's usually just a case of copy/pasting snippets of code from the internet.
You do not have the required permissions to view the files attached to this post.
Bet Angel
Bet Angel
Bet Angel
Posts: 3999
Joined: Tue Apr 14, 2009 3:47 pm

firlandsfarm wrote:
Tue May 19, 2020 5:44 pm
Understood but that's why I included the "B" - "K" cells in my 'clearing' sub because anything left in there from a previous market really screws up my calculations for the next market.
There's no harm in adding lines to clear columns B to K to that macro I gave you earlier. It'll only trigger when the market id changes, so runs only once per market change, so you're free to adapt the macro to do whatever is required.
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Bet Angel wrote:
Tue May 19, 2020 5:54 pm
There's no harm in adding lines to clear columns B to K to that macro I gave you earlier. It'll only trigger when the market id changes, so runs only once per market change, so you're free to adapt the macro to do whatever is required.
Yes I know, I had every intention of adapting it to my own needs thanks that's why I said I would remove the references to "L", "M" and "N" cells! :) My point wasn't to ask if I could it was to say that although people tell me the Selections cells are cleared when the Market changes that's not always so and additional action is required to make sure. You will see that the sub I posted clears everything except the "L", "M" and "N" cells!
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Bet Angel wrote:
Tue May 19, 2020 5:50 pm
Here's the template spreadsheet with the macro attached, but it is intended as an example - if it clears more or less than required then you'll need to use the macro editor built into Excel to adapt it. It's worth investing a bit of time learning the basics of the macro editor as it unlocks so much potential in Excel. You don't need coding experience, once you can navigate around the macro editor it's usually just a case of copy/pasting snippets of code from the internet.
Hi all, just as a note of caution … the spreadsheet provided by BA above will clear all the "L","M" and "N" cells as well as the "O" (Bet Status) cells. So if you use it as is you will lose any entry (amount or formula) for the Bet Placing, Odds and Stakes. Should you not want to do that then right click on the worksheet tab and choose View Code when you should see the code. You will notice some is coloured Green, these are the coder's notes describing what the code following does. You will see they refer to ...

' clear the global cells
' clear betting command
' clear odds
' clear stakes
' clear status

Each is followed by a "Range(…).ClearContents" command … remove the commands for the cells you DO NOT want to clear. So if you only want to clear the "O" (Bet Status) cells then remove all the other ClearContents commands leaving just the "clear status" command.
Post Reply

Return to “Suggestions”