COM programming Help!

[the_mathman]

Hi to all!
I want to program an excel sheet with all the horses that run in a day, with its back and lay quotes .

Starting from the test_BA_COM.xls file, i have written this code that don't work:



If ba Is Nothing Then
Set ba = New BettingAssistantCom.ComClass
End If
sports = ba.getSports()
For Each sport In sports
'MsgBox sport.sport & sport.sportid
If sport.sportid = 13 Then 'Horse - Today races
events = ba.getEvents(sport.sportid)

cont = 1
For Each evnt In events
If Not (InStr(evnt.eventName, "(") > 0) Then
With Worksheets("Events")
.Cells(cont, 1).Value = evnt.eventId
.Cells(cont, 2).Value = evnt.eventName
' result = ba.openMarket(evnt.eventid, evnt.exchangeId)
End With
cont = cont + 1
result = ba.openMarket(evnt.eventId, evnt.exchangeId)
prices = ba.getPrices()
cont2 = 1
For Each priceItem In prices
With Worksheets("Horse")
.Cells(cont2, 1).Value = evnt.eventId
.Cells(cont2, 2).Value = priceItem.Selection
.Cells(cont2, 3).Value = priceItem.backOdds1
.Cells(cont2, 4).Value = priceItem.layOdds1
End With
cont2 = cont2 + 1
Next
End If
Next
End If
Next



The error is about an "The object don't exist" on the
.Cells(cont2, 2).Value = priceItem.Selection

code row.
Where i wrong?

[the_mathman]

I add some information for someone that can help me:

I have understand that:
->When i create the ba object, i can use the
- ba.getSports() function
- ba.getEvents(sportid) function
is right?

->If I want to use the ba.getPrices() function I must before open a market with the ba.openMarket() function
is right?

of course, the error that i riceive is beacuse i don't have instantiated some object before, but which?

[GaryRussell]

BA does not wait for the prices to appear after openMarket so your code tries to read them before they are available. This will be fixed in a future release. In the meantime you can code around it. Try the following.

Code: Select all

Public Sub test()
    If ba Is Nothing Then
        Set ba = New BettingAssistantCom.ComClass
    End If
    sports = ba.getSports()
    For Each sport In sports
        'MsgBox sport.sport & sport.sportid
        If sport.sportid = 13 Then 'Horse - Today races
            events = ba.getEvents(sport.sportid)
            cont = 1
            For Each evnt In events
                If Not (InStr(evnt.eventName, "(") > 0) Then
                    With Worksheets("Events")
                        .Cells(cont, 1).Value = evnt.eventId
                        .Cells(cont, 2).Value = evnt.eventName
                        ' result = ba.openMarket(evnt.eventid, evnt.exchangeId)
                    End With
                    cont = cont + 1
                    result = ba.openMarket(evnt.eventId, evnt.exchangeId)
                    Do
                        prices = ba.getPrices()
                    Loop Until UBound(prices) > 0
                    While Val(prices(0).marketId) <> evnt.eventId
                        prices = ba.getPrices()
                    Wend
                    cont2 = 1
                    For Each priceItem In prices
                        With Worksheets("Horse")
                            .Cells(cont2, 1).Value = evnt.eventId
                            .Cells(cont2, 2).Value = priceItem.Selection
                            .Cells(cont2, 3).Value = priceItem.backOdds1
                            .Cells(cont2, 4).Value = priceItem.layOdds1
                        End With
                        cont2 = cont2 + 1
                    Next
                End If
            Next
        End If
    Next
End Sub


[the_mathman]

thank you Gary!
I try it this night.


A question:
for my purpose, the use of the command
result = ba.openMarket(evnt.eventId, evnt.exchangeId)

is required?
If I want to use the ba.getPrices() function for all the events, must i open all the markets?

thank you again

[GaryRussell]

thank you Gary!
I try it this night.


A question:
for my purpose, the use of the command
result = ba.openMarket(evnt.eventId, evnt.exchangeId)

is required?
If I want to use the ba.getPrices() function for all the events, must i open all the markets?

