Hi
I have started using the Excel capablity of Betangel and would like to develop an idea I have further.
I have written a Macro within the Excel spreadsheet that clears all the contents of the "Status" cells, thus allowing another bet to trigger. I have created and assigned a button to the macro so it is easy to run when the market goes inplay. This works fine.
Now I would like to be able to take advantage of the new Guardian multi market function (in version 1.21.0) so that it trades automatically, one race after the other. What I would like to do is to be able to run the above macro after the market has displayed "Suspended" at the end of the race, thus re arming it and enabling it to trade on the next race. How can I monitor the cell that displays "Suspended" and then run the macro when it does so??
Would appreciate it if anyone knows the easy way of doing this??
Thanks in Advance..
Regards
Peter
Clearing status cells - Excel Macros
-
- Posts: 688
- Joined: Wed Apr 15, 2009 5:29 pm
Private Sub Worksheet_Calculate()
If Range("O9").Value = "PLACED" Or Range("O9").Value = "MARKET_SUSPENDED" Or Range("O9").Value = "ERROR" Or Range("O9").Value = "FAILED" Or (Range("M9").Value = 0 And Range("O9").Value = "PLACED_KILL_PENDING") Or (Range("M9").Value = 0 And Range("O9").Value = "PLACING") Then Range("O9").ClearContents
End Sub
Replicate the middle of the text above for as many outcomes as you require. But I urge caution, make sure your calculations are air tight, you don't want to have it go mental on you and running through posting your entire bank etc. And I'd suggest you remove the ERROR and FAILED parts in order to make sure your sheet doesn't create these responses, it's not good to have it trying to clear these over and over again.
If Range("O9").Value = "PLACED" Or Range("O9").Value = "MARKET_SUSPENDED" Or Range("O9").Value = "ERROR" Or Range("O9").Value = "FAILED" Or (Range("M9").Value = 0 And Range("O9").Value = "PLACED_KILL_PENDING") Or (Range("M9").Value = 0 And Range("O9").Value = "PLACING") Then Range("O9").ClearContents
End Sub
Replicate the middle of the text above for as many outcomes as you require. But I urge caution, make sure your calculations are air tight, you don't want to have it go mental on you and running through posting your entire bank etc. And I'd suggest you remove the ERROR and FAILED parts in order to make sure your sheet doesn't create these responses, it's not good to have it trying to clear these over and over again.
Well,
I can't see how that will automatically clear the cells without the code being invoked first? Which I think is what PeterLe is after. Unless I have missed something?
I use this code to clear the cells and link a button to it but I think the question is how to do this without having to manually call the macro or run a loop to monitor particular cells.
I can't see how that will automatically clear the cells without the code being invoked first? Which I think is what PeterLe is after. Unless I have missed something?
I use this code to clear the cells and link a button to it but I think the question is how to do this without having to manually call the macro or run a loop to monitor particular cells.
Code: Select all
Sub Reset_Sheet ()
Sheets("Bet Angel").Select
Range("O6,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").Select
Selection.ClearContents
End Sub
-
- Posts: 688
- Joined: Wed Apr 15, 2009 5:29 pm
Well, It seems to work fine for me I don't have to hit any buttons, press anything.. it simply clears the cell once a bet has been placed, freeing it up to place another one. Is that not what's required?
I can even leave this running all day and come home and still find it placing bets.
BTW, I use the 'and' to clear pending fill or kills or other remnants only when the odds are showing 0.00, as it does when the market is suspended. It's a bit of a bodge job as I could simply refer it to the market suspended cell. Works fine though.
I also recommend the kind people over at the MrExcel.com forums for helping me solve this problem and many others.
I can even leave this running all day and come home and still find it placing bets.
BTW, I use the 'and' to clear pending fill or kills or other remnants only when the odds are showing 0.00, as it does when the market is suspended. It's a bit of a bodge job as I could simply refer it to the market suspended cell. Works fine though.
I also recommend the kind people over at the MrExcel.com forums for helping me solve this problem and many others.
I can't get it working here
After placing a bet it stubbornly refuses to clear the cell. I've closed down and restarted Bet Angel, Excel and reloaded the sheet after placing your code in the project.
Where are you placing the code? In the 'ThisWorkbook', the 'Sheet1(Bet Angel)' object or elsewhere?
After placing a bet it stubbornly refuses to clear the cell. I've closed down and restarted Bet Angel, Excel and reloaded the sheet after placing your code in the project.
Where are you placing the code? In the 'ThisWorkbook', the 'Sheet1(Bet Angel)' object or elsewhere?
-
- Posts: 688
- Joined: Wed Apr 15, 2009 5:29 pm
Open up the visual basic editor, double click on the Bet Angel sheet "Sheet 1 (Bet Angel)".
New window pops up in which to paste the code. Drop down menu on this window top left - "Worksheet". Then paste the code in, this should change the drop down top right to "Calculate". That is all that is required in this window.
At least, it is for me.. works a treat, been out all afternoon and it's still going no cloggage of the status cells.
Does this work?
New window pops up in which to paste the code. Drop down menu on this window top left - "Worksheet". Then paste the code in, this should change the drop down top right to "Calculate". That is all that is required in this window.
At least, it is for me.. works a treat, been out all afternoon and it's still going no cloggage of the status cells.
Does this work?
-
- Posts: 688
- Joined: Wed Apr 15, 2009 5:29 pm
Better to be safe to begin with. The BA chaps leave it this way for a reason - to make sure it's safe to use. This macro is dangerous if you don't have the right money management stuff in the rest of your sheet.PeterLe wrote:Thanks Guys
Really appreciate your help with this...
I'm not an Excel wizard, but I'm going to have a go. (Think I'll transfer all the funds except say £20, into the Aus Wallet Just in case!!! )
Regards
Peter
Also, this will only clear the status cell relating to selection one in the market. (In case that wasn't obvious! apologies) To do the others you have to copy and paste the same and rename the cells for each row.
This link might give others help if they are looking for something similar.
http://www.ozgrid.com/VBA/run-macros-change.htm
http://www.ozgrid.com/VBA/run-macros-change.htm
Hi Nero
Been experimenting with this...and at the risk of being a pain..can I ask for more advice?? ! (",)
Following on from the user "BetAngel" post...where they state that the excel refresh state is slower when having many worksheets open.. I really just want to have the single worksheet file open..(using Guardian to switch markets). Within the worksheet I have created a macro called "Clear_Status"
I can get your code working... but rather than it constantly clearing the contents.. I only want to run the code Just once, Ideally right at the end of the race when it displays "Suspended"..
So I can either use your code..(modified in some way) Or I believe that it is possible to call a Macro from within VBA when a cell displays a certain string..?
If you are able to help..could you explain how I do it in easy steps??! (Still getting the hang of Macros and VBA)
Thank you
regards
Peter
Been experimenting with this...and at the risk of being a pain..can I ask for more advice?? ! (",)
Following on from the user "BetAngel" post...where they state that the excel refresh state is slower when having many worksheets open.. I really just want to have the single worksheet file open..(using Guardian to switch markets). Within the worksheet I have created a macro called "Clear_Status"
I can get your code working... but rather than it constantly clearing the contents.. I only want to run the code Just once, Ideally right at the end of the race when it displays "Suspended"..
So I can either use your code..(modified in some way) Or I believe that it is possible to call a Macro from within VBA when a cell displays a certain string..?
If you are able to help..could you explain how I do it in easy steps??! (Still getting the hang of Macros and VBA)
Thank you
regards
Peter
This is the code I use to reset the status everytime a bet has been placed; it's not a macro, it's code triggered by the worksheet_calculate event:
And you could simply wrap this in an IF statement...
The code should be in the module described below by Nero Tulip » Thu Apr 16, 2009 5:44 pm
Code: Select all
Private Sub Worksheet_Calculate()
Dim i As Integer
For i = 9 To 67 Step 2
If Range("O" & i).Value = "PLACED" Then _
Range("O" & i).ClearContents
Next i
End Sub
Code: Select all
If Range("H1").Value = "Suspended" Then
For i = 9 To 67 Step 2
If Range("O" & i).Value = "PLACED" Then _
Range("O" & i).ClearContents
Next i
End If