Circling through horse racing markets

[mak]

I just can't understand how it counts, without be more specific, or without declare something like

for every refresh add 1

Don't know if i can make myself clear with this...

how the program "understand" that with counter+1 i mean every refresh?

Nevertheless, thanks Captain

[danjuma]

mak something as simple as



Dim counter As Currency

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub

counter = counter + 1

If counter > 10 And [J3].Value = "L" Then
counter = 0
[Q2].Value = -5
ElseIf counter > 10 Then
counter = 0
[Q2].Value = -1
End If


End Sub

will continually loop through the markets every 10 refreshes, you just need to add further conditions within the code if you don't want it to move on until bets have been placed etc

Hi CS,

I see your code above did not include Application.EnableEvents... This is where I tend to get confused at times. What difference does this make if included or not included in a code? Just trying to understand this VBA thingy. Thanks

[Captain Sensible]

I just can't understand how it counts, without be more specific, or without declare something like

for every refresh add 1

Don't know if i can make myself clear with this...

how the program "understand" that with counter+1 i mean every refresh?

Nevertheless, thanks Captain

You need to look at all of the coding to see how it updates the value counter on each refresh. All the code does is declares a portion of memory within excel to hold a variable called counter

Dim counter As Currency

We now have a variable that will hold any value we want and that can be set or amended on each refresh by the program.



Private Sub Worksheet_Change(ByVal Target As Range) If Target.Columns.Count <> 16 Then Exit Sub

counter = counter + 1


End Sub

This piece of code will be executed on every Worksheet_Change i.e. everytime excel refreshes the data from BA. If the counter = counter + 1 wasn't included within the Worksheet_Change sub routine it wouldn't update.

So effectively we have the Worksheet_Change routine kicking off everytime the market is refreshed in excel and that in turn then tells the variable counter to add 1 to itself everytime the worksheet change routine is triggered

[mak]

Dim counter As Currency

We now have a variable that will hold any value we want and that can be set or amended on each refresh by the program.



definitely not there yet..
Is there any other example within this code where we could set it to hold some more value(s).Not that i can think of something but could we have

Dim counter as Currency
Dim x as ?
...
counter = counter + 1
x=?

Captain if you get bored i will understand

[Captain Sensible]

