Clearing status cells - Excel Macros

Example spreadsheets and comments on example spreadsheets.
Post Reply
PeterLe
Posts: 3263
Joined: Wed Apr 15, 2009 3:19 pm

Wed Apr 15, 2009 10:49 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

Thu Apr 16, 2009 11:47 am

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: 352
Joined: Wed Apr 15, 2009 11:00 pm

Thu Apr 16, 2009 12:56 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

Thu Apr 16, 2009 1:09 pm

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: 207
Joined: Wed Apr 15, 2009 5:29 pm

Thu Apr 16, 2009 1:36 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

Thu Apr 16, 2009 2:57 pm

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: 207
Joined: Wed Apr 15, 2009 5:29 pm

Thu Apr 16, 2009 3:00 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

Thu Apr 16, 2009 3:15 pm

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: 207
Joined: Wed Apr 15, 2009 5:29 pm

Thu Apr 16, 2009 5:44 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: 3263
Joined: Wed Apr 15, 2009 3:19 pm

Thu Apr 16, 2009 6:04 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

Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest