Update balance - Multiple markets in same sheet - VBA

[WBI]

hi all,

Im quite new to using VBA so this may be a simple this to resolve.
what im trying to do is update the balance/exp when needed/conditions are met.
I currently have all football markets open in the first tab/page then log them to the same sheet.
in cell S1 (and every 10th cell ie S11,S21) i have a formula that tells me if a bet has been cancelled within that market(this is needed as i normally have a lot of bets firing off and stake is based on balance...so bets get cancelled due to available balance) - Y/N, (also covers market state etc)
In cell AL1 i have a formula that counts all the 'Y's in S:S to the last market, output = 1,2,3 etc (i found i needed to do this so if there isnt a 'Y' in S1 but in S11 the macro will trigger)
The macro observes cell $AL$1, if>=1 then
look in S1 and every 10th for 'Y' then
if 'Y' it will update balance for that market by putting 'U' in the relevant 'J#' cell.

This all works perfectly, however after a few hours of running the computer becomes unresponsive/frozen. (disable macro=no problem), this has me thinking i need to flush mem/cache/whatever but like i said im new to this so may need something different.

my code:-
Module

Code: Select all

Sub updatebalance()
            Range("S1").Select
                    Do Until IsEmpty(Selection.Value)
            If ActiveCell.Value = "Y" Then
                 ActiveCell.Offset(1, -9).Value = "U"
            End If
    ActiveCell.Offset(10, 0).Select
Loop
End Sub

Sheet1

Code: Select all

Private Sub worksheet_change(ByVal target As Range)
    If target.Columns.Count = 16 Then
        Application.EnableEvents = False
       With Application
        .EnableEvents = False
        .ScreenUpdating = False
        If UCase(Me.Range("$AL$1").Value) >= 1 Then
            Call updatebalance
        End If
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    End If
End Sub

BA version 1.1.0.65z9c

Cheers
WBI

[osknows]

Hello,

You may not need code at all. In the latest beta versions of BA in options/preferences/general there is an option for balance refresh rate. If you set this to something reasonable like 60 then the balance will update every 60 seconds.

I've always been led to believe that refreshing the balance too often is detrimental to the performance of BA so wouldn't recommend refreshing more than once every 60seconds. Things may have changed though?

If you still want to use code then you could use the following

Code: Select all

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count <> 16 Then Exit Sub
   
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        If UCase(Me.Range("$AL$1").Value) >= 1 Then
            Call updatebalance(Target.Parent)
        End If
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

Code: Select all

Option Explicit

Const balanceRateSec As Long = 60
Dim lastBalanceUpdate As Date

Sub updatebalance(ByVal ws As Worksheet)
Dim timeNow As Date

    'With timer
    timeNow = Now
    With ws
        If .Range("S1").Value = "Y" And _
            DateDiff("s", lastBalanceUpdate, timeNow) > balanceRateSec Then
                lastBalanceUpdate = timeNow
                ws.Range("Q2").Value = "U"
        End If
    End With
   
'    'Without Timer
'    With ws
'        If .Range("S1").Value = "Y" Then
'            ws.Range("Q2").Value = "U"
'        End If
'    End With

End Sub

[WBI]

cheers Osknows,

it seems the beta version is perfect for my needs as i use the 'F**' function in my triggers, that combined with auto refresh of the balance.
However ill have a look at your code tomorrow and see how it compares.

Regards
WBI