COM betStatus

[Bid]

It's defintely the getbet statement that is taking up the time. I've been testing with using it on it's own with a known betID.

Something like this:

Code: Select all

Option Explicit

Dim ba As New BettingAssistantCom.ComClass

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Bet As Object
    Dim betStatus As String
    Dim requestedSize As Double
    Dim matchedSize As Double

        Set Bet = ba.getbet("99999999")
        betStatus = Bet.betStatus
        requestedSize = Bet.requestedSize
        matchedSize = Bet.matchedSize

    Debug.Print Time & " ID: " & tmpBetID & " Status: " & betStatus & " Requested: " & requestedSize & " Matched: " & matchedSize
End Sub

Would declaring Bet as type Object have anything to do with it?

[GaryRussell]

The first time the COM object is used the response time is longer. Make sure that BettingAssistantCom.ComClass is only instantiated once. Is your PlaceBet sub in a separate module with it's own instance of BettingAssistantCom.ComClass? It would be better if you had the following statement in one module only so that every call uses this instance.

Code: Select all

public ba As New BettingAssistantCom.ComClass

Does this help?

[GaryRussell]

It's defintely the getbet statement that is taking up the time. I've been testing with using it on it's own with a known betID.

Something like this:

Code: Select all

Option Explicit

Dim ba As New BettingAssistantCom.ComClass

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Bet As Object
    Dim betStatus As String
    Dim requestedSize As Double
    Dim matchedSize As Double

        Set Bet = ba.getbet("99999999")
        betStatus = Bet.betStatus
        requestedSize = Bet.requestedSize
        matchedSize = Bet.matchedSize

    Debug.Print Time & " ID: " & tmpBetID & " Status: " & betStatus & " Requested: " & requestedSize & " Matched: " & matchedSize
End Sub

Would declaring Bet as type Object have anything to do with it?

In the above example is it the same if you run it multiple times?

[Bid]

You're right, the first time it runs it takes a while, then subsequently it's pretty quick. I was only really once in my testing as I was stepping through the code trying to work out where it was failing.

This is fine for my needs as this part of my code doesn't need to run very often.

Thanks for all your help - I think my system is actually ready to go fully automated now so it's time to make some money!