VBA code is very slow

[Wade]

Hi there, I use Guardian and often have 40+ markets all refreshing at the same time at 20ms interval. I'm no VBA expert but I've got some basic code just to clear the bet status fields. With so many active markets it can be 20+ seconds to complete the cycle of my bet triggering, my code running to clear the status fields and my next bet triggering. If I only have a couple of markets refreshing in Guardian then the cycle between bets is only 1 second.

Any suggestions on how to improve on the 20+ second cycle time (bearing in mind my basic VBA knowledge) ? Or is the 40+ markets just asking too much of Bet Angel / Guardian / Excel and my pretty basic PC?

[sa7med]

Pretty hard to help without seeing the code

[ShaunWhite]

40 markets * 20ms is 0.8s. I'm not sure how the 20s+ comes into it.

[Wade]

@ShaunWhite, the 20+ second cycle time refers to how long Excel takes to trigger the next bet. The Guardian refresh cycle is not an issue.

@sa7med, here is some sample code. My best guess is that the Guardian data feed into Excel for the 40+ markets is taking precedence over the running of the VBA code. Like I say, when I'm only connected to a couple of markets in Guardian the VBA runs quickly.

Sub worksheet_change(ByVal Target As Range)
Set Target = Sheets("Bet Angel A1Match Odds").Range("A1MatchOddsHome")
If Target.Value = "PLACED" Then
Sheets("Bet Angel A1Match Odds").Select
Range("A1MatchOddsHome").Select
Selection.ClearContents
End If
End Sub

[ShaunWhite]

@ShaunWhite, the 20+ second cycle time refers to how long Excel takes to trigger the next bet. The Guardian refresh cycle is not an issue.

@sa7med, here is some sample code. My best guess is that the Guardian data feed into Excel for the 40+ markets is taking precedence over the running of the VBA code. Like I say, when I'm only connected to a couple of markets in Guardian the VBA runs quickly.

Sub worksheet_change(ByVal Target As Range)
Set Target = Sheets("Bet Angel A1Match Odds").Range("A1MatchOddsHome")
If Target.Value = "PLACED" Then
Sheets("Bet Angel A1Match Odds").Select
Range("A1MatchOddsHome").Select
Selection.ClearContents
End If
End Sub

You're using worksheet_change as a trigger.
That code will execute everytime any cell changes anywhere, and on a 20ms cycle that's 1000s of times a second (or that's what it's trying to do) and it's probably causing a bottleneck. You'd be better off looking for changes on just the cells you're interested in.

If you search the forum there are several threads which address the performance issues around running vba in parallel with BA, and they include various techniques to improve it.

[jimibt]

@ShaunWhite, the 20+ second cycle time refers to how long Excel takes to trigger the next bet. The Guardian refresh cycle is not an issue.

@sa7med, here is some sample code. My best guess is that the Guardian data feed into Excel for the 40+ markets is taking precedence over the running of the VBA code. Like I say, when I'm only connected to a couple of markets in Guardian the VBA runs quickly.

Sub worksheet_change(ByVal Target As Range)
Set Target = Sheets("Bet Angel A1Match Odds").Range("A1MatchOddsHome")
If Target.Value = "PLACED" Then
Sheets("Bet Angel A1Match Odds").Select
Range("A1MatchOddsHome").Select
Selection.ClearContents
End If
End Sub

You're using worksheet_change as a trigger.
That code will execute everytime any cell changes anywhere, and on a 20ms cycle that's 1000s of times a second (or that's what it's trying to do) and it's probably causing a bottleneck. You'd be better off looking for changes on just the cells you're interested in.

If you search the forum there are several threads which address the performance issues around running vba in parallel with BA, and they include various techniques to improve it.

Shaun - just catching this thread. Yes, as you say, this is being triggered a bundle of times per second, so you need to home in of the Target.Range (in this case values in the Status column). Don't have an example in front of me, but from memory, you could test:

Code: Select all

Dim betSheet  as WorkSheet
Dim statusRange as Range

'set workbook and status range
set betSheet  = ThisWorkbook.Sheets("Bet Angel")
set statusRange = betSheet.Range("O9:O70")

If NOT Application.Intersect(statusRange, betSheet.Range(Target.Address)) Is Nothing then
''' do your stuff
end if

I'll have a look at that later, but that's the gist

I also just noticed that you are reassigning the Target range in your code -don't do that as the change event is telling you what range has changed (Target is the parameter). you are therefore ignoring that declarative setting and going off on a wander of your own (highlighted in RED in the quoted code of yours). As an aside, given the re-assignment of Target range above, be very careful about any other active code that you have as there will be many undesirable side effects introduced thro either misinterpretation or blisful ignorance

[Edit] - As a further aside, you could/should declare a module level variable that you store the current market into. that way, you only clear the Status cells when the market name on the Bet Angel sheet is different to the one stored inside your module level variable. That will save 1000's of repeats on basically redundant calls.

[Wade]

Thanks guys, I'm a novice with VBA but I'll do my best to work with what you'v given me.

I did however revert to the excel template provide by Bet Angel without any VBA. I noticed that even when manually entering the bet commands in Excel, the cycle time for updating the status field with PLACING and then PLACED is directly affected by how many Guardian markets are linked to the workbook and refreshing.

For example, a dozen markets in Guardian translates to about a 10 second lag in Excel between manually entering the bet command and the status field updating to PLACED. The Last Updated field in Excel is only updating every few seconds even though Guardian is refreshing @20ms and the Guardian Last Update field does update as expected.

Is this to be expected when having multiple markets active in Guardian? Or is it to do with the capacity of my PC?

[sa7med]

Thanks guys, I'm a novice with VBA but I'll do my best to work with what you'v given me.

I did however revert to the excel template provide by Bet Angel without any VBA. I noticed that even when manually entering the bet commands in Excel, the cycle time for updating the status field with PLACING and then PLACED is directly affected by how many Guardian markets are linked to the workbook and refreshing.

For example, a dozen markets in Guardian translates to about a 10 second lag in Excel between manually entering the bet command and the status field updating to PLACED. The Last Updated field in Excel is only updating every few seconds even though Guardian is refreshing @20ms and the Guardian Last Update field does update as expected.

Is this to be expected when having multiple markets active in Guardian? Or is it to do with the capacity of my PC?

Hi,

Guardian refreshes through the whole list. So if your rate is 20ms and you have 12 markets then it would take 240 ms to refresh each market (20 * 12). Give a little more for excel to catch up.

Should be a lot less than 10 seconds though. I cycle through about 40 markets in 3 secs (with a lot of formulas and VBA). So it seems like you have something jamming you up. Dont think its your computer unless you're running pentium 3 or something with no ram. One thing could be your hard drive is failing. Google something like 'optimizing' excel to make sure you're okay. I know having the wrong language pack can mess things up a bit. Which excel are you on? There are versions which mess up with bet angel when you try and edit a cell when connected (see viewtopic.php?f=19&t=15746)