I would like to be able to use the COM interface in a synchronous way so that when I open a market & get the prices, I get the instant prices etc and no refreshes at all. I appreciate that I might be able to set the refreshrate to a large number, but it appears that I'm getting "partial" data on the 1st refresh i.e. I'm not getting the saddlecloth numbers immediately; they are initially coming up as zero and so I needed to wait for the 2nd or subsequent ba_pricedUpdated call
My initial approach was to try to figure out when I've got al the saddlecoth numbers by using an excel countif() function and when all teh saddlercloth numbers are non-zero set my BA COM object = Nothing and this seemed to work OK, but I have multiple worksheets (one per race) so I needed to use a "Mutex" to single-thread the access to BA
This seems to work OK on my computer but using it on a friends, it appears that the BA COM object is being destroyed a bit early.
Is there any way that I can use the COM interface to request the price & saddlecloth infor for a single race and once all information has been received, stop i.e. a take a single snap-shot
For the excel proficient, I have included my worksheet macro below. You will see I am looking for the saddlecloth numbers in column A and have an excel function "=COUNTIF(A2:A99,"=0")" in cell AB1
My next approach is to put all the code below into a single modulew rather than into each worksheet and pass the worksheet as a parameter
- Code: Select all
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
' Mutex Stuff
Private Declare Function CreateMutex Lib "kernel32" Alias "CreateMutexA" (lpMutexAttributes As Any, ByVal bInitialOwner As Long, ByVal lpName As String) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function ReleaseMutex Lib "kernel32" (ByVal hMutex As Long) As Long
Const ERROR_ALREADY_EXISTS = 183&
Dim mutex As Long
Dim WithEvents ba As BettingAssistantCom.ComClass
Sub getMutex()
Dim my_mutex As Long
Dim got_mutex As Boolean
got_mutex = False
While got_mutex = False
my_mutex = CreateMutex(ByVal 0&, 1, "BFMutex")
If Err.LastDllError = ERROR_ALREADY_EXISTS Then
ReleaseMutex (my_mutex)
CloseHandle (my_mutex)
Debug.Print Me.Name & ": Waiting for mutex"
Sleep (100)
DoEvents
Else
got_mutex = True
mutex = my_mutex
Debug.Print Me.Name & ": acquired mutex"
End If
Wend
End Sub
Sub refreshData()
'release_mutex
getMutex
Me.Range("A2:P99").ClearContents
If ba Is Nothing Then
Set ba = New BettingAssistantCom.ComClass
End If
result = ba.openMarket(Me.Cells(1, 27), Me.Cells(2, 27))
ba.refreshrate = 0.2
End Sub
Private Sub ba_pricesUpdated()
prices = ba.getPrices
i = 2
For Each priceItem In prices
Cells(i, 1).Value = ba.getsaddlecloth(priceItem.Selection)
Cells(i, 2).Value = priceItem.Selection
Cells(i, 3).Value = priceItem.backOdds3
Cells(i, 4).Value = priceItem.backOdds2
Cells(i, 5).Value = priceItem.backOdds1
Cells(i, 6).Value = priceItem.layOdds1
Cells(i, 7).Value = priceItem.layOdds2
Cells(i, 8).Value = priceItem.layOdds3
Cells(i, 9).Value = priceItem.backMoney3
Cells(i, 10).Value = priceItem.backMoney2
Cells(i, 11).Value = priceItem.backMoney1
Cells(i, 12).Value = priceItem.layOdds1
Cells(i, 13).Value = priceItem.layOdds2
Cells(i, 14).Value = priceItem.layOdds3
Cells(i, 15).Value = priceItem.lastMatched
Cells(i, 16).Value = priceItem.totalMatched
i = i + 1
Next
' While i >= 2
' i = i - 1
' Cells(i, 1) = ba.getsaddlecloth(Cells(i, 2))
' Wend
If Cells(1, 28).Value = 0 Then
Set ba = Nothing
release_mutex
End If
End Sub
Sub release_mutex()
ReleaseMutex (mutex)
CloseHandle (mutex)
Debug.Print Me.Name & ": released mutex"
End Sub