Please help me with this VBA code

[danjuma]

I only just started using BA properly, especially for its superb integration with excel. I have taught myself a bit of excel, and with the little knowledge and help from this forum (and other excel forums), I have been writing a spreadsheet for a betting strategy.

I have now got to a stage where I need the spreadsheet to carry out a couple of functions which unfortunately can only be done using a VBA code. I am not versed in this (VBA), though it's something I intend to teach myself in the near future. So, for now, I require some assistance.

I have cheated and already recorded the two macros I want. The first macro I labelled Reset_Lay, and the second macro I labelled Back.

What I want to do is broken down in 2 steps:

1. When I open up my spreadsheet and log it to BA, or when the spreadsheet is already opened and logged to BA, but loads a new race (after finishing with the previous race), I want the Reset_Lay macro to run. So, basically for the macro to automatically run if it detects "Not In Play" in E2.

2. When a race then goes in play, and after about a few seconds (allowing for the market to settle down), I want the Back macro to run. So, basically for the macro to run automatically a few seconds (say 10secs) after it detects "In Play" in E2.

The raw code for the two macros (which was created by using the record a macro method in excel) are below. I just need the codes amended for the codes to run automatically when the relevant conditions above are met.

Many thanks for assistance in advance.

FIRST MACRO

Sub Reset_Lay()
'
' Reset_Lay Macro
'
' Keyboard Shortcut: Ctrl+r
'
Range("T5:T50").Select
Selection.ClearContents
Range("S5:S50").Select
Selection.ClearContents
Range("R5:R50").Select
Selection.ClearContents
Range("Q5:Q50").Select
Selection.ClearContents
Range("S5").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-13]<>"""",R6C30,"""")"
Range("S5").Select
Selection.AutoFill Destination:=Range("S5:S50"), Type:=xlFillDefault
Range("S5:S50").Select
ActiveWindow.SmallScroll Down:=-30
Range("R5").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]<>"""",RC[-12],"""")"
Range("R5").Select
Selection.AutoFill Destination:=Range("R5:R50"), Type:=xlFillDefault
Range("R5:R50").Select
ActiveWindow.SmallScroll Down:=-30
Range("Q5").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-11]<>"""",RC[-11]<=2,RC[11]=1,R20C30=3,R[-3]C[-12]<>""In Play"",NOT(ISNUMBER(MATCH(RC[-11],ExHorses!R[-3]C[-16]:R[95]C[-16],0)))),""BET"","""")"
Range("Q5").Select
Selection.AutoFill Destination:=Range("Q5:Q50"), Type:=xlFillDefault
Range("Q5:Q50").Select
Range("Q51").Select
ActiveWindow.SmallScroll Down:=-36
End Sub


SECOND MACRO


Sub Back()
'
' Back Macro
'
' Keyboard Shortcut: Ctrl+b
'
Range("T5:T50").Select
Selection.ClearContents
Range("S5:S50").Select
Selection.ClearContents
Range("R5:R50").Select
Selection.ClearContents
Range("Q5:Q50").Select
Selection.ClearContents
Range("S5").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-13]<>"""",(RC[3]/R8C30)*RC[4],"""")"
Range("S5").Select
Selection.AutoFill Destination:=Range("S5:S50"), Type:=xlFillDefault
Range("S5:S50").Select
ActiveWindow.SmallScroll Down:=-21
Range("R5").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]<>"""",R8C30,"""")"
Range("R5").Select
Selection.AutoFill Destination:=Range("R5:R50"), Type:=xlFillDefault
Range("R5:R50").Select
ActiveWindow.SmallScroll Down:=-27
Range("Q5").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-11]<>"""",R12C30=1,RC[-11]<2,RC[9]=1),""BACK"","""")"
Range("Q5").Select
Selection.AutoFill Destination:=Range("Q5:Q51"), Type:=xlFillDefault
Range("Q5:Q51").Select
ActiveWindow.SmallScroll Down:=6
Range("Q52").Select
ActiveWindow.SmallScroll Down:=-42
End Sub

