Venturing into BA's Excel world

[firlandsfarm]

OK, have set up two sheets in the Multiple wb one for the Donor sheet and one for the Master sheet. Have configured Guardian so that it automatically scrolls down the list of markets that are ordered in Guardian as Donor Market 1, Master Market 1, Donor Market 2, Master Market 2, … Donor Market X, Master Market X and that works fine. Have my VBA script embedded in both sheets to clear them on change of market … off we go. The screen has an irritating flicker but it seems to be working, bets are being placed . Check the Market Reports and … multiple bets! (not many, 3 or so for a selection instead of just the 1 targeted). I mull this over and awake this morning having thought, the flicker, I wonder if there is an infinitesimal period of null value in cell B2 (the Market cell) that is causing the VBA to think "wait a minute, didn't that cell value just change? I've got work to do clearing everything" and so multiple bets! And then I read Shaun's comment …

Worksheet_Change works fine, but it's a bit over enthusiastic because it sticks it's nose in whenever anything anywhere changes, for each change. A cell updates and Worksheet_Change pops its head up and asks if there's anything you need doing.

… so thanks Shaun, that comment may explain the flicker and the reason for the multiple bets. If so then the Worksheet_Change route is probably a no-no because the worksheet is constantly changing but I have a few thoughts to test so will go off and report back later.

Thanks for your continuing interest guys.

[CallumPerry]

Yeah the flicker will 100% be because you're refreshing rapidly and each time the sheet is refreshing because of the worksheet_change. This may be what that longer code you saw was handling. It wrote a value to cell B2 a single time and then your code checks whether this one cell changes. Only having to check the status of one cell would make things run much smoother, perhaps get rid of the flicker entirely and make your system that little bit tighter (single bets per single triggers). Good luck.

[firlandsfarm]

Yeah the flicker will 100% be because you're refreshing rapidly and each time the sheet is refreshing because of the worksheet_change.

OK, had a rethink of the logic but can't get it to work probably because of my lack of knowledge. I thought … I'm not placing any bets beyond the start time but I let Guardian refresh the wb until 5 seconds after Start Time so how about I clear everything when the Countdown goes negative … simple. Err, no! I thought this would do it …

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

I might have known that would be too simple. I've tried every variant of "<0" I could think of but to no avail. (BTW in my experimenting I discovered a 'strangeness' in the F4 cell and wondered if that might be it but no.) (F4 shows a negative countdown beyond the start time but Excel left to itself shows negative time values as "#######".) So I thought make [G4]=F4 and format that as a simple number … that's great until it goes negative when the formatting of G4 switched, all by itself, to a (negative) time format! I tried to compare the Time element of Now() with the Start Time in F3 but again no go. What's annoying is that if I run the macro from the marco dialogue box it works fine so it's clearly a lack of a trigger that is preventing it. Any thoughts please.

[firlandsfarm]

OK, I seem to have solved it by sticking with my original code and increasing the refresh time to 1 second, I guess that gives everything the chance to settle down between refreshes. I have had one double bet placed but that may have been because I was fiddling with it! Yeah I know, could be dangerous.

I'm still confused why the code in my last post wouldn't trigger. Any thoughts?

[spreadbetting]

Yeah the flicker will 100% be because you're refreshing rapidly and each time the sheet is refreshing because of the worksheet_change.

OK, had a rethink of the logic but can't get it to work probably because of my lack of knowledge. I thought … I'm not placing any bets beyond the start time but I let Guardian refresh the wb until 5 seconds after Start Time so how about I clear everything when the Countdown goes negative … simple. Err, no! I thought this would do it …

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

I might have known that would be too simple. I've tried every variant of "<0" I could think of but to no avail. (BTW in my experimenting I discovered a 'strangeness' in the F4 cell and wondered if that might be it but no.) (F4 shows a negative countdown beyond the start time but Excel left to itself shows negative time values as "#######".) So I thought make [G4]=F4 and format that as a simple number … that's great until it goes negative when the formatting of G4 switched, all by itself, to a (negative) time format! I tried to compare the Time element of Now() with the Start Time in F3 but again no go. What's annoying is that if I run the macro from the marco dialogue box it works fine so it's clearly a lack of a trigger that is preventing it. Any thoughts please.

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

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

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