Help with VB to run a macro

[allgreen]

I am a VB novice and wonder if someone can help me with the code to run my macro please. I have looked at most of the examples on these forums and also searched the Mr Excel forums but as yet can't get it to work to my satisfaction.

I am trying to run a macro that will copy and paste my 'race sheet' each time a bet is completed so that I can go back later and review the market conditions that were in place when each bet was made. If it can be done I figure the best way/time to run the macro is just after the profit/loss figure changes for any runner in the range X5:X28 on the data download page. This would have the advantage of also triggering the macro when an offset bet is matched, even though the original bet references are still showing in the appropriate cells.

I initially thought that Worksheet_SelectionChange would do the trick but after some trial and error and a search of the Microsoft Knowledge Base it appears that this is an 'on click' event and doesn't appear to be triggered by changes to the data in the target range.

The code below ran the macro each time a cell in the target range was (manually) selected rather than when data was entered and I couldn't get it to run when a bet was triggered automatically from Excel. (2003)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ErrXIT
If Not Intersect(Target, Range("X5:X28")) Is Nothing Then
Application.EnableEvents = False
Run "Screen_Shot"
End If
ErrXIT:
Application.EnableEvents = True
End Sub

Conversely, Worksheet_Change event did trigger the macro automatically each time the data was refreshed by BA so I guess this begs the question can I code it such that all but the appropriate range is excluded?

I imagine not because that is the purpose of Worksheet_SelectionChange?

Thanks for any consideration.

[Mitch]

The Worksheet_SelectionChange event will only fire when the active cell changes either by you clicking in a different cell or a macro selecting a different cell. BA will never trigger a selectionchange event.

You could check to see if any of the P/L figures have changed using the worksheet_change event handler but then you'll get the state of the market when the bet is matched rather than when the bet is placed (although that might be the same thing).

Can you use the trigger cell changing as the trigger for when to record the state of the market. Is it vba or formula?

[dermag]

Hi allgreen,

I do somethng similar to this but only once at the end of in running. My code basically copies the whole range that BA sends to Excel down below row 50.

So at the end of the race the range A1 to DY40 gets copied once to the bottom of the worksheet. I then analyse it after racing has finished. I go as far as DY so that if I add extra bits to the columns on the right I know my code will pick up a full 100 columns. I also collect data like lowest price in play, highest price in play, betfair sp's and betfair race over prices etc etc.

If I was wanting a 'snapshot every time one of the profit/loss cells changed I would copy the cells X5:x28 to another empty column and then chack for discrepancies. If there is a discrepancy then you know one of your bets has been updated and the 'snapshot' will occur.

I'd probably go something like this..... (thanks to Mitch and george uk for various bits of code that helped me)

Code: Select all

If Target.Columns.Count <> 16 Then Exit Sub        '16 cols for the API version, 13 for the WEB version of the software.
Application.EnableEvents = False    'essential as any change will result in a possible endless loop and possible lock-up

Dim runners As Integer
Dim sourceRange As Range
Dim destRange As Range

Dim profitSource As Range
Dim profitDest As Range
Set profitSource = Range("X5:X28") 'presumably the range where your proit/loss appears
Set profitDest = Range("AA5:AA28") 'blank range that isn't used on your sheet CHANGE IF NEED BE


runners = WorksheetFunction.CountA(Range("A5:A40"))    'counts the current race runners, upto 36 runners


If WorksheetFunction.Sum(profitDest) <> WorksheetFunction.Sum(profitSource) Then   'identifies change and snapshots
    Range("A45") = "Previous Snapshots"
    Set sourceRange = Range(Cells(1, 1), Cells(1, 1).Offset(runners + 4, 100))   'This is the current region used
    Set destRange = Range((Cells(Rows.Count, "A").End(xlUp).Offset(6, 0)), Cells(Rows.Count, "A").End(xlUp).Offset(runners + 10, 100))   'captures 100 columns
    destRange.Value = sourceRange.Value
End If

profitDest.Value = profitSource.Value 'replaces old data with new updated data

Application.EnableEvents = True


