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
State Machine Spreadsheet
-
- 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.
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
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
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
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
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.CallumPerry wrote: ↑Thu Apr 26, 2018 6:24 pmIf not for the whole code, I was hoping somebody could show me how to reference specific cells,
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
https://msdn.microsoft.com/en-us/vba/la ... -statement
Following on from Spreadbettings comment
but it's not a very common way to reference cells.
Maybe you seen something along the lines of:There are plenty of ways to reference cells within VBA
Code: Select all
If [J32] = "A" And [B33] = "BACK" Then [J32] = "B"
- 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
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
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
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.
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
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.
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
ShaunWhite wrote: ↑Fri Apr 27, 2018 11:49 amReally? 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
-
- 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...
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
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
Any ideas?
Cheers,
Callum
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
To get the value of whats in there:
Does away with:
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:
you've got
twice, should it be BAStateCellVal As String ?
Code: Select all
Dim StateCell As Range
Set StateCell = Worksheets("Sheet1").Range("A1")
Code: Select all
StateCellVal = StateCell.Value
Code: Select all
StateCellVal = Worksheets(CurrSheet).Range(StateCell).Value
If you want to CurrSheet to return the sheet name its just:
Code: Select all
CurrSheet = Me.Name
Code: Select all
BAStateCell As String
- 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.
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.
-
- 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?
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
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
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
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