State Machine Spreadsheet

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

I have tried the code but it doesn't seem to work? I have created a new blank document and just used the slightly modified code below to test out where it is faulty but can't seem to figure out wh y it still wont work.

I have used Private Sub Worksheet_Change so the cells will automatically change from the moment the spreadsheet connects to BetAngel and I have tried referencing example cell A1 both as 'A1' and as ' Worksheets("Sheet1").Range("A1").Value'

Here is the code I have trialed...

Code: Select all

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim StateCell As String, ActionCell As String, BAStateCell As String
Dim StateCellVal As String, ActionCellVal As String, BAStateCell As String
Dim CurrSheet As String

StateCell = Worksheets("Sheet1").Range("A1").Value
ActionCell = Worksheets("Sheet1").Range("A2").Value
BAStateCell = "Sheet1"

StateCellVal = Worksheets(CurrSheet).Range(StateCell).Value
ActionCellVal = Worksheets(CurrSheet).Range(ActionCell).Value
BAStateCellVal = Worksheets(CurrSheet).Range(BAStateCell).Value

Select Case StateCellVal

Case "A"

If ActionCellVal = "BACK" Then
    StateCellVal = "B"
Else
    StateCellVal = "A"
End If

End Select

End Sub
When I type the word "BACK" into the action cell (A2 in this case) nothing happens. I have tried typing "BACK" into all cells referenced in the code incase I had it typed incorectly, tried using formulas to make "BACK" appear based on calculations in another cell and still nothing?

Any ideas?

Cheers,
Callum :)
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Any cells that you need to reference on a sheet need to be dimmed as a range not as a string and then to be told what cell/cells it refers to

Code: Select all

Dim StateCell As Range
Set StateCell = Worksheets("Sheet1").Range("A1")
To get the value of whats in there:

Code: Select all

StateCellVal = StateCell.Value
Does away with:

Code: Select all

StateCellVal = Worksheets(CurrSheet).Range(StateCell).Value
The above was returning 2 errors, CurrSheet was equal to "" and Statecell was just whatever value was in the cell, so it was saying Sheet "", Range(2) , or if it was a word, Sheet "", Range(Back) etc,

If you want to CurrSheet to return the sheet name its just:

Code: Select all

CurrSheet = Me.Name
you've got

Code: Select all

BAStateCell As String
twice, should it be BAStateCellVal As String ?
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

I was trying to keep it relatively simple nigelk so avoiding getting into range variable types.

Calum the reason your code doesn't do anything is because you haven't told it to do anything. All you're doing is setting a variable. What happened the my example line of code that writes it back to the sheet? Put an F9 breakpoint in your code and step through with F8 when the breakpoint is reached to see what's being executed.
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

@ ShaunWhite
My bad pal, I’ve never used ‘select case’ before, I’m still a newbie when it comes to VBA so I just assumed the part after ‘select case’ was the instruction part of the code to move between each letter, not just having them defined as variables. I did have that line of code present but must have mis-pasted it in my last message. I’ll make sure it’s in the next one below for definite!

@nigelk
I have amended my code slightly with your advice to get the code I will paste to the bottom of this message but I get a few bugs. Firstly, to even have excel recognise the code I had to change ‘Private Sub Worksheet_Change(ByVal Target As Range)’ to simply ‘Sub StateMachine’. From there I get:

Compile Error: Invalid use of Me keyword

When I amend it to “Bet Angel” (I have changed the example document sheet name to “Bet Angel” prior before you ask, I get Run-time error ‘424’: Object required. Debugging show the line ‘Set StateCell = “Nothing”’ but I assume this is just because it is confused which of my … ahem one sheets … to look at?

Code: Select all

Option Explicit

Sub StateMachine()

Dim StateCell As Range, ActionCell As Range, BAStateCell As Range
Dim StateCellVal As Range, ActionCellVal As Range, BAStateCellVal As Range
Dim CurrSheet As String

CurrSheet = "Bet Angel"                                 'This is the problematic line
Set StateCell = Worksheets("Bet Angel").Range("A1").Value
Set ActionCell = Worksheets("Bet Angel").Range("A2").Value
Set BAStateCell = Worksheets("Bet Angel").Range("A3").Value

StateCellVal = StateCell.Value
ActionCellVal = ActionCell.Value
BAStateCellVal = BAStateCell.Value

Select Case StateCellVal

Case "A"

If ActionCellVal = "BACK" Then
    StateCellVal = "B"
Else
    StateCellVal = "A"
End If

End Select

'This is the line that I missed off previously but is it needed if I have StateCellVal = StateCell.Value above like nigelk pointed out?
Worksheets(CurrSheet).Range(StateCell).Value = StateCellVal
'If it is required, I will need two more lines like this for ActionCell and BAStateCell wont I?

End Sub
P.s remember this is a standard sub but ultimately i'm looking for it to work automatically, via worksheet_change I assume?

Any ideas gents? And other than that, does the rest of the code look correct for now? If so, how do I not only define the variables but go about making the letters change on the sheet as intended?

Much appreciated,
Callum :)
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

