In very basic terms all I want is for the code to happen on the sheet named "Bet Angel" not sure if the whole code should be located in the sheet code for sheet 1 (Bet Angel) or as a module?
Simply put, i'm just trying to make the system automatically recognise that if the StateCell changes letter then it should execute a sub and move to the next letter else do nothing.
For example:
Current StateCell = A
If ActionCell = "BACK" then Call sub BACKONE () AND StateCell changes to "B" ready for the next statecell change to happen. Every millisecond there is no change to the StateCell will result in the ActionCell staying as "A", in other words it just gets ignored until there is a change.
Does that make sense? It will get more complex and move through letters in a specific order whilst calling different sub routines at each stage. I will be able to work that out for myself once I understand how you simply go from A to B if the ActionCell shows something specific.
Callum.
State Machine Spreadsheet
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
What most people do is to have the Worksheet_Change routine running inside the sheet that Bet Angel is sending data to , that way the Worksheet_Change will be triggered when your cells change.CallumPerry wrote: ↑Wed May 02, 2018 10:16 amIn very basic terms all I want is for the code to happen on the sheet named "Bet Angel" not sure if the whole code should be located in the sheet code for sheet 1 (Bet Angel) or as a module?
In order to keep things neat and tidy you'd usualy the stick all your other coding into modules and call them as and when they're required. But if your code is relatively small there's no reason you can't have all your code running within the worksheet_change routine. Whichever way you decide you do need the worksheet_change code to be under the Bet Angel worksheet otherwise no code would get triggered when cells change.
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
Try something simple like this to see if you can get things up and running. Had a look at some of your earlier code and you may be getting confused with some of the variables and what they do. Once you've amended a variable the change is only done in memory so still needs to be sent back to the sheet.
Under the Bet Angel worksheet add
All that will do is track any changes that occur in the worksheet bet angel and if they do it Calls a sub routine called StateMachine, we turn off events so the sub routine doesn't continually get triggered and grind your PC to a halt. There are ways we can make that routine more efficient but best left out for now.
We add the StateMachine routine into a module
Under the Bet Angel worksheet add
Code: Select all
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False 'Turn off events so changes to cells don't retrigger event
Application.Calculation = xlCalculationManual ' set calculations to manual whilst code runs
Call StateMachine
Application.Calculation = xlCalculationAutomatic 'turn on calculations
Application.EnableEvents = True 'Turn back on events
End Sub
We add the StateMachine routine into a module
Code: Select all
Option Explicit
Sub StateMachine()
Dim StateCell As Variant, ActionCellVal As Variant 'Declare our variables
StateCell = Worksheets("Bet Angel").Range("A1").Value 'set the variable
ActionCellVal = Worksheets("Bet Angel").Range("A2").Value 'set the variable
Select Case StateCell
Case "A"
If ActionCellVal = "BACK" Then
Worksheets("Bet Angel").Range("A1").Value = "B" 'Write new value of A1 to sheet
End If
End Select
-
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
All is looking good so far guys! I have the following typed up in a module and a second module dedicated to the BackOne, LayOne and ClearBrandOne subs. Strangely, as I was experimenting a line at a time I had to keep calling the StateMachine sub from the macro button but after a while it just started doing it automatically as intended? I have no idea why this happened but hoping it works when I wire it up to a master template (which I will start later tonight). Maybe due to the sheet code part?? Any ideas???
Now this long-winded set of (Back-only-at-the-moment) instructions is only for the first brand/runner/whatever you want to call it. If I copy and paste the code in the same module for the second runner (and more) can I just type it as:
Or is there an easier, simpler and less time-consuming way of presenting everything? Can you have the same Case names (A, B, C, D etc) for different runners or do these terms have to be unique also i.e. Case "A Runner One"?
Nearly there!! Once I have these last few questions answered I think I will be able to quietly go about my work in the background and be able to share a pretty powerful template with everyone really soon!
Callum
Code: Select all
Option Explicit
Sub StateMachine()
Dim StateCell As Variant, ActionCellVal As Variant, BAActionCell As Variant 'Declare our variables
StateCell = Worksheets("Bet Angel").Range("A1").Value 'set the variable
ActionCellVal = Worksheets("Bet Angel").Range("A2").Value 'set the variable
BAActionCell = Worksheets("Bet Angel").Range("A3").Value ' set the variable
Select Case StateCell
Case "A"
If StateCell = "A" Then ClearBrandONE
If ActionCellVal = "BACK" Then
Worksheets("Bet Angel").Range("A1").Value = "B"
If ActionCellVal = "LAY" Then
Worksheets("Bet Angel").Range("A1").Value = "E"
End If
End If
Case "B"
If Worksheets("Bet Angel").Range("A1").Value = "B" Then
Call BackOne
If Worksheets("Bet Angel").Range("A1").Value = "B" Then
Worksheets("Bet Angel").Range("A1").Value = "C"
End If
End If
Case "C"
If Worksheets("Bet Angel").Range("A1").Value = "C" And BAActionCell = "PLACED" Then
Call ClearBrandONE
If Worksheets("Bet Angel").Range("A1").Value = "C" And BAActionCell = "PLACED" Then
Worksheets("Bet Angel").Range("A1").Value = "D"
End If
End If
Case "D"
If Worksheets("Bet Angel").Range("A1").Value = "D" Then
Call LayOne
If Worksheets("Bet Angel").Range("A1").Value = "D" Then
Worksheets("Bet Angel").Range("A1").Value = "A"
End If
End If
End Select
End Sub
Code: Select all
Option Explicit
Sub StateMachine()
Dim StateCellONE As Variant, ActionCellONEVal As Variant, BAActionCellONE As Variant
Dim StateCellTwo As Variant, ActionCellTwoVal As Variant, BAActionCellTwo As Variant 'New line
StateCellOne = Worksheets("Bet Angel").Range("A1").Value 'set the variable
ActionCellOneVal = Worksheets("Bet Angel").Range("A2").Value 'set the variable
BAActionCellOne = Worksheets("Bet Angel").Range("A3").Value ' set the variable
StateCellTwo = Worksheets("Bet Angel").Range("B1").Value 'set the variable 'New line
ActionCellTwoVal = Worksheets("Bet Angel").Range("B2").Value 'set the variable 'New line
BAActionCellTwo = Worksheets("Bet Angel").Range("B3").Value ' set the variable 'New line
Select Case StateCellONE
Case "A"
If StateCellOne = "A" Then ClearBrandONE
If ActionCellOneVal = "BACK" Then
Worksheets("Bet Angel").Range("A1").Value = "B"
If ActionCellOneVal = "LAY" Then
Worksheets("Bet Angel").Range("A1").Value = "E"
End If
End If
End Select
'Everything below is new
Select Case StateCellTwo
Case "A"
If StateCellTwo = "A" Then ClearBrandONE
If ActionCellTwoVal = "BACK" Then
Worksheets("Bet Angel").Range("B1").Value = "B"
If ActionCellTwoVal = "LAY" Then
Worksheets("Bet Angel").Range("B1").Value = "E"
End If
End If
End Select
End Sub
Nearly there!! Once I have these last few questions answered I think I will be able to quietly go about my work in the background and be able to share a pretty powerful template with everyone really soon!
Callum
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
CallumPerry wrote: ↑Wed May 02, 2018 4:37 pm
Or is there an easier, simpler and less time-consuming way of presenting everything? Can you have the same Case names (A, B, C, D etc) for different runners or do these terms have to be unique also i.e. Case "A Runner One"?
Rather than having separate variables for each runner what you normally do is stick the data into an array and then loop thru each element (i.e. runner) at a time and adjust the data each time using the same Statemachine sub routine but obviously with different cell addresses.
So a simple Loop in VBA would be something like
Code: Select all
Dim i As Long
For i = 1 To 3
MsgBox i
Next i
Code: Select all
Dim i As Long
For i = 1 To 3
Worksheets("Bet Angel").Range("A" & i).Value = i
Next i
Code: Select all
Dim LastRow As Long
LastRow = Worksheets("Bet Angel").Cells(Rows.Count, "A").End(xlUp).Row
Code: Select all
Sub looping()
Dim LastRow As Long, i As Long
LastRow = Worksheets("Bet Angel").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
MsgBox " Cell A" & i & " Contains " & Worksheets("Bet Angel").Range("A" & i).Value
Next i
End Sub
So with your Statemachine routine you'd simply stick the routine within a loop and reference cells in relation to your varaiable held in i, i.e.
StateCellOne = Worksheets("Bet Angel").Range("A" & i).Value 'set the variable
ActionCellOneVal = Worksheets("Bet Angel").Range("A" & 1 + i ).Value 'set the variable
Might be a bit to get your head around looping thru arrays so start off simply just to undersatnd how they work
How about if you let the spreadsheet itself do most of the hard work?
On the attached i want to back 1 runner only if:
1. The price of either runner is less than the other
2. The book % is less than 100.75%
3. The wom is greater than 65%
4. Do nothing if both runners match all the criteria
Broken down into a series of 1 and 0's H6 look to see if all criteria have been met, they have. As a last check before i place a bet, i want to check that only 1 runner meets all the criteria, and looking at H12, there is at least 1 more.
Change either runners price and a msgbox pops up saying which runner to back.
Looking at the code, we only need 2 if statement for 2 runners.
On the attached i want to back 1 runner only if:
1. The price of either runner is less than the other
2. The book % is less than 100.75%
3. The wom is greater than 65%
4. Do nothing if both runners match all the criteria
Broken down into a series of 1 and 0's H6 look to see if all criteria have been met, they have. As a last check before i place a bet, i want to check that only 1 runner meets all the criteria, and looking at H12, there is at least 1 more.
Change either runners price and a msgbox pops up saying which runner to back.
Looking at the code, we only need 2 if statement for 2 runners.
You do not have the required permissions to view the files attached to this post.
-
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
@ spreadbetting: This looks like something I could easily implement when I get the first draft of the template complete to make it more efficient! I will do some research!!
@nigelk: I like how the first line of code is a way of cutting the code short, I could use this if the letter or some other condition remains the same to make it more efficient.
I’ve created a new draft template and to be fair, it ALMOST works perfectly! Cell B31 is the ActionCell (You can see how it works via cells B34 and B37 simply enough), J32 is the StateCell and O9 is the BAActionCell.
For some reason it skips over Case “J” which is the error identifier and for some reason it doesn’t do the ‘Lay’ side of instructions?
If you Type A in J32 and make the ActionCell show “BACK” then it will transition from B to C, type ”PLACED” in O9 (D) and then make the ActionCell show “LAY” (E) and type “PLACED” again in O9 to loop back round to (A). This shows that it is successfully launching sub LayOne so there’s no problems there BUT if you try and do the inverse:
Start at (A) and type “LAY” in the ActionCell (Should go to (F)) nothing happens?
Also the code doesn’t recognise:
So instead I’ve had to use:
Any way of making this tidier? After this bug is fixed the spreadsheet will be ready to share so other members can run repeated automated strategies via the spreadsheet that dynamically reloads and help me look for possible bugs.
This has proven to be more frustrating than I originally thought it would be.
Callum.
@nigelk: I like how the first line of code is a way of cutting the code short, I could use this if the letter or some other condition remains the same to make it more efficient.
I’ve created a new draft template and to be fair, it ALMOST works perfectly! Cell B31 is the ActionCell (You can see how it works via cells B34 and B37 simply enough), J32 is the StateCell and O9 is the BAActionCell.
For some reason it skips over Case “J” which is the error identifier and for some reason it doesn’t do the ‘Lay’ side of instructions?
If you Type A in J32 and make the ActionCell show “BACK” then it will transition from B to C, type ”PLACED” in O9 (D) and then make the ActionCell show “LAY” (E) and type “PLACED” again in O9 to loop back round to (A). This shows that it is successfully launching sub LayOne so there’s no problems there BUT if you try and do the inverse:
Start at (A) and type “LAY” in the ActionCell (Should go to (F)) nothing happens?
Also the code doesn’t recognise:
Code: Select all
Case "B"
If StateCell = "B" Then
Call BackOne
If StateCell = "B" Then
StateCell = "C"
End If
End If
Code: Select all
Case "B"
If Worksheets("Bet Angel").Range("J32").Value = "B" Then
Call BackOne
If Worksheets("Bet Angel").Range("J32").Value = "B" Then
Worksheets("Bet Angel").Range("J32").Value = "C"
End If
End If
This has proven to be more frustrating than I originally thought it would be.
Callum.
You do not have the required permissions to view the files attached to this post.
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
CallumPerry wrote: ↑Thu May 03, 2018 2:48 pmAlso the code doesn’t recognise:So instead I’ve had to use:Code: Select all
Case "B" If StateCell = "B" Then Call BackOne If StateCell = "B" Then StateCell = "C" End If End If
Any way of making this tidier? After this bug is fixed the spreadsheet will be ready to share so other members can run repeated automated strategies via the spreadsheet that dynamically reloads and help me look for possible bugs.Code: Select all
Case "B" If Worksheets("Bet Angel").Range("J32").Value = "B" Then Call BackOne If Worksheets("Bet Angel").Range("J32").Value = "B" Then Worksheets("Bet Angel").Range("J32").Value = "C" End If End If
This has proven to be more frustrating than I originally thought it would be.
Callum.
The code will recognise it but all you're doing with If StateCell = "B" Then StateCell = "C" is setting the value of StateCell to "C" you're not writing it back to the sheet so it can be picked up on the next refresh.
There's no need for you to add lines like below within your select case statements a the Case "B" has done that for you
Code: Select all
If Worksheets("Bet Angel").Range("J32").Value = "B" Then
Code: Select all
Case "B"
Call BackOne
Worksheets("Bet Angel").Range("J32").Value = "C"
Case "C"
etc etc.......
You also don't need to keep adding Application.EnableEvents = False to your other routines as that gets turned off within the Worksheet_Change routine which initailly runs your code, but turning it back on within your code you may start to run into problems with events retriggering prematurely when data is written to the sheet. It gets turned back on at the end of the Worksheet_Change routine once all the code has run anyway.
It is worth adding a routine into your module to reset the events manually though just in case you crash the code when tinkering, just add a sub called reset so you can run it if you get problems.
Code: Select all
Sub reset()
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
You should create a thread with all your vb advice sb, top stuff.
It would be a huge shame if it just gets lost, it's not a 5 min job explaining things that well.
It would be a huge shame if it just gets lost, it's not a 5 min job explaining things that well.
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
Nice of you to say, Shaun. I'm just a tinkerer when it comes to VBA and all my knowledge has come from people on forums like this taking time out to explain it to me. I know how frustrating it can be starting out so happy to pass on any knowledge others have given me freely.
-
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
I agree! I really do appreciate all the (free) advice that you are kindly sharing. I have already learned a lot in my short time as a member and it's all because of people on this forum being so helpful!
I have created the first draft of the template which I am about to share in a new thread so it's easy for the wandering lurker of this forum to find.
One more question…
When closing a trade what is the difference between L9 = “LAY” stake = £2 and L9 = “Close_Trade” and stake = £2? When would you ever use the individual command option Close_Trade over just laying?
Callum
I have created the first draft of the template which I am about to share in a new thread so it's easy for the wandering lurker of this forum to find.
I tried simplifying the code to something like above but for some reason it wouldn’t recognise it so for now, I just wanted to get the spreadsheet out for other members to enjoy and test, I have made quite a lengthy bit of code for the template. The bottom line is that it works and should generally run pretty darn fast so i'm happy to share now and take your advice on board to improve things in my own time.Case "B"
Call BackOne
Worksheets("Bet Angel").Range("J32").Value = "C"
Case "C"
etc etc.......
One more question…
When closing a trade what is the difference between L9 = “LAY” stake = £2 and L9 = “Close_Trade” and stake = £2? When would you ever use the individual command option Close_Trade over just laying?
Callum