Auto Update Macro

[Shaun]

I need a macro to run every 30 seconds and wanted to find the best option.

There is plenty of info about Application.OnTime on the net but i was wondering if this is the best option considering we already have the betfair feed updating.

Suggestions please.

[faibo]

Consider using the Worksheet_change sub of a worksheet object.

http://www.gruss-software.co.uk/forum/v ... php?t=6547

If can also use the "Private Sub ba_pricesUpdated()" as stated in the help file.

Application.OnTime would work but i recommend the use of Worksheet/workbook change

[Shaun]

yes i understand now, design a counter and have my macro run when it is higher then say 30 and have the counter reset to zero.

Thanks.

[faibo]

Well, I dont know your case. Maybe you can elaborate, but that doesnt seems to be the best solution.

if you want to use .Ontime you just need to run this:

Code: Select all

dTime = Now + TimeValue("00:00:30")
Application.OnTime dTime, "MyMacro"


If you put it at the end of your macro it will be called every 30seconds. You just need to call your macro one time, then it will run auto.
You dont need any counter

I dont know if that's a fit for your code. I'm just guessing. You can PM me if you want to elaborate

[Shaun]

Can you look at this macro and see if there is an issue?

The timer part works ok but the bottom half of my macro that used to work has stop working.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
With ThisWorkbook
.Sheets("Data").Range("R1").Value = .Sheets("Data").Range("R1").Value + 1
If .Sheets("Control").Range("S5").Value = 0 Then
.Sheets("Data").Range("R1").Value = 0
GoTo Xit:
Else
If .Sheets("Data").Range("R1").Value > .Sheets("Control").Range("S5").Value Then
.Sheets("Data").Range("R1").Value = 1
BetChoice
Xit:
End If
End If
If .Sheets("Data").Range("T2").Value <> 1 Then
   .Sheets("Data").Range("T3").Value = 0
End If
If .Sheets("Data").Range("T2").Value = 1 And .Sheets("Data").Range("T3").Value <> 1 Then
    Paste1
   .Sheets("Data").Range("T3").Value = 1
End If

If .Sheets("Data").Range("U2").Value <> 1 Then
   .Sheets("Data").Range("U3").Value = 0
End If
If .Sheets("Data").Range("U2").Value = 1 And .Sheets("Data").Range("U3").Value <> 1 Then
  Result
   .Sheets("Data").Range("U3").Value = 1
End If

Application.EnableEvents = True
End With
End Sub


Here is the original macro that used to work fine.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
With ThisWorkbook
If .Sheets("Data").Range("T2").Value <> 1 Then
   .Sheets("Data").Range("T3").Value = 0
End If
If .Sheets("Data").Range("T2").Value = 1 And .Sheets("Data").Range("T3").Value <> 1 Then
    Paste1
   .Sheets("Data").Range("T3").Value = 1
End If

If .Sheets("Data").Range("U2").Value <> 1 Then
   .Sheets("Data").Range("U3").Value = 0
End If
If .Sheets("Data").Range("U2").Value = 1 And .Sheets("Data").Range("U3").Value <> 1 Then
  Result
   .Sheets("Data").Range("U3").Value = 1
End If

Application.EnableEvents = True
End With
End Sub


[Shaun]

I changed the code, not tested yet but i think should be ok.

Code: Select all


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
With ThisWorkbook
.Sheets("Data").Range("R1").Value = .Sheets("Data").Range("R1").Value + 1
If .Sheets("Control").Range("S5").Value = 0 Then
   .Sheets("Data").Range("R1").Value = 0
End If
If .Sheets("Data").Range("R1").Value > .Sheets("Control").Range("S5").Value Then
   .Sheets("Data").Range("R1").Value = 1
   BetChoice
End If

If .Sheets("Data").Range("T2").Value <> 1 Then
   .Sheets("Data").Range("T3").Value = 0
End If
If .Sheets("Data").Range("T2").Value = 1 And .Sheets("Data").Range("T3").Value <> 1 Then
    Paste1
   .Sheets("Data").Range("T3").Value = 1
End If

If .Sheets("Data").Range("U2").Value <> 1 Then
   .Sheets("Data").Range("U3").Value = 0
End If
If .Sheets("Data").Range("U2").Value = 1 And .Sheets("Data").Range("U3").Value <> 1 Then
  Result
   .Sheets("Data").Range("U3").Value = 1
End If

Application.EnableEvents = True
End With
End Sub


[osknows]

You may find the DateDiff function useful down to 1 second accuracy. If you need ms accuracy lookup 'vb timer class' in a search engine.

[Shaun]

Thanks, don't need it that accurate as i am importing webpages just need it to run.

[Shaun]

You can answer a question for me if that;s ok?

I have 6 macros to run that each import bookmakers prices, would i be better off running all the macros at once or have them in a sequence where at the end of each macro it will call the next macro and so on until all have run.

Right now they are in a sequence and it takes about 10 seconds to run all 6 this time is fine for me but just wondering the best option.

[faibo]

All code in vba run in sequence. single threading.

So, your 2 options are basically the same.

If you want faster times you can look here:
http://www.excelhero.com/blog/2010/05/multi-threaded-vba-update.html

[Shaun]

Gees that swam looks interesting in deed, i do a hell of a lot of scraping for almost every sheet i create.

I can see i won't understand a lot of this so will be asking for help in time,hehe.