Getting the Betfair Ladder into Excel: question

[grimesy]

Folks,

I use the following Private Sub to transpose the BF ladder into Excel columns for each runner in a race.

Only one problem - every time BA refreshes, the macro will fire. This takes my CPU usage to colse to 100%; as a result, I cannot compile, or indeed activate, any other subs/functions in the Worksheet.

Here is my code:-

===========================================

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
If ba Is Nothing Then
Set ba = New BettingAssistantCom.ComClass
End If
Dim tradedVol As Variant, tradedVols As Variant
tradedVol = ba.getAllTradedVolume
If Not IsEmpty(tradedVol) Then
Dim i As Integer, c As Integer, k As Integer, selecName As String, j As Integer
c = 28
Cells(3, c).Select
Do
If ActiveCell.Value <> "" Then
For i = 0 To UBound(tradedVol)
If tradedVol(i).Selection = ActiveCell.Value Then
tradedVols = tradedVol(i).tradedVolumes
j = UBound(tradedVols)
ActiveCell.Select
ActiveCell.Offset(2, 0).Select
For k = 0 To j
Range("counter") = k
ActiveCell.Value = tradedVols(k).Odds
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = tradedVols(k).totalMatchedAmount
ActiveCell.Offset(1, -1).Select
Next k
Exit For
End If
Next
End If
If ActiveCell.Value < 1.01 Then
Cells(3, c).Select
c = c + 6
Cells(3, c).Select
End If
Loop Until ActiveCell.Value = ""
End If
Application.EnableEvents = True
End If
Range("jolly_LPM").Select
End Sub

===========================================

What I need the coding to do instead, is to only update the data that has changed, rather than looping the whole way through each BF ladder for each runner.

For example, consider the following code; this event is fired on each refresh which is an ideal time to call getPrices.

===========================================

Sub ba_pricesUpdated()
prices = ba.getPrices
i = 4
For Each priceItem In prices
i = i + 1
Cells(i, 1).Value = priceItem.Selection
Cells(i, 2).Value = priceItem.backOdds1
Cells(i, 3).Value = priceItem.layOdds1
Cells(i, 4).Value = priceItem.lastMatched
Cells(i, 5).Value = priceItem.totalMatched
Next
End Sub

===========================================

Only priceItems that change are updated in the Excel sheet.

I want my coding for the BF ladder to so the same.

i.e. 95% of the records in each ladder do not change from one refresh to the next; there is no need to loop endlessly through each ladder.

Thoughts/comments/suggestions/ideas/help all greatly appreciated.

Grimesy

[dgs2001]

I would suggest doing all your calculation within the vba code, as its much quicker than reading/writing to the sheet.

To do this you would need to read the prices from the sheet into an array, (1 single read operation) and then get the current prices into a second array.

then compare the arrays, before writing the result back to your sheet in 1 single write operation.

There is some good info and code snippets about arrays here including code to compare two arrays :-
http://www.cpearson.com/Excel/VBAArrays.htm

Duncan