VBA Code assistance - Get AllTraded Volume

[Livermore]

Hello,

Would anyone be so generous and provide an insight to how best I include a condition statement within the DEFAULT VBA “Get AllTraded Volume_Example” excel file.

As is, the codes “fails” upon a runner having no bets matched.

This scenario is regular in the events I trade up until the last few minutes. Occasionally, a single runner may have no bets matched.

A satisfactory solution for a Value = 0 would be to display the equivalent “Back Odds 1” cell.

I can manage the IF THEN ELSE statement on a worksheet however, the debugger still halts the routine whilst ever a runner has no bets matched.

I’d appreciate any input and have included the code below with the line failing shown in BOLD.

Many thanks
Livermore



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
Dim totalTraded As Double, totalProfit As Double
tradedVol = ba.getAllTradedVolume
If Not IsEmpty(tradedVol) Then
Dim i As Integer, r As Integer, selecName As String, j As Integer
r = 5
Do
selecName = Cells(r, 1).Value
If selecName <> "" Then
For i = 0 To UBound(tradedVol)
If tradedVol(i).Selection = selecName Then
Cells(r, 26).Value = tradedVol(i).actualBsp
Cells(r, 27).Value = tradedVol(i).totalBspBackMatchedAmount
Cells(r, 28).Value = tradedVol(i).totalBspLiabilityMatchedAmount
tradedVols = tradedVol(i).tradedVolumes
totalTraded = 0
totalProfit = 0
For j = 0 To UBound(tradedVols)
If tradedVols(j).totalMatchedAmount <> 0 Then
totalTraded = totalTraded + tradedVols(j).totalMatchedAmount
totalProfit = totalProfit + (tradedVols(j).odds - 1) * tradedVols(j).totalMatchedAmount
End If
If tradedVols(j).odds = Cells(r, 15).Value Then
Cells(r, 25).Value = tradedVols(j).totalMatchedAmount
End If
Next
Cells(r, 29).Value = Round((totalProfit / totalTraded) + 1, 2) Exit For
End If
Next
End If
r = r + 1
Loop Until selecName = ""
End If
Application.EnableEvents = True
End If
End Sub

[alrodopial]

I don't know what the code does but I guess it's failing because the "totalTraded" is empty.

You can try this instead the failing line:

If totalTraded = "" then
Cells(r, 29).Value = "No bets matched"
else
Cells(r, 29).Value = Round((totalProfit / totalTraded) + 1, 2) Exit For
End if

[Livermore]

Thanks alrodopial for the suggestion, but no luck I'm afraid.

The CODE itself produces the WEIGHTED AVERAGE PRICE for each selection in an event.

I factor this with the Last BID & OFFER & Last TRADE price when calculating FAIR VALUE.

The file is available off the HELP menu in BA >> User Guide >> Version History >> Version 65 changes >> COM changes

I appreciate your interest and time given.
All the best

[KevinTHFC]

Rather than blank totalTraded probably equal 0, so try the following.
Idf it doesn't work what is the value of totalTraded when the code fails?

If totalTraded = 0 then
Cells(r, 29).Value = "No bets matched"
else
Cells(r, 29).Value = Round((totalProfit / totalTraded) + 1, 2) Exit For
End if