BF ladder into Excel question - Calling Gary/Mark

[grimesy]

ALSO POSTED ON THE DISCUSSION THREAD

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:-

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

Code: Select all

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.

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

Code: Select all

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

[GaryRussell]

There aren't any functions in BA that can help so basically you are asking how to make your code more efficient. I won't have opportunity to look at this week and I anticipate this could take hours. I'll take a look next week if I get some spare time.

It involves deciding which internal data structures to use so that comparisons between each refresh can be made without reading from the worksheet which uses more CPU and then writing the data to the worksheet in one write operation. Your code currently does many individual writes to the worksheet which is inefficient. Also I am not sure those .Select statements are necessary and will also slow things down.

[grimesy]

Thanks Gary for your reply.

I’d be interested in your thoughts next week when you get a moment.

I’m hoping there is a work-around i.e.

I can set each of the target 16 columns automatically updating data [last price matched; total volume traded, etc] for each runner in Excel; however, there surely must be some way to harness that to the TradedVols arrays.

In the meantime, I think I have a workaround that will reduce the Private Sub calcs.