Clearing status cells - Excel Macros

Post Reply
PeterLe
Posts: 3715
Joined: Wed Apr 15, 2009 3:19 pm

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
User avatar
TheTub
Posts: 267
Joined: Thu Mar 26, 2009 7:53 pm
Location: Nottinghamshire

The only way I know of doing this would be to run a loop and execute your macro when the 'suspended' notice is displayed.

However, doesn't the spreadsheet clear all these cells when the market is changed?
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

You probably need to put your code in the individual sheet and use the "Worksheet Change Event" to trigger your code when the cell value changes.
User avatar
TheTub
Posts: 267
Joined: Thu Mar 26, 2009 7:53 pm
Location: Nottinghamshire

The 'Worksheet change event' method does not work with these sheets on cells populated via the excel link from Bet Angel.

Unless you have found a way around this?
Nero Tulip
Posts: 686
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.
User avatar
TheTub
Posts: 267
Joined: Thu Mar 26, 2009 7:53 pm
Location: Nottinghamshire

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.

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
Nero Tulip
Posts: 686
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.
User avatar
TheTub
Posts: 267
Joined: Thu Mar 26, 2009 7:53 pm
Location: Nottinghamshire

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?
Nero Tulip
Posts: 686
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?
PeterLe
Posts: 3715
Joined: Wed Apr 15, 2009 3:19 pm

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
Nero Tulip
Posts: 686
Joined: Wed Apr 15, 2009 5:29 pm

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
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.

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.
User avatar
TheTub
Posts: 267
Joined: Thu Mar 26, 2009 7:53 pm
Location: Nottinghamshire

Yup that works.

I tried this method in the past and couldn't get it working. Must have made a simple error.

Thanks for the code.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

This link might give others help if they are looking for something similar.

http://www.ozgrid.com/VBA/run-macros-change.htm
PeterLe
Posts: 3715
Joined: Wed Apr 15, 2009 3:19 pm

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
hmw
Posts: 7
Joined: Wed Apr 15, 2009 9:58 pm

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:

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
And you could simply wrap this in an IF statement...

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
The code should be in the module described below by Nero Tulip » Thu Apr 16, 2009 5:44 pm
Post Reply

Return to “Tips and tricks”