BA VBA Basics!

Discuss anything related to using the program (eg. triggered betting tactics)

Moderator: 2020vision

BA VBA Basics!

Postby danjuma » Sun Jul 18, 2010 7:50 pm

Ok, if somebody would be kind enough to explain the following, I would find it quite useful and I am sure many VBA newbies would as well, and would probably make a good sticky.

As I go through the many suggested VBA codes in the forum, what I notice is that quite often, codes are written in different ways to do the same thing and this is quite confusing to the VBA novice trying to understand the logic to the code so to be able to adapt it to his/her needs.

One of the things I find confusing is when to stick a code in a sheet and when to stick it in a module. Also the popular
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False...


What does it all mean and when to use and not to use?

So my question is. I have the following scenario:

A sheet named market1, linked to race 1 in BA, in a workbook
Another sheet named market2, linked to race 2 in BA, in the same workbook.

So at its simplest. I want an action to occur, say cell AA1 in sheet market1 to have the value Y, anytime sheet market1 updates.

Cell AA1 in sheet market2 to have a value Y2, anytime sheet market2 updates.

Cell AA2 in sheet market2 to have a value Y3 anytime sheet market1 updates (so for example, say market1 goes in play, but market2 not in play. I know you can achieve this just using excel formula, but I am just trying to understand how updates/refreshes in one sheet affects actions in another sheet in the same workbook where VBA is concerned)

A code like Gary's count up timer to work independently in each sheet. So if market1 goes in play, the timer starts for market1, and dose not affect/impact on the timer in market2?

Like I mentioned, this thread could make a good sticky, and people can add their tips and tricks and build on the thread.

Many thanks.
User avatar
danjuma
 
Posts: 347
Joined: Mon Apr 21, 2008 4:17 pm

Postby milfor » Sun Jul 18, 2010 9:46 pm

Yes, any VBA program that cooperates with BA should have the structure:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
...
Application.EnableEvents = True
End Sub

The first line means that the whole program is executed when something
changes in the worksheet (a refresh from BA).

The second line is necessary because BA doesn't refresh everything in
one go. Without this line the program would be run more than once for
each refresh. The 16 makes sure that the program is only run after the
'main' data in the worksheet changed.

The third line is even more important. Without it everytime the program
writes something into the worksheet the program would be run again
which leads again to changes which leads again to runs ... This chain
reaction would bring Excel down to its knees quickly. The third and the
fifth line prevent the program from being run again until it's finished.
milfor
 
Posts: 437
Joined: Mon Jun 26, 2006 1:44 am

Postby danjuma » Sun Jul 18, 2010 10:33 pm

milfor,

Very well explained. Thanks very much. :D

Anyone else wants to contribute please? Thanks
User avatar
danjuma
 
Posts: 347
Joined: Mon Apr 21, 2008 4:17 pm

Postby osknows » Mon Jul 19, 2010 7:58 pm

The only thing I can add is....

Private Sub Worksheet_Change(ByVal Target As Range)
...
End Sub

is a recognised EVENT in excel and will only work when placed in each Sheet object in the vba editor; the event triggers when a cell(s) on the specific worksheet changes. The contents & range of the changed cells are passed as a range object named 'Target' which can also be used within the procedure.

'ByVal' means the argument has been passed by value, which means the procedure cannot modify the variable itself. An alternative in some routines is to use 'ByRef' which means the procedure can modify the variable itself.

Milford has explained well the reasons for the remaining code to identify when BA is making the changes and not a user changing cells and disabling/renabling of events to stop endless loops.
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am

Postby dgs2001 » Tue Jul 20, 2010 8:23 am

And one more piece of the puzzle,

Code: Select all
If Target.Columns.Count <> 16 Then Exit Sub


The number 16 here refers to the number of columns that have changed.

This means that when Betting Assistant does a full refresh of the available Back and Lay prices, BA updates 16 columns of information on your worksheet, there are other refresh counts which update other bits of information.

I'm sure another kind soul will tell us what they are as I can't find the thread where they were listed :oops:

Duncan

edit - I'll post this anyway , but have just reread milfor's post and this is covered !!
User avatar
dgs2001
 
Posts: 334
Joined: Thu Apr 05, 2007 4:53 pm
Location: The Home Of National Hunt

Postby Bumble » Fri Aug 06, 2010 8:16 am

I'm assuming from the 'target.columns.count<>16' that BA updates Sheet1 one column at a time? If you've got any insight into how this works it would be much appreciated.

What I don't understand is how the event might be fired if, say, only 10 or 15 columns had changed. Does the event fire after 1 column, 2 columns etc and then only pass through the =16 filter after it's done that many columns? Why/what does it remember? Or are we just saying "ignore other changes on this sheet, only fire when 'the change' affects a block of 16 columns"?