The Me.name will only work inside the sheet code itself, not a module. Try the below, remove the "msgbox" line when happy.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

Dim StateCell As Range, ActionCell As Range, BAStateCell As Range

Dim StateCellVal As String, ActionCellVal As String, BAStateCellVal As String

Dim CurrSheet As String

CurrSheet = Me.Name

'set the cells i want to look at

Set StateCell = Worksheets("Bet Angel").Range("A1")
Set ActionCell = Worksheets("Bet Angel").Range("A2")
Set BAStateCell = Worksheets("Bet Angel").Range("A3")

'get the value of what's in those cell

StateCellVal = StateCell.Value
ActionCellVal = ActionCell.Value
BAStateCellVal = BAStateCell.Value

Select Case StateCellVal

Case "A"

If ActionCellVal = "BACK" Then
    StateCellVal = "B"
Else
    StateCellVal = "A"
End If

End Select

MsgBox StateCellVal


End Sub
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

CallumPerry wrote:
Mon Apr 30, 2018 2:35 pm
how do I not only define the variables but go about making the letters change on the sheet as intended?
Just flip the statement,

instead of saying Something = Worksheets("Bet Angel").Range("A1")
use Worksheets("Bet Angel").Range("A1") = Something

Read it, check it, change it, write it (Wasn't that a Daft Punk tune?)
User avatar
jimibt
Posts: 3641
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

ShaunWhite wrote:
Mon Apr 30, 2018 4:45 pm
CallumPerry wrote:
Mon Apr 30, 2018 2:35 pm
how do I not only define the variables but go about making the letters change on the sheet as intended?
Just flip the statement,

instead of saying Something = Worksheets("Bet Angel").Range("A1")
use Worksheets("Bet Angel").Range("A1") = Something

Read it, check it, change it, write it (Wasn't that a Daft Punk tune?)
you mean this: https://www.youtube.com/watch?v=gAjR4_CbPpQ :)
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

Read it, check it, change it, write it (Wasn't that a Daft Punk tune?)
:lol: :lol: Sounds familiar!

Ok so first of all, I have copy and pasted the code from a module into the sheet code. Every time I select a new cell, a message box appears showing the contents of cell A1 so the ‘select case’ portion of the code is working absolutely fine. Now, when I substitute the msgbox line for:

Code: Select all

Worksheets(CurrSheet).Range(StateCell).Value = StateCellVal
So that the code writes itself back on to the sheet I get… Run-time error ‘1004’: Application-defined or object-defined error. ?? All I want is for it to write a single bloody letter in the cell lol!

I may be jumping ahead a bit too but my next query is, if each runner has its own cycle then would the I need to Dim, Set and Select StateCellOne, StateCellTwo, StateCellThree etc or is there a way it can be simplified to a single line of code? Bearing in mind that each runner will have its own unique cycle of commands as different runner will be backed and laid independently of one another based on specific criteria. Like I said though, may be jumping ahead of myself slightly here so that can wait until I know how to cycle through the letters at will to create the more advanced code I am trying to share.

Eagerly awaiting your next reply and madly appreciating your input thus far!
Callum :)
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Just to confirm where the code is going to end up?
from a module into the sheet code
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

The syntax of is all wrong as you'd already declared StateCell as a range, no need to wrap it into another range.

Code: Select all

Worksheets(CurrSheet).Range(StateCell).Value = StateCellVal
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

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
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

CallumPerry wrote:
Wed May 02, 2018 10:16 am
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
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

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

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! :D

Callum
spreadbetting
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
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
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”