Simple VBA question

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

Moderator: 2020vision

Simple VBA question

Postby Jack_123987 » Sat Jul 16, 2016 10:52 am

Hi all,

So what I am looking to do is have a worksheet to list results when my bet performance changes. For example, I already have a cell which will go up and down when the results of a bet is logged. For example, say winnings have been $25 and I win a $5 bet, the cell (lets call this cell Results!B27) will change to $30. Lets say the next result is a loss of $10, the cell (Results!B27) will go to $20.

I'm not very good at VBA but I've found this code to work when I manually update A1 on my new worksheet....

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.EnableEvents = False
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("A1").Value
Application.EnableEvents = True
End If
End Sub


However, when I change A1 to my results worksheet say =Results!B27 it stops working. I'm pretty sure its something simple I'm missing buy I'm not sure what.

Please could you help me.

Thanks
Jack
Jack_123987
 
Posts: 5
Joined: Sun Jul 03, 2016 3:57 am

Re: Simple VBA question

Postby Captain Sensible » Sat Jul 16, 2016 3:21 pm

Bit hung over so not too sure exactly what you're trying to do with performance changes? If you're simply logging the value of a cell when it changes you could try something like the following. It'd just check the value in A1 and leave an audit trail down column A. Its actually in the opposite direction of what you were previously doing, dunno if that's important or not. Easy enough to amend it to timpstamp in column B or just put the market details in if you're logging race results.

I think intersect is based on the active cell so would work fine manually but not wouldn't be triggered from the BA refreshes.

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

If Range("A2").Value <> Range("A1").Value Then
Range("A2").Insert (xlShiftDown)
Range("A2").Value = Range("A1").Value
End If


Application.EnableEvents = True

End Sub
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Simple VBA question

Postby Jack_123987 » Fri Jul 22, 2016 9:00 am

Hi Captain Sensible,

Thanks a lot for the reply but for some reason I can't get it work properly.

Overall what I'm trying to do is have my spreadsheet have a feature to do loss recovery.

The way I'm trying to do it is by having calculation on the results page be triggered when my loss balance increases.

The above suggestion was to have the changes in the loss calculation be the trigger to change the amount the spreadsheet bets.

The loss calculation does not work of BA refreshes but rather from the recalculation of the losses, which I suppose is pretty much the same thing.

Is there another way to achieve what I'm trying to do?

Thanks
Jack_123987
 
Posts: 5
Joined: Sun Jul 03, 2016 3:57 am

Re: Simple VBA question

Postby Captain Sensible » Fri Jul 22, 2016 2:39 pm

What do you mean by can't get it to work properly? The code not working? Maybe you need to fully reference any cells you want to use i.e, Worksheets("Results").Range("A1").Value rather than Range("A1").Value.

Or do you mean the loss recovery? Never been a fan of these recovery systems myself so I don't use them, The mentality of increasing stakes goes against my veiw of increasing stakes when things are going in your favour and dropping back when they're not. I'd have thought the simplest method of tracking any profit or loss was just by looking at your current balance against what you started with. Obviously you're always at the mercy of how quick Betfair settle markets but that'd be the same for any methods you'd use ie the results sheet. I know others just refresh the account balance before placing a bet to get the latest info to use for any stake calculations.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Simple VBA question

Postby bolpx001 » Mon Jul 25, 2016 2:33 pm

Completely agree with Captain Sensible. For me level stake is the only way to go, increasing slowly as results show a profit. - Paul
bolpx001
 
Posts: 297
Joined: Sat Nov 19, 2005 4:30 am
Location: Dublin, Ireland


Return to Discussion

Who is online

Users browsing this forum: No registered users and 12 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.