Spread sheet awfully slow - help please?

Please post any questions regarding the program here.

Moderator: 2020vision

Post a reply

Spread sheet awfully slow - help please?

Postby danjuma » Fri May 20, 2011 9:44 am

Hi,

I have a spreadsheet with some VBA code and cell formatting. The sheet, whether connected to BA or not is really slow. For example, when I enter new data in a cell, it takes a few seconds to update, can actually see the hour glass spinning (even when not logged to BA). The worksheet works fine (does what I want it to do), but just very slow. Below, I have copied the codes, which I have coded in my primitive way :) May be some VBA expert on here can have a look for me please and suggest a more efficient way? Many thanks

I have got two sheets on the worksheet named 'BA' and 'Correct Score'.

The 'BA' sheet is where BA is logged to, and contains no macros or cell formatting of any sort. The 'Correct Score sheet' has the following codes


Code 1: What this does, is that when I right click on any cell in the range A4:A20, a "BACK"or "LAY" is entered in the corresponding cell in column Q in the 'BA' sheet, waits for a second or two, then enter "CLEAR" in the cell.


Code: Select all
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Application.Intersect(Target, Range("A4")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q5").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q5").Value = "CLEAR"
ElseIf Not Application.Intersect(Target, Range("A5")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q6").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q6").Value = "CLEAR"
ElseIf Not Application.Intersect(Target, Range("A6")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q7").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q7").Value = "CLEAR"
ElseIf Not Application.Intersect(Target, Range("A7")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q8").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q8").Value = "CLEAR"
ElseIf Not Application.Intersect(Target, Range("A8")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q9").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q9").Value = "CLEAR"
ElseIf Not Application.Intersect(Target, Range("A9")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q10").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q10").Value = "CLEAR"
ElseIf Not Application.Intersect(Target, Range("A10")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q11").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q11").Value = "CLEAR"
ElseIf Not Application.Intersect(Target, Range("A11")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q12").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q12").Value = "CLEAR"
ElseIf Not Application.Intersect(Target, Range("A12")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q13").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q13").Value = "CLEAR"
ElseIf Not Application.Intersect(Target, Range("A13")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q14").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q14").Value = "CLEAR"
ElseIf Not Application.Intersect(Target, Range("A14")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q15").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q15").Value = "CLEAR"
ElseIf Not Application.Intersect(Target, Range("A15")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q16").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q16").Value = "CLEAR"
ElseIf Not Application.Intersect(Target, Range("A16")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q17").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q17").Value = "CLEAR"
ElseIf Not Application.Intersect(Target, Range("A17")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q18").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q18").Value = "CLEAR"
ElseIf Not Application.Intersect(Target, Range("A18")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q19").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q19").Value = "CLEAR"
ElseIf Not Application.Intersect(Target, Range("A19")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q20").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q20").Value = "CLEAR"
ElseIf Not Application.Intersect(Target, Range("A20")) Is Nothing Then
Cancel = True
ThisWorkbook.Sheets("BA").Range("Q21").Value = Range("V1").Value
Wait
ThisWorkbook.Sheets("BA").Range("Q21").Value = "CLEAR"
End If
End Sub



Code 2: This for spin buttons to increase or decrease the values in cells D4:D20, and K4:K20

Code: Select all
Private Sub SpinButton1_SpinDown()
Range("D4").Value = Range("D4").Value - Range("D1").Value
End Sub

Private Sub SpinButton1_SpinUp()
Range("D4").Value = Range("D4").Value + Range("D1").Value
End Sub

Private Sub SpinButton2_SpinDown()
Range("D5").Value = Range("D5").Value - Range("D1").Value
End Sub

Private Sub SpinButton2_SpinUp()
Range("D5").Value = Range("D5").Value + Range("D1").Value
End Sub


Private Sub SpinButton3_SpinDown()
Range("D6").Value = Range("D6").Value - Range("D1").Value
End Sub

Private Sub SpinButton3_SpinUp()
Range("D6").Value = Range("D6").Value + Range("D1").Value
End Sub

Private Sub SpinButton4_SpinDown()
Range("D7").Value = Range("D7").Value - Range("D1").Value
End Sub

Private Sub SpinButton4_SpinUp()
Range("D7").Value = Range("D7").Value + Range("D1").Value
End Sub


Private Sub SpinButton5_SpinDown()
Range("D8").Value = Range("D8").Value - Range("D1").Value
End Sub