thank you again

Yes, it needs to open the market first before the prices will be available to getPrices()

[xdg99667]

Hi Gary

I'm using your code to get a list of runners etc and its working fine

I wanted to get some additional information - so i linked an excel sheet to the gruss tab (so it shows the current market)

and then put a line in to blank A1 each time ba.openmarket

and a

Code: Select all

While Sheets(Win Market").[A1] = ""
DoEvents
Wend.

It works brilliantly when i'm stepping through the code, but when i take all the breakpoints off and let it go it gets itself in a twist and hang (both VBE and Gruss (the later of which hangs on getting the information on the market) after a few markets

I will post the full code below

can you suggest anything?

Thanks, Gary

Code: Select all

Public Sub test2()
    If ba Is Nothing Then
        Set ba = New BettingAssistantCom.ComClass
    End If
    sports = ba.getSports()
    For Each sport In sports
        'MsgBox sport.sport & sport.sportid
        If sport.sportid = 13 Then 'Horse - Today races
            events = ba.getEvents(sport.sportid)
            cont = 1
            cont2 = 5
            For Each evnt In events
                If Not (InStr(evnt.eventName, "(") > 0) Then
                    cont = cont + 1
                    'Debug.Print evnt.eventId; " : " & evnt.exchangeId
                    Sheets("Win Market").[a1] = ""
                    result = ba.openMarket(evnt.eventId, evnt.exchangeId)
                    Do
                        prices = ba.getPrices()
                    Loop Until UBound(prices) > 0
                    While Val(prices(0).marketId) <> evnt.eventId
                        prices = ba.getPrices()
                    Wend
                   
                    While Sheets("Win Market").[a1] = ""
                        DoEvents
                    Wend
                   
                    Debug.Print Sheets("Win Market").[a1]
                   
                    cont2 = cont2 + 1
                    For Each priceItem In prices
                        With Worksheets("Horse")
                            .Cells(cont2, 2).Value = Sheets("Win Market").[a1]
                            .Cells(cont2, 3).Value = evnt.startTime
                            .Cells(cont2, 4).Value = evnt.eventId
                            .Cells(cont2, 5).Value = priceItem.Selection
                            .Cells(cont2, 6).Value = priceItem.backOdds1
                            .Cells(cont2, 7).Value = priceItem.layOdds1
                        End With
                        cont2 = cont2 + 1
                    Next
                End If
            Next
        End If
    Next
End Sub

[GaryRussell]

It's possibly the DoEvents loop. It's never a good idea. Try to avoid have your code waiting for something to happen in a loop. DoEvents might even be causing your code to be re-entered which would explain why it locks BA because it's constantly running getSports()

Where your code updates the cells it might also be causing re-entry there because it triggers a Worksheet_Change event (depends if your sub is called from this event). The first thing you can try is preventing events firing while your code is running. Try the following code. If it doesn't help let me know and I will advise how you can avoid having the DoEvents loop.

Code: Select all

Public Sub test2()
    Application.EnableEvents = false
    If ba Is Nothing Then
        Set ba = New BettingAssistantCom.ComClass
    End If
    sports = ba.getSports()
    For Each sport In sports
        'MsgBox sport.sport & sport.sportid
        If sport.sportid = 13 Then 'Horse - Today races
            events = ba.getEvents(sport.sportid)
            cont = 1
            cont2 = 5
            For Each evnt In events
                If Not (InStr(evnt.eventName, "(") > 0) Then
                    cont = cont + 1
                    'Debug.Print evnt.eventId; " : " & evnt.exchangeId
                    Sheets("Win Market").[a1] = ""
                    result = ba.openMarket(evnt.eventId, evnt.exchangeId)
                    Do
                        prices = ba.getPrices()
                    Loop Until UBound(prices) > 0
                    While Val(prices(0).marketId) <> evnt.eventId
                        prices = ba.getPrices()
                    Wend
                   
                    While Sheets("Win Market").[a1] = ""
                        DoEvents
                    Wend
                   
                    Debug.Print Sheets("Win Market").[a1]
                   
                    cont2 = cont2 + 1
                    For Each priceItem In prices
                        With Worksheets("Horse")
                            .Cells(cont2, 2).Value = Sheets("Win Market").[a1]
                            .Cells(cont2, 3).Value = evnt.startTime
                            .Cells(cont2, 4).Value = evnt.eventId
                            .Cells(cont2, 5).Value = priceItem.Selection
                            .Cells(cont2, 6).Value = priceItem.backOdds1
                            .Cells(cont2, 7).Value = priceItem.layOdds1
                        End With
                        cont2 = cont2 + 1
                    Next
                End If
            Next
        End If
    Next
    Application.EnableEvents = true
End Sub

[xdg99667]

Cheers for the reply mate, I'll have a butchers when I get home

[xdg99667]

sorry just to add, there is nothing on the worksheet_change event but i will try turning events off.

I cant think of a way around it (the DoEvents loop) myself

Cheers, Gary

[GaryRussell]

sorry just to add, there is nothing on the worksheet_change event but i will try turning events off.

I cant think of a way around it (the DoEvents loop) myself

Cheers, Gary

You can't, but I can

[xdg99667]

Hi Gary

I disabled the events, tried a few other ways and also set the name of the last market (eg A1) to a variable and monitored the value (rather than blanking it)

but alas everything that i tried just led to it locking up again.

I'm in your hands (or brain)

Cheers, Gary

[GaryRussell]

This is how I would do it. I have tested the code below. You need to use the pricesUpdated event fired by BA therefore it needs to be in an object module. Put the following code in the "Win Market" module. You can see that the next market is opened after the pricesUpdated event has fired and the worksheet has been updated with the same market id.

Code: Select all

Dim WithEvents ba As BettingAssistantCom.ComClass
Dim currentMarketId As String, marketUpdated As Boolean, eventIdx As Integer
Dim cont2 As Integer
Dim events As Variant

Sub test2()
    If ba Is Nothing Then
        Set ba = New BettingAssistantCom.ComClass
    End If
    sports = ba.getSports()
    For Each sport In sports
        'MsgBox sport.sport & sport.sportid
        If sport.sportid = 13 Then 'Horse - Today races
            events = ba.getEvents(sport.sportid)
            cont2 = 5
            eventIdx = 0
            openNextMarket
        End If
    Next
End Sub

Private Sub openNextMarket()
    Dim found As Boolean
    While eventIdx <= UBound(events) And Not found
        If Not (InStr(events(eventIdx).eventName, "(") > 0) Then
            found = True
            currentMarketId = events(eventIdx).eventId
            marketUpdated = False
            'Debug.Print "Opening market id:" & events(eventIdx).eventId
            result = ba.openMarket(events(eventIdx).eventid, events(eventIdx).exchangeId)
        End If
        eventIdx = eventIdx + 1
    Wend
    If eventIdx > UBound(events) Then Debug.Print "Finished."
End Sub

Private Sub ba_pricesUpdated()
    If Not marketUpdated Then
        prices = ba.getPrices()
        If prices(0).marketId = currentMarketId And [N3] = currentMarketId Then
            marketUpdated = True
            Debug.Print [A1]
            For Each priceItem In prices
                With Worksheets("Horse")
                    .Cells(cont2, 2).Value = [A1]
                    .Cells(cont2, 3).Value = events(eventIdx - 1).startTime
                    .Cells(cont2, 4).Value = events(eventIdx - 1).eventid
                    .Cells(cont2, 5).Value = priceItem.Selection
                    .Cells(cont2, 6).Value = priceItem.backOdds1
                    .Cells(cont2, 7).Value = priceItem.layOdds1
                End With
                cont2 = cont2 + 1
            Next
            openNextMarket
        End If
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count = 16 Then
        If [F2] = "Closed" Then openNextMarket
    End If
End Sub


[xdg99667]

Many many thanks mate

worked like a charm. I put another indicator in to check if it had loaded the additional info (selection id) before it logged each one

Cheers fella, Gary