Those lines will probably do the job for you. I don't know what other bits of code you have in your worksheet so you may have to try and fit that in the right place in your worksheet_change module.

There's probably an easier way to do it but this worked for me.

[allgreen]

Thanks for the replies guys, I'll have a good look at them today when I get home from work. (In Australia - different time zone and no in play betting )

The Worksheet_SelectionChange event will only fire when the active cell changes either by you clicking in a different cell or a macro selecting a different cell. BA will never trigger a selectionchange event.

Thanks Mitch, something I have learnt today.

You could check to see if any of the P/L figures have changed using the worksheet_change event handler but then you'll get the state of the market when the bet is matched rather than when the bet is placed

I'm using a short fill and kill to just take available odds so hopefully the market won't have changed dramatically between when the bet is triggered and matched.

Thanks for the code dermag, as I said I am a VB novice so will try and get my head around what the code is doing.

Would it be simplier (doable?) to just have formula in one cell =SUM(X5:X28) and run the macro each time the total changed.

I don't have any other code in my worksheet apart from the screenshot macro that does the copy and paste. I am running this macro manually from a command button at present but am trying to set things up so that this runs automatically from an automated quick pick list.

I use four sheets. BA win data, BA place data, a control sheet which brings this data together (work in progress) and displays WOM indicators, rankings etc and the fourth sheet is where the copied data from the control sheet is posted to. The paste macro offsets to a new cell below the latest paste area so that on this sheet I build up an archive of market conditions and indicators in place when each bet is matched.

Hopefully this data will over time enable me to refine my methods and see what works and what doesn't. Still don't know if all this will prove profitable ongoing but the implementation of new ideas and solving of new problems is half the fun and keeps the tired old brain ticking over.

Thanks for your time.

[dermag]

Hi allgreen,

Like I said, I'm sure there's an easier way and your suggestion does indeed look favourable. However I'm buggered if I can get it to work with either Worksheet_Change or Worksheet_Calculate. I don't think Worksheet_SelectionChange enters the equation as that one triggeres when a physical cell is changed.

The problem I'm having is that the Worksheet_Change event only fires when a change is made either by programming code or physical typing, it does not fire if a cell (say X1) was to simply change by automatic calculation.

If anyone can suggest help I'll gladly keep working on it if I get any spare time.

[allgreen]

dermag, the following extract from a thread on Mr Excel forum would suggest that Worksheet.Calculate should be able to do it so unless these guys got it wrong........?

I'll now focus my research on Worksheet.Calculate and see if I can come up with any more info.

***************************************************************************
Re: run macro if cell value greater than 1
quote:
________________________________________
Originally posted by parry:
Unfortunately Excel doesnt have an event that is triggered by a cell change as a result of a formula
________________________________________
!!!! Look at the Calculation event.

Syntax at sheet level:
Private Sub Worksheet_Calculate()


Syntax at workbook level:
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
_________________
Tom Urtis
Doh! Thanks muchly Tom, I cant be drinking enough beer.


Private Sub Worksheet_Calculate()
If [j1005] > 0 Then
MsgBox "Run my code" 'place your code here
End If
End Sub

[dermag]

Hi allgreen,

In that example from the mrexcel forum they are looking at a specific If-Then value, if cell j1005 > 0 then...etc etc. This is ok if you have a fixed value to trigger things. This one is black or white, if its over zero trigger, if not then dont trigger. We don't have a fixed value as your X1 can be anything, it just needs to trigger on a change. This is where I'm a bit stuck. I tried all sorts of combinations this morning on the calculate event but couldn't get it to work.

This is why I used a comparison range in the first example I gave you yesterday. We have a black or white scenario there, if it's different then trigger, if not then dont trigger.

I'll keep at it as I'm interested to learn myself.

[GeorgeUK]

i've not got access to a computer with BA on for a while, but what do you get with worksheet_change?
Does it not only run when a cell in x5:x28 changes?

after the line
If Not Intersect(Target, Range("X5:X28")) Is Nothing Then
put
a = target.address
b = target.value
c = target.count
call Screen_Shot

select the line a=... and press F9.
The code will break when it reaches this point and you should be able to then step through using F8