Private Sub SpinButton5_SpinUp()
Range("D8").Value = Range("D8").Value + Range("D1").Value
End Sub


Private Sub SpinButton6_SpinDown()
Range("D9").Value = Range("D9").Value - Range("D1").Value
End Sub

Private Sub SpinButton6_SpinUp()
Range("D9").Value = Range("D9").Value + Range("D1").Value
End Sub

Private Sub SpinButton7_SpinDown()
Range("D10").Value = Range("D10").Value - Range("D1").Value
End Sub

Private Sub SpinButton7_SpinUp()
Range("D10").Value = Range("D10").Value + Range("D1").Value
End Sub

Private Sub SpinButton8_SpinDown()
Range("D11").Value = Range("D11").Value - Range("D1").Value
End Sub

Private Sub SpinButton8_SpinUp()
Range("D11").Value = Range("D11").Value + Range("D1").Value
End Sub

Private Sub SpinButton9_SpinDown()
Range("D12").Value = Range("D12").Value - Range("D1").Value
End Sub

Private Sub SpinButton9_SpinUp()
Range("D12").Value = Range("D12").Value + Range("D1").Value
End Sub

Private Sub SpinButton10_SpinDown()
Range("D13").Value = Range("D13").Value - Range("D1").Value
End Sub

Private Sub SpinButton10_SpinUp()
Range("D13").Value = Range("D13").Value + Range("D1").Value
End Sub


Private Sub SpinButton11_SpinDown()
Range("D14").Value = Range("D14").Value - Range("D1").Value
End Sub

Private Sub SpinButton11_SpinUp()
Range("D14").Value = Range("D14").Value + Range("D1").Value
End Sub


Private Sub SpinButton12_SpinDown()
Range("D15").Value = Range("D15").Value - Range("D1").Value
End Sub

Private Sub SpinButton12_SpinUp()
Range("D15").Value = Range("D15").Value + Range("D1").Value
End Sub

Private Sub SpinButton13_SpinDown()
Range("D16").Value = Range("D16").Value - Range("D1").Value
End Sub

Private Sub SpinButton13_SpinUp()
Range("D16").Value = Range("D16").Value + Range("D1").Value
End Sub


Private Sub SpinButton14_SpinDown()
Range("D17").Value = Range("D17").Value - Range("D1").Value
End Sub

Private Sub SpinButton14_SpinUp()
Range("D17").Value = Range("D17").Value + Range("D1").Value
End Sub


Private Sub SpinButton15_SpinDown()
Range("D18").Value = Range("D18").Value - Range("D1").Value
End Sub

Private Sub SpinButton15_SpinUp()
Range("D18").Value = Range("D18").Value + Range("D1").Value
End Sub


Private Sub SpinButton16_SpinDown()
Range("D19").Value = Range("D19").Value - Range("D1").Value
End Sub

Private Sub SpinButton16_SpinUp()
Range("D19").Value = Range("D19").Value + Range("D1").Value
End Sub

Private Sub SpinButton17_SpinDown()
Range("D20").Value = Range("D20").Value - Range("D1").Value
End Sub

Private Sub SpinButton17_SpinUp()
Range("D20").Value = Range("D20").Value + Range("D1").Value
End Sub


Private Sub SpinButton18_SpinDown()
Range("K4").Value = Range("K4").Value - Range("D1").Value
End Sub

Private Sub SpinButton18_SpinUp()
Range("K4").Value = Range("K4").Value + Range("D1").Value
End Sub

Private Sub SpinButton19_SpinDown()
Range("K5").Value = Range("K5").Value - Range("D1").Value
End Sub

Private Sub SpinButton19_SpinUp()
Range("K5").Value = Range("K5").Value + Range("D1").Value
End Sub


Private Sub SpinButton20_SpinDown()
Range("K6").Value = Range("K6").Value - Range("D1").Value
End Sub

Private Sub SpinButton20_SpinUp()
Range("K6").Value = Range("K6").Value + Range("D1").Value
End Sub

Private Sub SpinButton21_SpinDown()
Range("K7").Value = Range("K7").Value - Range("D1").Value
End Sub

Private Sub SpinButton21_SpinUp()
Range("K7").Value = Range("K7").Value + Range("D1").Value
End Sub


Private Sub SpinButton22_SpinDown()
Range("K8").Value = Range("K8").Value - Range("D1").Value
End Sub

Private Sub SpinButton22_SpinUp()
Range("K8").Value = Range("K8").Value + Range("D1").Value
End Sub