[danjuma]

Ok, no takers. Back to the drawing board then.

I think I have figured out how to do it with just excel.

[jokerjoe]

Ok, first up I've tidied your macros.

Code: Select all

Sub Reset_Lay()
'
' Reset_Lay Macro
'
' Keyboard Shortcut: Ctrl+r
'
Range("Q5:T50").ClearContents

Range("S5").Formula = "=IF(F5<>"""",$AD$6,"""")"
Range("S5").AutoFill Destination:=Range("S5:S50"), Type:=xlFillDefault

Range("R5").Formula = "=IF(F5<>"""",F5,"""")"
Range("R5").AutoFill Destination:=Range("R5:R50"), Type:=xlFillDefault

Range("Q5").Formula = "=IF(AND(F5<>"""",F5<=2,AB5=1,$AD$20=3,E2<>""In Play"",NOT(ISNUMBER(MATCH(F5,ExHorses!A2:A100,0)))),""BET"","""")"
Range("Q5").AutoFill Destination:=Range("Q5:Q50"), Type:=xlFillDefault

End Sub


Sub Back()
'
' Back Macro
'
' Keyboard Shortcut: Ctrl+b
'
Range("Q5:T50").ClearContents

Range("S5").Formula = "=IF(F5<>"""",(V5/$AD$8)*W5,"""")"
Range("S5").AutoFill Destination:=Range("S5:S50"), Type:=xlFillDefault

Range("R5").Formula = "=IF(F5<>"""",$AD$8,"""")"
Range("R5").AutoFill Destination:=Range("R5:R50"), Type:=xlFillDefault

Range("Q5").Formula = "=IF(AND(F5<>"""",$AD$12=1,F5<2,Z5=1),""BACK"","""")"
Range("Q5").AutoFill Destination:=Range("Q5:Q51"), Type:=xlFillDefault

End Sub

Second, this is based on my code for triggering market update events, this goes in your worksheet space, not a module.

Code: Select all

Private MyMarket As String
Private Layed As Boolean
Private InPlay As Boolean
Private InPlayTimer As Single
Private RefreshCount As Long

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
   
    Select Case Target.Columns.Count
        Case 16 'odds and market info update
           
            'check to see if market changed
            If wsBA.Range("A1").Value = MyMarket Then
                RefreshCount = RefreshCount + 1
            Else
                MyMarket = wsBA.Range("A1").Value
                RefreshCount = 1
                Layed = False
                InPlay = False
            End If
                       
            'waits 2 refreshes, lays if not in play or already layed
            If RefreshCount > 3 And Range("E2") = "Not In Play" And Range("F2") <> "Suspended" _
            And Not (Layed) Then
                Call Reset_Lay
                Layed = True
            End If
           
            'checks if in play
            If Not (InPlay) Then
                If Range("E2") = "In Play" Then
                    InPlay = True
                    InPlayTimer = Timer
                End If
            End If
           
            'checks if market's been in play for more than 10 seconds, and a lay has been triggered
            If InPlay And Timer - InPlayTimer > 10 And Layed Then
                Call Back
            End If

                       
        Case 5 'bets update
        Case Else
    End Select

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

End Sub


This code hasn't been tested.

[danjuma]

Thanks a lot JJ. I shall give it a try next few days and see how it goes. Very much appreciated.

[danjuma]

Hi jokerjoe,

I tried the code you provided, but got a Complie Error with the message: 'Ambiguous name detected:Worksheet_Change.

I have a feeling this is probably to do with another code (which I am using for for my count up timer) in the same work sheet. Below is the code for the count up timer:

Private Sub Worksheet_Change(ByVal Target As Range)
If updating Then Exit Sub
updating = True
If Cells(2, 5) = "In Play" Then
If Cells(1, 27) = "" Then Cells(1, 27) = Cells(2, 3)
If Cells(1, 27) <> "" Then Cells(1, 28) = DateDiff("s", Cells(1, 27), Cells(2, 3))
End If
If Cells(2, 5) <> "In Play" And Cells(2, 6) <> "Suspended" Then
Cells(1, 27) = ""
Cells(1, 28) = ""
End If
updating = False
End Sub


