If you've managed the above we can now set up some simple Worksheet_Change code.
In simple terms excel looks for events occuring and one of those events is a change in a cell on the sheet, if excel detects a change our worksheet_change routine kicks in and runs any code within it.
When Betting Assistant is linked to excel it sends 2/3 sets of data on each refresh, one set contains the market data like race time etc another contains the price data and the last additional columns. Obviously the price data is the one we want to concentrate on any changes so that's why the line
- Code: Select all
If Target.Columns.Count <> 16 Then Exit Sub
is included in most of the coding you see on the site, basically if any changes on the sheet affect less than 16 columns (A:P) we exit our worksheet_change sub routine early as there's no point it running thru all the coding. So when you changed AC5 manually it only affected 1 column and the routine exited before it called your copying routine.
Another couple of pieces of code you'll see in examples are
- Code: Select all
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'code is in here
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
The reason we add these to our code is to stop Events within excel continually triggering our Worksheet_Change routine which is simply looking for the 'event' of a change in a cell, i.e. if our code is writing data to cell AD5 that would trigger another worksheet_change routine. ou could say but we've already accounted for that by sticking in our code
If Target.Columns.Count <> 16 Then Exit Sub , the problem there is that although the routine would exit it would still run, if we inadvertaintly had our code changing 16 columns it'd get in a continuous loop. It's much better we simply turn off all events from being able to re-trigger whilst our routine is running. We also have the option of turning off screen updates whilst our code is running which can be useful if your sheet has hundreds of formulas because those continually being written to the screen can slow things down. As we're just using VBA there shouldn't be any need to turn screenupdating off and on.
So now our bare bones Worksheet_change routine would look like this, we've added
Option Explicit to the top as that requires us to declare all variables which is simply just good practice, maybe not needed now but may be at a later date. Another thing you'll notice is the use of comments by using an apostrophe ' , VBA ignore the text after the apostrophe. It's good practice to comment your code so you can remember what it's doing, it's very easy to get confused once your code gets biggers and the comments will help you track down parts that need changing etc
So here's your basic code to insert into the Worksheet
- Code: Select all
Option Explicit
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 'turns off events so we don't retrigger the code
Application.Calculation = xlCalculationManual 'turns off all calculations
'code is in here
Call CopyAC5 ' just your simple copying sub routine
Application.EnableEvents = True ' events back on
Application.Calculation = xlCalculationAutomatic ' calculations back on
End Sub
We now post this code under Sheet1 by double clicking on Sheet1 in the Microsoft Excel Objects on your VBA project screen. We could post it under any of the sheets listed but it needs to go under the excel sheet that is linked to Betting Assistant for it to fire. We can also include code under the tab for ThisWorkbook then it would fire under every sheet in the workbook but for what we're doing it's only eeded under the sheet you will link Betting Assistant to.
Now post the Sub routine into our module1
- Code: Select all
Sub CopyAC5()
If Range("AC5").Value = 1 Then Range("AD5").Value = Range("AC5").Value
End Sub
We will also add another simple sub to modules one to reset events if we encounter errors it means we can easily reset them just by firing the macro
- Code: Select all
Sub reset()
Application.EnableEvents = true
End Sub
Now close and save that file as test.xlsm, make sure you save it as a macro enabled file.
Fire up betting assistant and go to the Excel Menu, browse to your test.xslm file and press ok to link excel to betting assistant. Your code should now be active. But be aware your code isn't very useful and will only fire when those 16 columns get sent to excel from BA, depending on how quick you're refreshing from Gruss it may appear to update when you put a 1 in AC5 but the reality is it is only firing when it sends a price refresh to trigger the code because 16 columns have changed. Obviously if changing AC5 is part of some bot then fine but be aware of how and why it is triggering.