Private Sub SpinButton23_SpinDown()
Range("K9").Value = Range("K9").Value - Range("D1").Value
End Sub

Private Sub SpinButton23_SpinUp()
Range("K9").Value = Range("K9").Value + Range("D1").Value
End Sub

Private Sub SpinButton24_SpinDown()
Range("K10").Value = Range("K10").Value - Range("D1").Value
End Sub

Private Sub SpinButton24_SpinUp()
Range("K10").Value = Range("K10").Value + Range("D1").Value
End Sub

Private Sub SpinButton25_SpinDown()
Range("K11").Value = Range("K11").Value - Range("D1").Value
End Sub

Private Sub SpinButton25_SpinUp()
Range("K11").Value = Range("K11").Value + Range("D1").Value
End Sub

Private Sub SpinButton26_SpinDown()
Range("K12").Value = Range("K12").Value - Range("D1").Value
End Sub

Private Sub SpinButton26_SpinUp()
Range("K12").Value = Range("K12").Value + Range("D1").Value
End Sub

Private Sub SpinButton27_SpinDown()
Range("K13").Value = Range("K13").Value - Range("D1").Value
End Sub

Private Sub SpinButton27_SpinUp()
Range("K13").Value = Range("K13").Value + Range("D1").Value
End Sub


Private Sub SpinButton28_SpinDown()
Range("K14").Value = Range("K14").Value - Range("D1").Value
End Sub

Private Sub SpinButton28_SpinUp()
Range("K14").Value = Range("K14").Value + Range("D1").Value
End Sub


Private Sub SpinButton29_SpinDown()
Range("K15").Value = Range("K15").Value - Range("D1").Value
End Sub

Private Sub SpinButton29_SpinUp()
Range("K15").Value = Range("K15").Value + Range("D1").Value
End Sub

Private Sub SpinButton30_SpinDown()
Range("K16").Value = Range("K16").Value - Range("D1").Value
End Sub

Private Sub SpinButton30_SpinUp()
Range("K16").Value = Range("K16").Value + Range("D1").Value
End Sub


Private Sub SpinButton31_SpinDown()
Range("K17").Value = Range("K17").Value - Range("D1").Value
End Sub

Private Sub SpinButton31_SpinUp()
Range("K17").Value = Range("K17").Value + Range("D1").Value
End Sub


Private Sub SpinButton32_SpinDown()
Range("K18").Value = Range("K18").Value - Range("D1").Value
End Sub

Private Sub SpinButton32_SpinUp()
Range("K18").Value = Range("K18").Value + Range("D1").Value
End Sub


Private Sub SpinButton33_SpinDown()
Range("K19").Value = Range("K19").Value - Range("D1").Value
End Sub

Private Sub SpinButton33_SpinUp()
Range("K19").Value = Range("K19").Value + Range("D1").Value
End Sub

Private Sub SpinButton34_SpinDown()
Range("K20").Value = Range("K20").Value - Range("D1").Value
End Sub

Private Sub SpinButton34_SpinUp()
Range("K20").Value = Range("K20").Value + Range("D1").Value
End Sub



Code 3: These are in a module.


Code: Select all
Sub Button104_Click()
Range("D4:D20").Value = 0
Range("G4:G20").Select
Selection.ClearContents
Range("K4:K20").Value = 0
Range("M4:M20").Select
Selection.ClearContents
Range("I1").Select
End Sub

Sub Wait()
    waitTime = 1
    Start = Timer
    While Timer < Start + waitTime
        DoEvents
    Wend
End Sub


Function getPrevOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1.01 To 2
oddsInc = 0.01
Case 2.02 To 3
oddsInc = 0.02
Case 3.05 To 4
oddsInc = 0.05
Case 4.1 To 6
oddsInc = 0.1
Case 6.2 To 10
oddsInc = 0.2
Case 10.5 To 20
oddsInc = 0.5
Case 21 To 30
oddsInc = 1
Case 32 To 50
oddsInc = 2
Case 55 To 100
oddsInc = 5
Case 110 To 1000
oddsInc = 10
End Select
If Math.Round(odds - oddsInc, 2) >= 1.01 Then
getPrevOdds = Math.Round(odds - oddsInc, 2)
Else
getPrevOdds = 1.01
End If
End Function