Thanks

[jokerjoe]

You need to combine the two, just stick that bit of code without the first and last lines (private sub.. & end sub) at the end of mine, after "end select".

Btw I noticed something, you should remove the wsBA. references from my code, that's from how I name my sheets.

[danjuma]

Hi jokerjoe.

The code is now working fine (with one worksheet). Many thanks. However, there is one issue.

In other to avoid races clashing in terms of start time, I tend to open different venues in different tabs, and then log each venue to seperate duplicated sheets in the one workbook.
So what I have done is copy the code into each worksheet, but this has caused strange things to happen. For instance, when a race in one worksheet is not in play yet, instead of the Reset_Lay macro being called, the Back macro for in play gets called, probably because another race in another worksheet is in play?

Below is a copy of the code in the first worksheet and subsequent worksheets. What's the problem please? Thanks.


Private MyMarket As String
Private Layed As Boolean
Private InPlay As Boolean
Private InPlayTimer As Single
Private RefreshCount As Long

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Select Case Target.Columns.Count
Case 16 'odds and market info update

'check to see if market changed
If Range("A1").Value = MyMarket Then
RefreshCount = RefreshCount + 1
Else
MyMarket = Range("A1").Value
RefreshCount = 1
Layed = False
InPlay = False
End If

'waits 2 refreshes, lays if not in play or already layed
If RefreshCount > 3 And Range("E2") = "Not In Play" And Range("F2") <> "Suspended" _
And Not (Layed) Then
Call Reset_Lay
Layed = True
End If

'checks if in play
If Not (InPlay) Then
If Range("E2") = "In Play" Then
InPlay = True
InPlayTimer = Timer
End If
End If

'checks if market's been in play for more than 10 seconds, and a lay has been triggered
If InPlay And Timer - InPlayTimer > 10 And Layed Then
Call Back
End If


Case 5 'bets update
Case Else
End Select

If updating Then Exit Sub
updating = True
If Cells(2, 5) = "In Play" Then
If Cells(1, 27) = "" Then Cells(1, 27) = Cells(2, 3)
If Cells(1, 27) <> "" Then Cells(1, 28) = DateDiff("s", Cells(1, 27), Cells(2, 3))
End If
If Cells(2, 5) <> "In Play" And Cells(2, 6) <> "Suspended" Then
Cells(1, 27) = ""
Cells(1, 28) = ""
End If
updating = False

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

[jokerjoe]

Try this, the backs and lays are now worksheet specific. This also means you won't ba able to manually call them, perhaps rename them if you want to be able to.

Code: Select all

Private MyMarket As String
Private Layed As Boolean
Private InPlay As Boolean
Private InPlayTimer As Single
Private RefreshCount As Long

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Select Case Target.Columns.Count
    Case 16 'odds and market info update
   
    'check to see if market changed
    If Range("A1").Value = MyMarket Then
        RefreshCount = RefreshCount + 1
    Else
        MyMarket = Range("A1").Value
        RefreshCount = 1
        Layed = False
        InPlay = False
    End If
   
    'waits 2 refreshes, lays if not in play or already layed
    If RefreshCount > 3 And Range("E2") = "Not In Play" And Range("F2") <> "Suspended" _
    And Not (Layed) Then
        Call Reset_Lay(Target)
        Layed = True
    End If
   
    'checks if in play
    If Not (InPlay) Then
        If Range("E2") = "In Play" Then
            InPlay = True
            InPlayTimer = Timer
        End If
    End If
   
    'checks if market's been in play for more than 10 seconds, and a lay has been triggered
    If InPlay And Timer - InPlayTimer > 10 And Layed Then
        Call Back(Target)
    End If
   
   
    Case 5 'bets update
    Case Else
End Select