I'm very new to all this and, so far, have been copying code from one of osknows' workbooks (for dutching, thanks osknows) which has
If Not Intersect(Target, ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A3:J16")) Is Nothing Then
[do stuff]


What I'm trying to do is work with the MyBets sheet. At present I've got a variant of the above (not intersect) condition, replacing "A3:J16" with "A1:F500" - on the assumption that I won't put more than 500 bets on a race. OK, I can reduce that to 50 or so but I still wonder if this is very inefficient and maybe I should use target.columns.count<>6 ?

Or is MyBets just updated in a big burst, so I might as well not have the condition and just fire whenever it changes?
User avatar
Bumble
 
Posts: 16
Joined: Fri Jun 18, 2010 5:57 am

Postby milfor » Fri Aug 06, 2010 11:15 am

Bumble wrote:Or are we just saying "ignore other changes on this sheet, only fire when 'the change' affects a block of 16 columns"?

Yes. I think the sheet is refreshed in 3 steps. One changes 16 columns, the others less (and therefore are ignored).

I think the MyBets sheet is changed in one go.
milfor
 
Posts: 437
Joined: Mon Jun 26, 2006 1:44 am

Postby osknows » Fri Aug 06, 2010 12:31 pm

Put this code in each sheet object of a completely blank Excel workbook, link to BA and you will see exactly which cells update on each refresh

Code: Select all
Private Sub worksheet_change(ByVal Target As Range)

MsgBox (Target.Worksheet.Name & vbCrLf & Target.Address)

End Sub
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am

Postby Bumble » Sat Aug 07, 2010 7:54 am

Many thanks milfor and osknows.

I've experimented and learned:
- MyBets refreshes with one write of all current bets; on loading a new market Gruss writes all current bets to MyBets; on loading a new market for which I have no bets, it just writes the header line

- Sheets 1 - on loading a new market, cells X4:Q4 (headings above the trigger are updated) then any extra rows from the previous market are removed; after that, alternatingly, it refreshed A1:Pn and T5:Xn (n depending on numbers of runners) ie updating prices then updating trigger area.

This i just what I needed to know. Thanks again.
User avatar
Bumble
 
Posts: 16
Joined: Fri Jun 18, 2010 5:57 am

Postby danjuma » Tue Aug 10, 2010 9:09 am

Just a quick one guys.

Is

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

'Code


Application.EnableEvents = True
End Sub


Same thing as

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then

'Code

End If
End Sub


Thanks
User avatar
danjuma
 
Posts: 347
Joined: Mon Apr 21, 2008 4:17 pm

Postby dgs2001 » Tue Aug 10, 2010 11:17 am

Yes they are the same, both are checking that 16 columns of information was changed in one go.
<> means does not equal
= means equals

Even if all back prices and lay prices stay the same, BA will rewrite them to your spreadsheet in a single block of 16 columns of information.

Duncan
User avatar
dgs2001
 
Posts: 334
Joined: Thu Apr 05, 2007 4:53 pm
Location: The Home Of National Hunt

Postby danjuma » Tue Aug 10, 2010 11:36 am

dgs2001 wrote:Yes they are the same, both are checking that 16 columns of information was changed in one go.
<> means does not equal
= means equals

Even if all back prices and lay prices stay the same, BA will rewrite them to your spreadsheet in a single block of 16 columns of information.

Duncan


Many thanks
User avatar
danjuma
 
Posts: 347
Joined: Mon Apr 21, 2008 4:17 pm

Postby chemist » Mon Jan 09, 2012 1:07 am

osknows wrote:Put this code in each sheet object of a completely blank Excel workbook, link to BA and you will see exactly which cells update on each refresh

Code: Select all
Private Sub worksheet_change(ByVal Target As Range)

MsgBox (Target.Worksheet.Name & vbCrLf & Target.Address)

End Sub


Nice idea but I would use Default.Print because MsgBox waits the program. Default.Print writes to the immediate window.
chemist
 
Posts: 3
Joined: Sun Jan 08, 2012 1:05 pm

Postby decodagobert » Wed Oct 17, 2012 11:53 pm

Hi all,

here I found exactly a part of my problem(s)

this is the normal code:

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
...
Application.EnableEvents = True
End Sub


but what's with a 2nd or 3rd market in the same sheet, the "<>16" it's just working for the 1st market startet from cell A1

any solution, for a sheet with a 2nd, 3rd or 4th market in the same sheet?? (A1, A100, A200, ...)

hoping for answer

decodagobert
decodagobert
 
Posts: 23
Joined: Wed Sep 08, 2010 1:09 am

Postby MiniBlueDragon » Wed Apr 03, 2013 1:57 am

osknows wrote:The only thing I can add is....

Private Sub Worksheet_Change(ByVal Target As Range)
...
End Sub

is a recognised EVENT in excel and will only work when placed in each Sheet object in the vba editor; the event triggers when a cell(s) on the specific worksheet changes. The contents & range of the changed cells are passed as a range object named 'Target' which can also be used within the procedure.


OK I'm confuddled.

I have the following sheets:
"Gruss" = Has the predominant 16 columns on it and live data from Betting Assistant. The market ID on this sheet is in cell N3.
"Race Card" = Sheet with various pieces of race card info on it.

And my code looks like this:
Code: Select all
Public CurrentMarketID As Long

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub

Application.EnableEvents = False

' Get current Market ID
CurrentMarketID = Sheets("Gruss").Range("N3").Value

' Note Current Market ID
Sheets("Race Card").Range("D1").Value = CurrentMarketID

'Check if Market ID changes
If CurrentMarketID <> Me.Cells(3, "N") Then
    CurrentMarketID = Me.Cells(3, "N")
    ' Run GetCard method
    GetCard
End If

Application.EnableEvents = True

End Sub


The "GetCard" method is in the "Module2" Excel object.

I've tried having that code in the "ThisWorkbook" Excel object in the VBA editor and also in the "Sheet 1 (Gruss)" object but it just doesn't want to run when the Betting Assistant swaps market.

What am I doing wrong? :(
MiniBlueDragon
 
Posts: 130
Joined: Tue Jul 05, 2011 1:14 pm
Location: London

Next

Return to Discussion

Who is online

Users browsing this forum: No registered users and 8 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.