Function getNextOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.98
oddsInc = 0.02
Case 3 To 3.95
oddsInc = 0.05
Case 4 To 5.9
oddsInc = 0.1
Case 6 To 9.8
oddsInc = 0.2
Case 10 To 19.5
oddsInc = 0.5
Case 20 To 29
oddsInc = 1
Case 30 To 48
oddsInc = 2
Case 50 To 95
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
If Math.Round(odds + oddsInc, 2) <= 1000 Then
getNextOdds = Math.Round(odds + oddsInc, 2)
Else
getNextOdds = 1000
End If
End Function

Function plusTicks(odds As Currency, ticks As Byte) As Currency
Dim i As Byte
For i = 1 To ticks
odds = getNextOdds(odds)
Next
plusTicks = odds
End Function

Function minusTicks(odds As Currency, ticks As Byte) As Currency
Dim i As Byte
For i = 1 To ticks
odds = getPrevOdds(odds)
Next
minusTicks = odds
End Function


I then have some conditional cell formatting in cells A4:A20, D4:D20, K4:K20, Q4:P20
User avatar
danjuma
 
Posts: 347
Joined: Mon Apr 21, 2008 4:17 pm
Top

Postby osknows » Fri May 20, 2011 12:43 pm

One thing that screams out is the use of `Wait` - how long are you waiting and what are you waiting for?
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am
Top

Postby osknows » Fri May 20, 2011 1:54 pm

I would replace the Worksheet_BeforeRightClick with this. Note: Have used column AR to timestamp when bet was placed

Code: Select all
Private Sub Worksheet_BeforeRightClick(ByVal target As Range, Cancel As Boolean)
Application.EnableEvents = False

If Not Application.Intersect(target, _
ThisWorkbook.Sheets(target.Worksheet.Name).Range("A4:A20")) Is Nothing Then

    'allow ony single cell click??
    If target.Columns.Count = 1 And target.Rows.Count = 1 Then
    dtTimeNow = Now()
        With ThisWorkbook.Sheets("BA")
            Cancel = True
            .Range("Q" & target.Row + 1).Value = .Range("V1").Value
            .Range("AR" & target.Row + 1).Value = dtTimeNow 'timestamp AR when Q is written
        End With
   
    End If
End If
Application.EnableEvents = True
End Sub



Then in the standard worksheet_change event monitor the timestamp and clear the bets

Code: Select all
Private Sub Worksheet_change(ByVal target As Range)


Dim i As Long, timestampArray() As Variant

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
ClearTimer = 1 '1second timer
dtTimeNow = Now()

With ThisWorkbook.Sheets("BA")
timestampArray = .Range("AR1:AR20").Value 'check timestamps in AR

For i = LBound(timestampArray) To UBound(timestampArray)
    If Not IsEmpty(timestampArray(i, 1)) Then
        If DateDiff("s", CDate(timestampArray(i, 1)), dtTimeNow) >= ClearTimer Then
            .Range("Q" & i).Value = "CLEAR" 'Clear Q
            timestampArray(i,1) = vbnullstring
        End If
    End If

Next i
.Range("AR1:AR20").Value = timestampArray
End With

Application.EnableEvents = True

End Sub


Last edited by osknows on Fri May 20, 2011 3:19 pm, edited 3 times in total.
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am
Top

Postby danjuma » Fri May 20, 2011 2:11 pm

osknows wrote:One thing that screams out is the use of `Wait` - how long are you waiting and what are you waiting for?


Hi Os, and many thanks for reply and assistance as usual. :D

The wait is set to 1, which I am assuming is 1 sec, in the sub code below, which I found some where on the net. Basically, what I wanted to do is place a "BACK" or "LAY" in a cell in column Q, and then clear the bet ref so that another bet can be placed when I want to again. So, the wait, was just for a brief pause, for the "BACK" or "LAY" statement to execute before placing "CLEAR".

I will try the suggestion/code you have provided and feedback to you. Many thanks m8. :D


Code: Select all
Sub Wait()
    waitTime = 1
    Start = Timer
    While Timer < Start + waitTime
        DoEvents
    Wend
End Sub
User avatar
danjuma
 
Posts: 347
Joined: Mon Apr 21, 2008 4:17 pm
Top

Postby danjuma » Fri May 20, 2011 3:11 pm

Many thanks osknows. I have used your suggested code, and I can see some improvement, and it also makes the coding neater and a lot less.

THANK YOU VERY MUCH SIR! :D :D :D
User avatar
danjuma
 
Posts: 347
Joined: Mon Apr 21, 2008 4:17 pm
Top
Post a reply
Return to Help

Who is online

Users browsing this forum: No registered users and 73 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.

Powered by phpBB® Forum Software © phpBB Group