Bet Angel - Spreadsheet / Excel chat : State Machine Spreadsheet

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

Mon Jan 01, 2018 4:44 pm

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 :)

User avatar
CallumPerry
Posts: 168
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

Thu Apr 26, 2018 6:24 pm

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

Thu Apr 26, 2018 7:43 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: 458
Joined: Wed Apr 15, 2009 11:00 pm

Thu Apr 26, 2018 10:24 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: 3567
Joined: Sat Sep 03, 2016 3:42 am

Fri Apr 27, 2018 1:49 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

Niko
Posts: 282
Joined: Thu Dec 01, 2016 9:36 am

Fri Apr 27, 2018 7:48 am

wish i had listened at school :oops:

nigelk
Posts: 458
Joined: Wed Apr 15, 2009 11:00 pm

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.

User avatar
ShaunWhite
Posts: 3567
Joined: Sat Sep 03, 2016 3:42 am

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.

User avatar
ShaunWhite
Posts: 3567
Joined: Sat Sep 03, 2016 3:42 am

Fri Apr 27, 2018 12:02 pm

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

Fri Apr 27, 2018 4:48 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

Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”

  • Information
  • Who is online

    Users browsing this forum: No registered users and 2 guests