Venturing into BA's Excel world

[firlandsfarm]

I don't actually use BA but it's possible that when the countdown is negative it's actually displaying the time as a string rather than number, you could always try to use excels ISTEXT function to catch that.

Code: Select all

If WorksheetFunction.IsText(Range("f4").Value)  Then Range("B9:K68").ClearContents

It is text but I couldn't get it to convert back to a value as a number nor time so I then tried using Left to get the first character and if "-" then clear cells but nope. I then tried making G4 = "Clear" when ready to clear the cells (and it did) but again VBA would not clear the cells. In every instance the cells would clear if I clicked to run the macro but never would VBA do it by a comparison. It's clearly VBA not triggering that's the problem.

There are plenty of ways you can optimise excel to stop the flickering by turning off screen updating and calculations whilst the code is running and also exiting the sub early if there's no need to run thru the whole code. Mainly you're looking to ensure code only runs when it's needed so after it's run you'd usually set a flag so it doesn't run again til it needs to by resetting that flag.

That seems a bit advanced and too complicated for me! The problem here was getting VBA to see the trigger not the saving of milliseconds in the running of the code. If VBA did as it should have done and seen any one of the options I trialled it with there wouldn't have been a problem. I couldn't find anything on the Internet to advise why VBA was not triggering, everything I read said "this will work" but it didn't.

And it does get a lot easier once you start to understand the quirks which aren't as hard as they first appear when you're starting out.

I've found a way around it so let the quirks live on … at the end of the day quirks just cause me to lose interest! I call them bugs and consider the product faulty. I've wasted a day because of quirks and still don't know what the quirk is so could easily waste another day in the future falling foul of the same quirk and no available solution. Doesn't exactly fill me with glee and enthusiasm! Don't get me wrong, I'm grateful for the help you and Shaun have volunteered … it's VBA I'm pissed with and for BA not to clear the Status fields when moving to a new market is a clear and obvious bug. (Should I have posted that on the Rant thread?! )

[spreadbetting]

The code you posted up is just a sub code so it needs something to trigger it off, it won't be constantly checking the worksheet. That's why you'll see people using Worksheet_Change a lot. The worksheet change basically checks the sheet for changes on each refresh and will execute any code within the Private Sub Worksheet_Change(ByVal Target As Range), if you wish to run other sub codes withing that routine you need to Call them.

So the code will constantly check the sheet each refresh

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

to also run our Sub code within that we'd either need to include the code or call the sub routine like the examples below

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("f4").Value < 0 Then Range("B9:K68").ClearContents
If Range("f4").Value < 0 Then Range("O9:AE68").ClearContents

End Sub

Or call it

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)



Call Clear_Cells

End Sub

I'd imagine there's probably some example code to do what you want already on the forum as I doubt you'd be the only person with a problem with status cells not clearing

[Atho55]

I`m with firlands with this one as I also believe that BA as the software owner should be the one implementing the solution and not forum users.

A quick search shows that this problem has been rumbling on since 2010 and we are still no further forward with a solution.

Clear Status Cells.jpg

Other parts of the BA package get regular updates but Excel now looks tired and needs some functionality adding.

Not everyone is a super pc user and much of the snake chatter is above my head and I doubt that I am not the only one.

Before anyone says "why not try to learn it"..... my desk at school had an inkwell in the corner and we wrote using a piece of wood with a nib stuck in the end..

[greenmark]

I`m with firlands with this one as I also believe that BA as the software owner should be the one implementing the solution and not forum users.

A quick search shows that this problem has been rumbling on since 2010 and we are still no further forward with a solution.

Clear Status Cells.jpg

Other parts of the BA package get regular updates but Excel now looks tired and needs some functionality adding.

Not everyone is a super pc user and much of the snake chatter is above my head and I doubt that I am not the only one.

Before anyone says "why not try to learn it"..... my desk at school had an inkwell in the corner and we wrote using a piece of wood with a nib stuck in the end..

The alternative(s) is program it yourself. Excel is a reasonable software to co-opt. Its updated regularly, relatively secure and best of all YOU get to control what goes on. The effort involved in programming is vast, in my experience you're talking £80-90k per person per year overall. And thats not salary, thats hardware, people, admin, professional services etc.

