How do you wait for "OK"?

[GaryCook]

HI,

I'm waiting for an "OK" to let me know a command has been accepted via Excel but when I do a VBA whole loop it just loops indefinitely until Excel crashes.

When I don't try and detect that with VBA code it works fine but I need the function described for what I am working on.

Thanks for any help in advance.

[GaryCook]

Ok (no pun intended), I get what it is doing now.

VBA isn't great with async code.

It's just moving onto the command for the selection to the loop so the "OK" injection into the cell isn't being fired in the first place.

SO, I would need a way to solve that.

Working on it myself but any input would be awesome.

Could do a WAIT but that's minimum 1 sec increments, I believe, need shorter for this. And Sleep seems a bit overkill.

[sa7med]

Hard to see what your issue is without code snippets. Are you basing your code on a worksheet change event? If so you could do something like this

edit: woops forgot to increment by 2, fixed

Code: Select all

Private Sub Worksheet_change(ByVal Target As Range)

If Not Application.Intersect(Range("O9:O60"), Range(Target.Address)) _
            Is Nothing Then
            Dim j As Integer
            j = 9
                Do Until Range("B" & j) = ""
                    If Range("O" & j).Value = "OK" Then
                               'your code here
                    End if 
                    j = j + 2 
                Loop
End If

[Wolf1877]

I think your issue is probably the way that Bet Angel/Guardian and Microsoft Excel.Interop work together behind the scenes. I did some tests a couple of years ago that seemed to show that BetAngel/Guardian skips excel frame refreshes whilst an Excel Macro is still running, so you running a loop in your Excel VBA waiting for the cell to be refreshed with OK will actually stop the status cell being refreshed and probably also stop the Bet Angel Back/Lay or whatever command being executed.

You might be able to get around your issue by coding Application.DoEvents within your loop. I dont use Application.DoEvents myself but I seem to remember Shaun White was running some code with continuous loops though I think he is now uses bespoke API code for his automation now.

I think the best way to do what you want this is by retriggering a new instance of the VBA macro on each refresh of the Bet Angel worksheet by Guardian. Remember all your VBA variables will be lost because the previous excel VBA macro instance will have terminated so you might want to store some values in cells in a separate Tracking worksheet so you can keep track of the overall state of things. You could also write a bit of code checking for returned status data in cell column "O" rows 9, 11, 13 etc whenever a command is found in column "L" rows 9, 11, 13 etc.

So for each Bet Angel Worksheet you would code something like in the following image that I have coded for my "Bet Angel (10)" worksheet.
Note you need to make sure that Sheet(nn) needs to be consistently named as Bet Angel (nn) in the worksheet properties as highlighted in yellow in the Properties Box in the image where nn=10 in the example. This allows you to write a single VBA macro module that can easily process every Bet Angel (nn) worksheet and access the Bet Angel worksheet cells via Integer subscript s. Read/Write access to excel cells using integer sheet number and integer cell coordinates runs a lot faster IMO than all that Range stuff that people tend to code usually picked up from recorded macros.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$C$2:$C$6" Then
     Call LogData(10)
   End If
End Sub

Then

Code: Select all

Sub LogData(s As Integer)

     ' Create a new VBA Module with LogData subroutine to be run on each Guardian Frame Refresh for each Bet Angel Worksheet then you just access the BA cells via subscript s

      unmatched_bets = Sheets(s).Cells(5, 3).Value
      race_matched = Sheets(s).Cells(2, 3).Value
      runners =  Sheets(s).Cells(4, 3).Value
           
      '  insert your bespoke VBA code here 
      

EndSub