[quote="danjuma]

Hi CS,

I see your code above did not include Application.EnableEvents... This is where I tend to get confused at times. What difference does this make if included or not included in a code? Just trying to understand this VBA thingy. Thanks[/quote]

I took out Application.EnableEvents so it wouldn't confuse things

I'm a bit of a VBA novice too so hopefully someone will come along and explain it better. But as far as I can see we turn of certain events to stop the program from getting into endless loops because of the way Worksheet_Change is fired.

So say our worksheet_change fires because of a change in cell A1, if within our subroutine we do something that causes A1 to alter this will trigger of another worksheet_change routine and we could end up in an endless loop causing the progra to freeze. To stop that from happening we tell excel not to output these changes until the end of the routine

Others like Application.screenupdating and
Application.Calculation = xlCalculationManual are used to stop the excel sheet from updating until the code has ended to speed up things as if we have lots of formulas within the sheet and lots of code the routine may take a lot longer if excel is recalculating after each part of the code and having to output those results to the screen

[Captain Sensible]

Dim counter As Currency

We now have a variable that will hold any value we want and that can be set or amended on each refresh by the program.



definitely not there yet..
Is there any other example within this code where we could set it to hold some more value(s).Not that i can think of something but could we have

Dim counter as Currency
Dim x as ?
...
counter = counter + 1
x=?

Captain if you get bored i will understand

Yes we can add other variables if we want to

Dim is just short for dimension and is just VBA's way of reserving an area of memory to hold these variables we can Dim them as Integers (ie whole numbers), Decimal (decimals ) , Strings (text), Date (date and time) and so on .

If you wanted to output these variables to the sheet you just need to add a line like

[X1].Value= counter

[Captain Sensible]

mak if you try the code

Dim counter As Currency

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub

[X1].Value = counter

counter = counter + 1

[X2].Value = counter
If counter > 10 And [J3].Value = "L" Then
counter = 0
[Q2].Value = -5
ElseIf counter > 10 Then
counter = 0
[Q2].Value = -1
End If


End Sub


This will show the values of counter into X1 and X2 on the screen, it'll show the original value of counter when we enter the code at X1 and then show the increased value at X2 after the program has done the counter= counter +1 part of the code

[mak]

Hey Captain
Thanks for the lessons..

for the time being i can understand that for example with

Dim counter As Currency
counter = counter + 1....

excel can count each refresh because of the Worksheet_Change...and in mind is something generic and not specific

i can understand when (in others code) i see something
Dim lastrace As String
where somewhere in the code later you set it
lastrace = Cells(1, 1).Value

this is specific
dim something & set it later...

Are there any others no specific variables or something that we can hold their value or something beside refreshes of BA?

My answer would be no, but i know i can't answer the question...

[Captain Sensible]

Hey Captain
Thanks for the lessons..

for the time being i can understand that for example with

Dim counter As Currency
counter = counter + 1....

excel can count each refresh because of the Worksheet_Change...and in mind is something generic and not specific

i can understand when (in others code) i see something
Dim lastrace As String
where somewhere in the code later you set it
lastrace = Cells(1, 1).Value

this is specific
dim something & set it later...

Are there any others no specific variables or something that we can hold their value or something beside refreshes of BA?

My answer would be no, but i know i can't answer the question...

Not sure what you mean by specific and generic or what you mean by "something that we can hold their value or something beside refreshes of BA?"

We can declare variables inside or outside that worksheet_change coding we don't even need to declare (Dim) the variables it's just considered good programming practice to declare the variables as it means we can reserve that memory for them and also set them to hold the type of data we want them to i.e. String would hold text , Integer holds whole number etc. There aare lots of different variables we can declare from Global variables that will hold their value, multidimensional arrays that could retain values specific to each market we had entered and so on.

So whilst we can declare variables at any time we need some way to utilise these values and thats why the majority of coding you'll see is in the within the worksheet_change as that lets us execute code after BA has refreshed the market and changed any prices etc. We're not limited to worksheet_change to run our code we can use macro button, have code execute when a sheet opens etc but the majority of stuff you'll see is worksheet_change as thats the most obvious way we can set off our code to run when data within the market has changed

[danjuma]

CS,

Thank you most kindly for this in depth lesson/explanation. I have found it most useful and it has certainly made things clearer for me.

Now let's go and see whether I can get my code working the way it should.

[mak]

Captain thanks from me also

Danjuma good luck

[danjuma]

...

Danjuma good luck

Thanks m8

[danjuma]

Ok CS, or anyone else who can help please

I pieced together the code below

Code: Select all

Dim counter As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
With ThisWorkbook.Sheets(Target.Worksheet.Name)

Range("Q2").Value = ""
Worksheets("Place").Range("Q2").Value = ""

If Worksheets("Place").Range("X1").Value = 0 And Worksheets("Place").Range("J3").Value <> "L" Then
Worksheets("Place").Range("Q2").Value = -1
Range("Q2").Value = -1
ElseIf Worksheets("Place").Range("X1").Value = 0 And Worksheets("Place").Range("J3").Value = "L" Then
Worksheets("Place").Range("Q2").Value = -5
Range("Q2").Value = -5
End If

If Range("X2").Value = 1 Then
counter = counter + 1
End If
If counter > 10 And Range("J3").Value = "L" Then
counter = 0
Range("Q2").Value = -5
ElseIf counter > 10 Then
counter = 0
Range("Q2").Value = -1
End If
End With
Application.EnableEvents = True
End Sub

I have a sheet named 'Win' for the win market, another sheet named 'Place' for the place market, both in the same workbook. The code is placed in sheet Win.

Cell X1 in sheet Win is 1 if the race in the win market is the same as the race in the place market, and 0 if not.
Cell X2 in sheet Win is 1 if no bets have been placed in any market, and 0 if a bet as been placed in either market.

Cell X1 in sheet Place is 1 if the race in the win market is the same as the race in the place market, and 0 if not.

Now what I was trying to achieve with the code is for the code to first establish the same race has been loaded in the Win and Place markets (so working on the same race). If a bet has been placed in any of the markets, load next market, and if not, monitor the markets for say 10 refreshes and load next market. And continue doing this until all the markets have bets or end of last race.

The code is working to a certain extent, but not smoothly or efficiently. Basically loading the races erratically. For exampleThe same race might have been loaded in both markets, but then one of the markets will load the next race regardless (before any monitoring is done) and goes through a few loops before coming back to sync with the same race in the other market.

So would be very grateful if someone could tidy this up for me please to run more smoothly.

Many thanks

[Captain Sensible]

Haven't got time at the moment to look thru it properly but the bits

Range("Q2").Value = ""
Worksheets("Place").Range("Q2").Value = ""

If Worksheets("Place").Range("X1").Value = 0 And Worksheets("Place").Range("J3").Value <> "L" Then
Worksheets("Place").Range("Q2").Value = -1
Range("Q2").Value = -1
ElseIf Worksheets("Place").Range("X1").Value = 0 And Worksheets("Place").Range("J3").Value = "L" Then
Worksheets("Place").Range("Q2").Value = -5
Range("Q2").Value = -5
End If

need tidying up no need for reseting Q2 as that'll clear on refresh anyway so the bits

Range("Q2").Value = ""
Worksheets("Place").Range("Q2").Value = ""

could be removed

The bit

If Worksheets("Place").Range("X1").Value = 0 And Worksheets("Place").Range("J3").Value <> "L" Then
Worksheets("Place").Range("Q2").Value = -1
Range("Q2").Value = -1
ElseIf Worksheets("Place").Range("X1").Value = 0 And Worksheets("Place").Range("J3").Value = "L" Then
Worksheets("Place").Range("Q2").Value = -5
Range("Q2").Value = -5
End If

Will continually fire in changes to Q2 in both markets as the main condition is determined by X1 being 0 in the place sheet so if they don't match both sheets will change market which would mean by definition the next two markets loaded won't be in sync and so on until they possibly catch up at the last market.

You're placing 1 in place X1 when we have a match but not using it any time within your coding, I'd use this to be another condition to start your counter code. I'd let the win market be the driving sheet and only move on both sheets once all your conditions have been met rather than the way you're doing it at the moment by amending Q2 on both sheets just because the markets don't match, hopefully it makes some sense to you

[danjuma]

Thanks for the reply CS.

I have amended the code as follows, following your suggestion

Dim counter As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
With ThisWorkbook.Sheets(Target.Worksheet.Name)

If Range("X1").Value = 0 And Range("J3").Value <> "L" Then
Range("Q2").Value = -1
ElseIf Range("X1").Value = 0 And Range("J3").Value = "L" Then
Range("Q2").Value = -5
End If

If Range("X1").Value = 1 And Range("X2").Value = 0 And Range("J3").Value <> "L" Then
Range("Q2").Value = -1
Worksheets("Place").Range("Q2").Value = -1
ElseIf Range("X1").Value = 1 And Range("X2").Value = 0 And Range("J3").Value = "L" Then
Range("Q2").Value = -5
Worksheets("Place").Range("Q2").Value = -5
End If

If Range("X1").Value = 1 And Range("X2").Value = 1 Then
counter = counter + 1
End If
If counter > 10 And Range("J3").Value = "L" Then
counter = 0
Range("Q2").Value = -5
Worksheets("Place").Range("Q2").Value = -5
ElseIf counter > 10 Then
counter = 0
Range("Q2").Value = -1
Worksheets("Place").Range("Q2").Value = -1
End If
End With
Application.EnableEvents = True
End Sub

This is what is now happening.
Let's say I have race 1 to 10 in the quick pick list for the win market, and the place equivalent 1 to 10 in the place market. I link race 1 to sheet Win first, then while linking race 1 in the place market to sheet Place, sheet Win would have jumped to next race (same thing happens whether I link the place market first before the win market). Sheet win continues jumping through the races until it comes back to race 1 and then in sync with race 1 in the place market. It then stays to check conditions, and then jump to next race (race 2). Sheet Place also then jumps to race 2, but then remains on race 2, while sheet Win jumps through all the races again and come back to race 2 to sync with sheet Place. And so it goes on.

Any suggestions or ideas please? In the mean time, I am cracking my brains as well. Many Thanks