State Machine Spreadsheet

[CallumPerry]

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.

[spreadbetting]

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?

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.

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.

[spreadbetting]

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

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

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

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

[CallumPerry]

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

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

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:

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

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

[spreadbetting]

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

So the code is simply stepping through the numbers 1 to 3 and executing the code inbetween each step and in each step i holds the value from 1 to 3. We can reference that number in a cell address simply by referencing a cell like so

Code: Select all

Dim i As Long
For i = 1 To 3
Worksheets("Bet Angel").Range("A" &  i).Value = i
Next i

So our code now cycles thru A1 to A3. Obviously your number of runners may be more than three so to find that last row with data we'd use the following to put the row number into a variable called LastRow, obviously if your runner data is in a different column than A change it

Code: Select all

Dim LastRow As Long
LastRow = Worksheets("Bet Angel").Cells(Rows.Count, "A").End(xlUp).Row

Still with me, putting that lot to loop thru column A from A1 to the last cell would be as follows

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

[nigelk]

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.

[CallumPerry]

@ 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!

BetAngel_Cal_Template.xls

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

So instead I’ve had to use:

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

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.

[spreadbetting]

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

So instead I’ve had to use:

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

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.

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

Select Case StateCell means we're looking at what value StateCell is holding if it holds "B" then the Case "B" part is true so that part of the code with be executed so you'd simply put

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]

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.

[spreadbetting]

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.

[CallumPerry]

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.

Case "B"

Call BackOne
Worksheets("Bet Angel").Range("J32").Value = "C"

Case "C"

etc etc.......

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.

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