[spreadbetting]

I`m with firlands with this one as I also believe that BA as the software owner should be the one implementing the solution and not forum users.

I'd agree, but this is posted in the excel section not the suggestions or requests. If enough people suggest I imagine it'll be implemented.

[CallumPerry]

Code: Select all

Private Worksheet_Change(ByVal Target As Tange)

Application.Screenupdating = FALSE
Application.Calculation = xlManual

If Range("F4").Value < 0
Then Range("B9:K68").ClearContents

If Range("F4").Value < 0
Then Range("O9:AE68").ClearContents

Application.Calculation = xlAutomatic
Application.Screenupdating = TRUE

End Sub

Load two (only two) markets into Guardian tomorrow. Turn on restrict refresh. Refresh rate at 200m/s and put this above code into your sheet1 module. Let us know what happens. The turning screen updating on and off as well as the sheet calculations will make things quicker usually. I tend to wrap my whole strategy up in this just to make my machine worry about less things, one mini lesson for you. This code should run ridiculously fast, much quicker than 1/5th of a second (200 m/s) so if this works several times you've cracked it. Keep the same refresh settings etc

Just make sure all of your formulae is in code and not on the sheet. The problem could be that the sheet updates much slower than VBA, by the time the sheet has changed its value the code could already have done it's checked and missed a change. Though the values are coming in live from BA so this may be different to just testing with a stationary sheet?? I'll be honest, I'm not sure.

As for making sure your formulae are in code, if you're not sure how to just click 'Record a Macro' and then select the cells that have formulae in (in order if you have chains) and press enter for EVERY cell. You'll see them appear in a new module which you can call from before your very first IF(value<0) line.

[firlandsfarm]

Hi Callum, thanks for your message last Friday … sorry I was busy over the weekend. I copied and pasted your code but am getting a Compile error (see screen grab). You will also notice I don't have a "sheet1" … should there be one or do you just mean the first sheet (my sheet2)? That's where I pasted the code.

ClearStatusCells.JPG

[spreadbetting]

I'd guess the line

Code: Select all

Private Worksheet_Change(ByVal Target As Tange) 

should actually read

Code: Select all

Private Worksheet_Change(ByVal Target As Range)

looks like a mistype of Tange instead of Range.

The other thing is that code will fire on each refresh whilst the condition is true

[firlandsfarm]

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Hi Spreadbetting, thanks for your input and apologies for the delay in responding I was tied up over the weekend.

I understand your comments … I switched away from ...Worksheet_Change … following Shaun's earlier post when he advised the wb will check the Target every time there is a change to any cell in the ws and so slow it down but maybe with Callum's suggestions that can be speeded up enough to make ...Worksheet_Change … viable again.

I also wondered about 'calling' the Sub and actually searched for that with Google but couldn't find anything other than a couple of articles that advised the Sub would be run every time the wb updated so I thought, fair enough … keep looking (for something else!). Is it the use of 'Private Sub' that makes it run at every update?

As for sample code sets in the Forum, yep, there's plenty … all different and all amended by long debate. But I couldn't find one that would scroll through pairs of events.

[spreadbetting]

I understand your comments … I switched away from ...Worksheet_Change … following Shaun's earlier post when he advised the wb will check the Target every time there is a change to any cell in the ws and so slow it down but maybe with Callum's suggestions that can be speeded up enough to make ...Worksheet_Change … viable again.

Yep, everyone has their own way of coding things and Shaun does seem to like timers rather than Worksheet_Change, personally I'd much rather be reacting to when the odds change than hoping I'll drop in at the right time with some timer. But so much depends on what your betting approach entails and if changes in odds or set times are more important. Excel is quite efficient and I simply ensure I exit the Worksheet change routine early if there's no need to run through all the code.

I also wondered about 'calling' the Sub and actually searched for that with Google but couldn't find anything other than a couple of articles that advised the Sub would be run every time the wb updated so I thought, fair enough … keep looking (for something else!). Is it the use of 'Private Sub' that makes it run at every update?

It's the Worksheet_Change that simply runs the code every time something on the worksheet changes, You get Private and Public Sub's which won't affect what you're doing now and may just confuse things to delve into them now.

Calling the sub routine using Call was just the way I learnt to call sub routines but you can simply enter the name of the routine and omit Call, I still do it as it makes any code easy for me to spot. Using sub routines usually makes large amounts of coding easier to tweak. What you normally do to stop code continually running is to set a flag to say the code has run and then reset it later. So the 'flag' could be held in memory or simply written to a cell on the sheet as an indication your code has run. So for instance when you run your code Range("B9:K68").ClearContents
 you may wish to enter "Y" in Cell AA1 and your code would become

Code: Select all

If Range("F4").Value < 0 And  Range("AA1").Value <>"Y" Then Range("B9:K68", "O9:AE68").ClearContents: Range("AA1").Value ="Y"

That way our code will still run but only fire Clearcontents once rather than each refresh. Obviously we'd need to reset cell AA1 to ensure we'd fire next market but that depends what criteria you'd want to reset it ready for the next market. Something like this may be all that's needed to simply reset it when F4 is greater than 0

Code: Select all

If Range("F4").Value > 0 And  Range("AA1").Value <>"N" Then Range("AA1").Value ="N"

As for sample code sets in the Forum, yep, there's plenty … all different and all amended by long debate. But I couldn't find one that would scroll through pairs of events.

Not sure what you mean by pairs of events.

[firlandsfarm]

... personally I'd much rather be reacting to when the odds change than hoping I'll drop in at the right time with some timer. But so much depends on what your betting approach entails and if changes in odds or set times are more important.

Thanks for such a detailed response spreadbetting. I'm not doing anything fancy like trying to scalp with multiple trades I'm just wanting to place some bets using Excel to work through a short algorithm and then place some bets, if appropriate, about 10 seconds before the start (i.e. as close as I can get to final odds).

My problem has been finding the right combination of instruction set and trigger to clear down all selection specific cells (not only the Status cells but also the Selections, Odds, Matched/Unmatched bets cells etc.). My problem is these cells have not always fully cleared and can cause my algorithm to misbehave!

It's the Worksheet_Change that simply runs the code every time something on the worksheet changes, You get Private and Public Sub's which won't affect what you're doing … you may wish to enter "Y" in Cell AA1

Yeah, as I said I found articles that suggested a sub would update when the sheet updates but that doesn't surprise me. I find about 50% of what I read on the Internet when it comes to computing doesn't work! As I will not be looking to place any bets in play I'm simply looking to clear the Selection specific cells as soon as the event goes in play. I was thinking of using something similar to your AA1 sugestion and have set cell G3 = IF(F3>NOW()-INT(NOW()),"Bet","Clear") and then when it equals "Clear" that's the trigger for my sub to clear the cells. Then when the next event is loaded the IF condition is met and the cell's contents revert to "Bet" until the start time is past. I'm not happy with using F4 as I've said earlier, it behaves in a 'funny' way when it goes negative!

Not sure what you mean by pairs of events.

I'm comparing two markets from the same event (hence I referred to them as "a pair") so I am using the first two sheets from the Multiples wb and auto-binding my way down the list of events.

I think I'm getting there! I'm now using …

Code: Select all

Private Sub Worksheet_Calculate()

If Range("G3").Value = "Clear" Then
Application.EnableEvents = False
Range("B9:K68").ClearContents
Range("O9:AE68").ClearContents
Application.EnableEvents = True
End If

End Sub

… with my 'G3' formula above and it's working. I found the structure and design of the code in another thread in the Forum. I'm next going to try and follow Callum's suggestion that I put my formulas into VBA.

Thanks for all your interest and help.

[firlandsfarm]

Just make sure all of your formulae is in code and not on the sheet ...if you're not sure how to just click 'Record a Macro' and then select the cells that have formulae in (in order if you have chains) and press enter for EVERY cell. You'll see them appear in a new module which you can call from before your very first IF(value<0) line.

Hi Callum, you can see from my latest response to spreadbetting I have a code that's working. I can see that your code would also work with the G3 trigger. I am interested in the variances between the code I am using (extracted and adapted from another thread in the Forum) and yours. I notice 'my' code uses "Application.EnableEvents" to turn the sheet On/Off and wraps it within the If statement whereas you use "Application.Calculation" to turn the Sheet On/Off and have the If statements inside the On/Off. Is there a significant difference or is it just a matter of preference?

I haven't yet put the formulas into VBA but will do so now that I have something that's working. One thing I think I have learned in this process is that it seems that sometimes code does not automatically execute (my original attempts) and sometimes it does auto-execute (my current code) … where do I put the Formulas Module and how do I activate it?

[spreadbetting]

No problem, good to see you're making progress. On thing to point out is if you put all your code into VB your Worksheet_Calculate routine won't trigger unless there's some calculation on the worksheet. BAsically Worksheet_Change fire when a cell changes on the sheet, Worksheet_Calculate fire whan a calculation is done i.e. some formula runs on the sheet i.e. =A1+A2, IF..... etc If you stick the formulas into VB they won't be on the sheet to fire the routines.

Regarding Application.EnableEvents we usually turn those off when our code runs to ensure our code doesn't go into endless loops and continually firing, i.e. if events were True each time something was written to the sheet like your stakes for a bet it would set off another worksheet_change routine because the sheet had changed. So we simply wrap the code between

Code: Select all

Application.EnableEvents = False 

Code goes here

Application.EnableEvents = True 

And the same applies for the Application.Calculation we set it to manual at the start of the code and turn it back to automatic at the end otherwise every change to a formula would mean they kept recalculating. They're not always needed as it obviously depends on what your sheet does but when starting out it's not much hassle to turn them off and back on at the end of the code, also worth having error trapping to especially when starting as it's easy to mess things up when you're in the tinkering stages. There are other Application. settings like stopping the screen updating too but with calculations and events off it's not really needed unless you write lots of cells one at a time with vba.

Getting values from two sheets isn't that hard if the workbooks are open in the same instance of excel you'd simply need to reference them fully like.

Code: Select all

  Workbooks("Betting.xlsm").Worksheets("Sheet1").Range("A2").Value =  Workbooks("Bets.xlsm").Worksheets("Sheet1").Range("A2").Value

[CallumPerry]

Hi firlandsfarm,

Apologies, I typed the code up at work using word rather than Excel but still a very silly error to not spot, as spreadbetting pointed out it should be Range not Tange. As he also pointed out Shaun likes timers; I’m a fan of worksheet_change but that’s just down to individual preferences, you use whatever you favour.

I find worksheet_change fine because I like my system to repeatedly look for the trigger, it’s simply a good starting point. My more advanced stuff I’m currently trying do only look for a change in certain cells for time but your system seems less complex and layered so it should work well. Your system is probably loads simpler and more profitable anyways lol

So to conclude, I would use worksheet_change to ALWAYS monitor for changes, add in calculation/screenupdating lines for speed and then put things in ‘IFs’. Doing this will speed things up massively because instead of checking all your triggers as a whole (every update), your system may find the first part FALSE and then stop and wait for the next update.

Truthfully, I have never come across EnableEvents. From a quick google search there’s a quick explanation here: https://www.mrexcel.com/board/threads/a ... on.329759/
Use enable events, calculation and screenupdating. Anybody else used all three with larger systems or is this just being over the top?

As for your formulas. I would say add a new module and put all of them here or just have a sub routine called ‘Formulas’. Inside this sub routine try to make a simple trigger at first which you can test. Inside the master sub routine write ‘Call Formulas’. Anywhere inside the master sub press F8 repeatedly and watch how the code ‘flows’. I think you said you looked at calling subs in the above messages? What do you mean by your code sometimes auto-executes?

Cal

[CallumPerry]

^^^ Ahh a few more questions answered. I feel like I'm coming back into this thread having missed a few messages so let me know what stage you're at and maybe attach some workbooks as you progress so they can be tweaked and sent back with some explanations.