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

Hi guys,

I've half created a bit of code for a 'state machine' whereby quick flashing orders wont disappear before they get submitted through the instruction field but i'm having a little difficulty with it. If anybody could PM me directly i'd like to get this completed soon and then I will share the spreadsheet on this forum.

Basically if you have "BACK" placed in cell L9 but it disappears too quickly (due to the nature of your formulas) this spreadsheet will keep it there until it reached BF and is confirmed, the "PLACED" cell will then disappear and you can go again. No multiple firing bets, only "BACK"/"LAY" followed by "CLOSE_TRADE" and vice versa. Should speed up a lot of your strategies so yeah, if there is anybody who thinks they could help me out for the greater good of the commiunity hit me up :)

Alternatively, if there is something like this alreday out there could one of you point me towards it please, i've had a scan and not noticed anything to be fair.

Cheers,

Callum :)
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

The following code is for the first selection of a market only at the moment. The idea is that the letters follow the order like in the screenshot attached so no 'repeat' orders can accidentally enter the market. The problem i'm having is referring to specific cells i.e. J33. B33 is the trigger that will either show "BACK" or "LAY" based on specific formulas and the J32 will show the letters corresponding to the diagram in the attached document.

Code: Select all

'***State Machine
'Brand One

'If J32="A" And B33="BACK", Then J32="B"
'If J32="B" Then Call BACKONE() 'Back Triggered
'If O9="PLACED" and L9="BACK", Then J32="C"
'If J32="C" Then Call ClearBrandONE 'Back Placed
'If J32="C" And B33="LAY" Then J32="D" 'Back Close Triggered
'If J32="D" And O9="PLACED" Then J32="A" 'Reload

'If J32="A" And B33="LAY" Then J32="E"
'If J32="E" Then Call LAYONE() 'Lay Triggered
'If J32="E" And O9="PLACED" And L9 = "BACK" Then J32="F"
'If J32="E" Then Call ClearBrandONE 'Lay Placed
'If J32="F" And B33="BACK" Then J32="G" 'Lay Close Triggered
'If J32="G" And O9="PLACED" Then J32="A" 'Reload

'If J32="A" And O9="ERROR" Then J32="H" 'ERROR
'If J32="H" Then G6="ERROR" 'Error handling
If not for the whole code, I was hoping somebody could show me how to reference specific cells, I think what i'm trying to do is self-explanatory in the code (at least to people more familiar with VBA than me). If anybody could help tidy this up or point me in the right direction, I will make the finished spreadsheet available for everyone to use :)

Thanks in advance,
Callum :)


Edit: Attachment is not accepted so order of operations =
A to B to C to D, back to A
A to E to F to G, back to A
H is error
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

CallumPerry wrote:
Thu Apr 26, 2018 6:24 pm
If not for the whole code, I was hoping somebody could show me how to reference specific cells,
There are plenty of ways to reference cells within VBA but it's best to fully reference the address and worksheet of the cell otherwise VBA just treats the cell address as the active worksheet you have open. Might seem overkill for now but once you start adding or amending code it's always best to have it easily readable. There are ways to simplify writing the address but I don't want to complicate things for you if you're new to VBA.

If we just want to reference cell A1 in a worksheet Sheet1 we'd access the value of the cell using the following, this would find the value of the cell even if it contains a formula.

Code: Select all

Worksheets("Sheet1").Range("A1").Value
Might be worth you looking at "Select Case" for moving around your code rather than lots of IF statements

https://msdn.microsoft.com/en-us/vba/la ... -statement
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Following on from Spreadbettings comment
There are plenty of ways to reference cells within VBA
Maybe you seen something along the lines of:

Code: Select all

If [J32] = "A" And [B33] = "BACK" Then [J32] = "B"
but it's not a very common way to reference cells.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

The OPs code (and nigel's) won't work. :?:

They're both just looking at variables called J32 & B33 etc not cell values.

Stick to what sb says and you won't go far wrong.

I started to rewrite it for you but your logic soon stopped making much sense, you know what it's supposed to do so I'm sure you'll fill in the blanks

Code: Select all


'Declare your stored values (aka variables) to make spotting typos easier because vb changes the case if it knows them

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

'Define your refs in one place so changes are easy
StateCell = "J32"
ActionCell = "B33"
BAStateCell = "09"
CurrSheet = "Bet Angel"

'Get status
StateCellVal = Worksheets(CurrSheet).Range(StateCell).Value
ActionCellVal = WorkSheets(CurrSheet).Range(ActionCell).Value
BAStateCellVal = WorkSheets(CurrSheet).Range(BAStateCell).Value

'Change status
Select Case StateCellVal

    Case "A"    
    
        If ActionCellVal = "BACK" Then 
        	StateCellVal = "B"
        Else
        	StateCellVal = "E"
        end if

    Case "B"  
      	If ....... Then
      		StateCellVal = "C"
      	end if
    Case "C" 
      	If ....... Then
      		StateCellVal = "D"
      	end if
	
    Case "D"
        If ....... Then
      		StateCellVal = "A"
      	end if

    Case "E"
        If ....... Then
      		StateCellVal = "F"
      	end if

    Case "F"
        If ....... Then
      		StateCellVal = "G"
      	end if

    Case "G"
        If ....... Then
      		StateCellVal = "A"
      	end if

    Case "H" ' Error
	If MsgBox("Error has occured, do you want to continue",vbYesNo) = vbYes Then
    	    'Reset status
            StateCellVal = "A"
         Else
            Stop
        End If

    Case Else   
    	'Unexpected status so reset & continue
        StateCellVal = "A"
    
End Select

'Write status back to the sheet !!!
Worksheets(CurrSheet).Range(StateCell).Value = StateCellVal
User avatar
Niko
Posts: 282
Joined: Thu Dec 01, 2016 9:36 am

wish i had listened at school :oops:
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Not so.
They're both just looking at variables
J32 is a variable.

[J32] is a perfectly valid way of referencing a cell, it's just not a very common way of doing it, and judging from Callums code, I thought he had seen something like that.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

nigelk wrote:
Fri Apr 27, 2018 8:35 am
Not so.
They're both just looking at variables
J32 is a variable.

[J32] is a perfectly valid way of referencing a cell, it's just not a very common way of doing it, and judging from Callums code, I thought he had seen something like that.
Really? I tried it because I'd never seen it before and when I watched the statement it was always empty. If it works for you then fair enough.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Niko wrote:
Fri Apr 27, 2018 7:48 am
wish i had listened at school :oops:
It wasn't school, it was the zx81 manual. And with there being nothing to do where I grew up, the newsagents was the nearest thing I had to a library, Practical Electronics, Electronics and Music Maker, Custom Car Magazine etc lasted longer than the Beano because it took me 10 reads before I understood any of it. :geek:
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

ShaunWhite wrote:
Fri Apr 27, 2018 11:49 am
nigelk wrote:
Fri Apr 27, 2018 8:35 am
Not so.
They're both just looking at variables
J32 is a variable.

[J32] is a perfectly valid way of referencing a cell, it's just not a very common way of doing it, and judging from Callums code, I thought he had seen something like that.
Really? I tried it because I'd never seen it before and when I watched the statement it was always empty. If it works for you then fair enough.

I've used [J32] and [J32].Value when I first started learning VBA, nice and quick to enter references.

I only switched to using Ranges later on as they made more sense to me when using arrays etc as I could easily define a range to set and access it from anywhere within the VBA. The thing I found with VBA is you soon start to see the possibilities available to you and tweak your code before you know it you haven't got a clue what's going on if you haven't referenced everything cleary fro the start. I even comment my code these days :o
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
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”