If updating Then Exit Sub
    updating = True
    If Cells(2, 5) = "In Play" Then
        If Cells(1, 27) = "" Then Cells(1, 27) = Cells(2, 3)
        If Cells(1, 27) <> "" Then Cells(1, 28) = DateDiff("s", Cells(1, 27), Cells(2, 3))
    End If
    If Cells(2, 5) <> "In Play" And Cells(2, 6) <> "Suspended" Then
        Cells(1, 27) = ""
        Cells(1, 28) = ""
    End If
updating = False

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic


Code: Select all

Sub Reset_Lay(ByRef Target As Range)
'
' Reset_Lay Macro
'
' Keyboard Shortcut: Ctrl+r
'
With Target.Worksheet
    .Range("Q5:T50").ClearContents
   
    .Range("S5").Formula = "=IF(F5<>"""",$AD$6,"""")"
    .Range("S5").AutoFill Destination:=.Range("S5:S50"), Type:=xlFillDefault
   
    .Range("R5").Formula = "=IF(F5<>"""",F5,"""")"
    .Range("R5").AutoFill Destination:=.Range("R5:R50"), Type:=xlFillDefault
   
    .Range("Q5").Formula = "=IF(AND(F5<>"""",F5<=2,AB5=1,$AD$20=3,E2<>""In Play"",NOT(ISNUMBER(MATCH(F5,ExHorses!A2:A100,0)))),""BET"","""")"
    .Range("Q5").AutoFill Destination:=.Range("Q5:Q50"), Type:=xlFillDefault
End With

End Sub


Sub Back(ByRef Target As Range)
'
' Back Macro
'
' Keyboard Shortcut: Ctrl+b
'
With Target.Worksheet
    .Range("Q5:T50").ClearContents
   
    .Range("S5").Formula = "=IF(F5<>"""",(V5/$AD$8)*W5,"""")"
    .Range("S5").AutoFill Destination:=.Range("S5:S50"), Type:=xlFillDefault
   
    .Range("R5").Formula = "=IF(F5<>"""",$AD$8,"""")"
    .Range("R5").AutoFill Destination:=.Range("R5:R50"), Type:=xlFillDefault
   
    .Range("Q5").Formula = "=IF(AND(F5<>"""",$AD$12=1,F5<2,Z5=1),""BACK"","""")"
    .Range("Q5").AutoFill Destination:=.Range("Q5:Q51"), Type:=xlFillDefault
End With

End Sub

[danjuma]

Jokerjoe, many thanks for your time and assistance. it's very much appreciated.

Do the two seperate codes go into the worksheet, or does the second code go into a module?

Also, my code that I use for the countup timer which is imbedded towards the end of your first code interacts with a module - Public updating As Boolean.
Do I need to do anything about this or just leave it as it is?

Many thanks.

PS. The countup timer code is something I copied from the forum and pasted into my worksheet. I don't know much about VBA, so don't really understand how codes are written or work.

[jokerjoe]

No worries, I enjoy a little challenge!

The second part goes in a module. Only event-driven subs go into worksheets, in this case the worksheet change event.

I actually think the updating part is redundant as we turn off further events at the start of the code so nothing else should run, you can safely delete the first two and last lines of that section. If you did want to keep it though add "Private updating As Boolean" at the top of the worksheet code to keep it worksheet specific and delete the other reference you mentioned.

If you wanted to learn VBA I recommend Excel 2003 Power Programming with VBA by John Walkenbach. I personally found it very helpful in pulling the strands I knew together and teaching me new tricks.

[danjuma]

Hi jokerjoe,

Just to let you know the code now works fine - independently of each worksheet. Many thanks for your assistance. I did try your suggestion below,

I actually think the updating part is redundant as we turn off further events at the start of the code so nothing else should run, you can safely delete the first two and last lines of that section. If you did want to keep it though add "Private updating As Boolean" at the top of the worksheet code to keep it worksheet specific and delete the other reference you mentioned.

but the code started behaving funny, so I left the suggestion out.

Many thanks.
Dan