Multiple tabs, double bets error

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
rain91
Posts: 112
Joined: Tue Feb 07, 2017 7:11 pm

Hi,
i have use an excel for trading, and i have a problem. I use this VBA for clear status cells.

Code: Select all

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim i As Integer
For i=9 to 69 step 2
If Range("O" & i) = "PLACED" Then
Range("O" & i).ClearContents
End If
Next
Application.EnableEvents = True
End Sub
The commands (BACK, LAY, and cancel bets for both) are in "L" cells as function.
Many times i get multiple bet from lay or from back or lay bets, and i dont know, why.

The function in cells "L" are very complicated, but the logic:

BACK = O not "Placing (AND) no unmatched BACK bets (AND) matched bets not hedged, or no BACK bets
LAY = O not "Placing" (AND) no unmatched LAY bets (AND) matched bets not hedged (i have a limit value), so i have BACK bet without LAY bet.

If i use only one Excel tab, it works fine, any time!
But if i make 5 excel tabs, i get many times double BACK bets, in same time, for expamle at odds 9.6 at 20:45:15, see my attached screenshot.
If the bet placed, in this case the bets hedged or many stake unmatched, in these cases i dont have BACK or LAY command in "L"), so can be no more bets..
You do not have the required permissions to view the files attached to this post.
User avatar
ODPaul82
Posts: 683
Joined: Sun May 08, 2011 6:32 am
Location: Digswell Herts

Quite possibly because you're not being explicit with your range object about which worksheet it is referring to. The Range object if you don't specifiy the worksheet for it to operate on will operate on the currently active worksheet.

This is the code I've used for years with no issues. It checks a named range on the worksheet to see whether the cells need cleaning or not and then iterates through each of them checking if they need to be cleared.

' This goes in its own code module

Code: Select all

Option Explicit


Private Const EMPTY_STRING As String = ""
Private Const COL_REPORT As Long = 12
Private Const COL_STATUS As Long = 15


Public Sub gTestForWipe(ByRef pobjWs As Excel.Worksheet)

    Dim lngRow As Long

    If pobjWs.Range("fldNeedClean") Then
    
        Application.EnableEvents = False
        
        For lngRow = 9 To 69 Step 2
        
            If pobjWs.Cells(lngRow + 1, COL_REPORT) <> EMPTY_STRING Then pobjWs.Cells(lngRow + 1, COL_REPORT) = EMPTY_STRING
            If pobjWs.Cells(lngRow, COL_STATUS) <> EMPTY_STRING Then pobjWs.Cells(lngRow, COL_STATUS) = EMPTY_STRING
        
        Next lngRow
        Application.EnableEvents = True
    
    End If


End Sub
Then each BA worksheet has the following in it

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    gTestForWipe Me
End Sub
rain91
Posts: 112
Joined: Tue Feb 07, 2017 7:11 pm

Its a bit hard to me (i only know the basics of VBA).
My Sub runs every sheets (i copied the Subs with Ctrl+C / Ctrl+V), but in this case it is not good? (see attached picture)
I have more codes in this Sub, that works fine on every active / non active sheets.
Thanks for the help / reply!
sheets.PNG
You do not have the required permissions to view the files attached to this post.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”