When you hover the cursor over c, is it > 0
can't remember if ther was an issue with more than one cell being updated at the same time.

Will put my thinking cap on if you can get back to me.

George

[allgreen]

Hi dermag, how about something like :

(a) X1 = SUM(X5:X28)
(b) another cell, say AA1 we enter 0.

- Condition to trigger reads 'If X1<>AA1 Trigger macro'

- When first bet placed X1 changes and triggers screenshot macro

- (here's the kicker) I add to the existing screenshot macro code to also copy X1 and special paste the value into AA1 each time it runs thus cancelling the trigger condition until the next bet is made.

- This also means the macro would run when automatic quickpick changes to a new race and reverts the value of X1 back to zero, IF a bet had been placed in the preceding race.

That's my theory, anyway.

[dermag]

Hi allgreen,

I was thinking of something the same, simply get the code to update the comparison value (AA1).

I could add an If/Then statement to stop it running when the quickpick changes race.

I can knock something up later if you want? time permitting.

[allgreen]

George, I did as you suggested and got target.value = empty and target.count =1 first up then target.count=5 when I tried it again on another race.

It's getting late here so off to bed for this old boy. Thank's for the interest.

[allgreen]

Thank's dermag, that would be much appreciated. I will have more free time over the Easter break to try and get everything up to scratch.

[dermag]

Hi allgreen,

I used this code in the Worksheet_Calculate event ...

Code: Select all

Private Sub Worksheet_Calculate()

If Range("x1") = Range("aa1") Or Range("x1") = 0 Then Exit Sub 'indicates no change or new race

'Everything under here runs if there is a change in X1

Dim runners As Integer
Dim sourceRange As Range
Dim destRange As Range

Application.EnableEvents = False

runners = WorksheetFunction.CountA(Range("A5:A40"))    'counts the current race runners, upto 36 runners

Range("A45") = "Previous Snapshots"
Set sourceRange = Range(Cells(1, 1), Cells(1, 1).Offset(runners + 4, 100))   'This is the current region used
Set destRange = Range((Cells(Rows.Count, "A").End(xlUp).Offset(6, 0)), Cells(Rows.Count, "A").End(xlUp).Offset(runners + 10, 100))   'captures 100 columns
destRange.Value = sourceRange.Value
Range("AA1").Value = Range("x1").Value

Range("z1") = Range("z1") + 1 'this row was just a debugging thing, it count the number snapshots and displayed on the sheet


Application.EnableEvents = True

End Sub


Now, everything worked ok until I realised that BA puts bet info in row by row, unlike race info which goes in one chunk of 16 columns X (number of runners) rows. What I was getting was the update in X5 for the first bet, this triggered the event ok. Then the update for X6 which also triggered the even, and then X7 which also triggered the event and so on for the 20 runners that I tested it on.

The code works ok but is triggered X number of runners everytime a bet is updated. Which is no good to anyone.

More research methinks.

[Mitch]

To get around that problem I use the line

Code: Select all

If Target.Columns.Count <> 16 Then Exit Sub

at the start of the code which prevents the macro from running each time the single rows of bet info are updated - just when the main chunk is updated.

I know it's not exactly when you want it to run, but the data you're checking will only have been updated once since the last time the macro ran.

Edit: I've just re-read your code and realised there is no Target with the Worksheet_Calculate event so to use it you'd have to have it in a Worksheet_Change event.

[dermag]

Hi Mitch,

I usually do use your line (taget.column etc) in my code, thanks for that, but I was trying to get the worksheet_calculate event to work. Partly to help allgreen and partly to increase my knowledge.

I personally would use the worksheet_change event as it would do the same job. To get the worksheet_calculate to work I think we could use the last horse's cell X? to compare if it's changed instead of using sum(x5:x28)in cell X1 and comparing this to the AA1 cell (which holds the last recorded sum).

This way the macro fires when the last horse's profit/loss has been updated and not every time there's a horse updated. I'm assuming (maybe foolishly) that whatever stakes or part stakes get matched on whatever horse that the profit/loss figure would change for all the horses. Including our target, the last in the list. Which should I think do the job we want.